Nav - Optimize Tables

Malcynn

Version: Navison 2009 - Classic Client. (MS SQL 2008 Back-end)

We are finding that when we post Credit Notes, Apply Payments or print invoices we find the system to be extremely slow (could take over 7 minutes to post a credit note). We have never run the Table Optimization routine within Nav nor have I re-indexed the tables in the SQL Server. We have several companies in our current database.

I do not believe we have made any customization's to any of these reports/tables. Would running the table optimization function (In Nav) followed by updating the table stats (SQL SIDE - sp_Updatestats) possibly increase my performance without having to re-index my tables in SQL. If so how often should we be running these routines.

More information that could help:

1) # of Number of users 30-35
2) # of posters 2 (issue occurs even if there is only one person posting).
3) Dimension codes in use - 2
4) Database size about 5 gig

Your thoughts,

kriki

Optimizing the tables in NAV is not needed.

Best create a maintenance plan in SQL Server to rebuild the indexes at least once a week (if you have time also each night) with a fillfactor of 90% (= leave 10% free space in pages). Rebuilding the index removes fragmentation inside the database and at the same time calculates the statistics with 100% sampling. Better than that....
Do NOT use autoshrink in properties or with a maintenance plan.
Make sure your database always has around 80% free space (easiest to check using NAV => File => Database Info
Check if your database files are fragmented and if they are, run the Windows defrag.
With a DB of 5 GB, I don't think memory is a problem, at least if there are no other databases on the server and nothing else runs on that server.

There are also others things to control, but I would start with these.

Marije_Brummel

https://www.youtube.com/watch?v=0KbZkKdyZps

Malcynn

Kriki, I will try that out and see what happens. I fully support your statement on the auto shrink, I have never been a fan of that functionality in SQL. I could run a defrag on it the server tonight after hours, though I have never done this on a Windows Server before. My database usage is at about 94% (stated in Nav-Database-Info), looking at the database setup in the SQL Server it is set to Autogrowth (by 10%). Should I change this?

Mark, thank you for the video. I will run each of those scripts manually today to see what impact they will have on my test databases. Kriki suggests I run a 90% fill factor vs the 15% fill factor your recommend in your video. I will try with both and see which works best for our database.

What I did forget to mention is that all users access Navision through a Terminal Server (I have checked for memory and hard disk space and all is well on that front even the cpu is sitting at about 40% (peak) which is good for 33 simultaneous users).

Thank you all in advance,

kriki

The autogrowth should only be used as a failsafe to avoid that the person that can grow the database is ill or in vacation and your databasefile is 100% full.
10% is not a good idea because it starts with very small increments and when the database is big, they are very big increments.
Better change it to 50MB increments. But like I said, this is only as failsafe. The best is to grow it manually when the need arrives.

90% or 85% fillfactor does not change a lot. It is a value that depends on personal preference. The idea is not to have 100% fill factor.

Marije_Brummel

Fill Factor means the amount of free space on a SQL Server page. A page can contain a number of records.

Let's say that for example a page can contain 10 records of a sales line. If then someone later adds another salesline for the same order this is stored on a different page, unless the fill factor allows free space.

With 90% kriki means that 90% of the page is used.

With 15% denster means that 85% of the page is used.

There are more advanced tools that calculate this percentage based in page splits.

Malcynn

Hey Mark, why do an sp_createstats 'indexonly' only and not an sp_createstats fullscan? Is it because your re-index plan accommodates the fullscan on the data (100% sample)?