Index and statistic maintenance

Mauddib

We have been reviewing the default parameters of the scripts we use in SQL to rebuild and maintain indexes and statistics on the SQL side. A bit of a newbie to this I have a few small questions about the default values which are actually recommended by Microsoft for SQL server in general, but might be better modified for NAV specifically (Navision 2017 CU 2.1. SQL version 12.0.5579.0.).

The script does a reorganization if there is a 5 to 30% fragmentation and a rebuild for anything larger than this. Are there more recommended values for Navision than this? We find that we reach 5% remarkably quickly, in less than a week, in some cases.

Some of our indexes however are highly fragmented and remain so because the script only works with indexes larger than 1000 pages. Again, is there a more recommended value for Navision than this, or what is considered too high or too low.

How often is it recommended to update statistics on these indexes too? And what Fill factor is recommended for Navision? I have seen a few values recommended, but 5 or 10% free in a page is common as a recommendation.

Thanks muchly all

Slawek_Guzek

NAV is not much different than any other application from the SQL Server point of view.

The fragmentation thresholds are usually set up based on table fragmentation and defragmentation operation impact and available service windows. Reorganization is an online operation, which means the index is available to use - but it fills up transaction log, and usually takes longer to complete. Rebuild does not blow up transaction logs and usually takes shorter to complete - but it also takes the index / table offline.

The Fill Factor is usually tuned up for a particular table structure and read/write workloads on this table, on a table per table basis, not for an entire application, be it NAV or any other app running on a SQL Server.

You would normally start playing with those when there is a problem to solve. In your case there is no apparent problem so my personal recommendation would be to leave the parameterts at their default values.
You can perhaps drop the fragmentation threshold a bit (to 500 pages) - but again will probably not make any difference for the perfomance of your system. Such small tables (1000 pages =~ 8MB) usually live entirely in SQL Server cache if accessed frequently, and high fragmentation level on those does not make any noticeable impact.

kriki

Also Rebuild blows up the transaction log. But it does a better job to defrag, but locks the whole table/index during rebuild. If you Enterprise Edition, you can do online rebuild. It only places a lock at the beginning and the end of the process.

Slawek_Guzek

NAV is not much different than any other application from the SQL Server point of view.

The fragmentation thresholds are usually set up based on table fragmentation and defragmentation operation impact and available service windows. Reorganization is an online operation, which means the index is available to use - but it fills up transaction log, and usually takes longer to complete. Rebuild does not blow up transaction logs and usually takes shorter to complete - but it also takes the index / table offline.

The Fill Factor is usually tuned up for a particular table structure and read/write workloads on this table, on a table per table basis, not for an entire application, be it NAV or any other app running on a SQL Server.

You would normally start playing with those when there is a problem to solve. In your case there is no apparent problem so my personal recommendation would be to leave the parameterts at their default values.
You can perhaps drop the fragmentation threshold a bit (to 500 pages) - but again will probably not make any difference for the perfomance of your system. Such small tables (1000 pages =~ 8MB) usually live entirely in SQL Server cache if accessed frequently, and high fragmentation level on those does not make any noticeable impact.

kriki

Also Rebuild blows up the transaction log. But it does a better job to defrag, but locks the whole table/index during rebuild. If you Enterprise Edition, you can do online rebuild. It only places a lock at the beginning and the end of the process.