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