what is the best setting
If there would be a "best setting", then there would be only one setting: The best.
It's an individual decison and depends on several factors.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
we have SQL server 2019 and I have task to fine tuning all indexes around 3000 . I noted most of the DB fill factors set to 75 and 80 and some are 0. what is the best setting we should provide in base line level
many thanks
what is the best setting
If there would be a "best setting", then there would be only one setting: The best.
It's an individual decison and depends on several factors.
Hi @Ashwan ,
Welcome to Microsoft Q&A!
If your system static and just read only, a default Fill Factor of 100 (or 0) is ideal. As there is no insert, update or delete, having all the pages filled up makes sense.
If your system has lots of OLTP transactions, then a lowering Fill Factor (between 70 to 90) provides better result
There will not be a clear answer. You need judge it regarding the appropriate Fill Factor that suits your system.
For more information, please refer to these links:
https://www.itprotoday.com/what-best-value-fill-factor-index-fill-factor-and-performance-part-2
https://www.mssqltips.com/sqlservertip/5908/what-is-the-best-value-for-fill-factor-in-sql-server/
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
There is absolutely no straightforward to that question.
If you have an index with a monotonically growing key, and there are never any growing updates(*), there is no reason not to use 100. (0 is the same as 0.)
But if the key is more random, for instance a GUID, or there are plenty of growing updates, a lower fillfactor may be better, as you can avoid page splits. But it's only really a point if your monitor the system closely and rebuild the index before the space fills up.
(*) updates that enlarges variable-length values. Also updates in databases where some sort of snapshot is enabled, as they add a 14-byte pointer to the record.