SQL server 2019 Index fill factor setting best practise

Ashwan 531 Reputation points
2022-06-30T03:13:18.907+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 45,881 Reputation points
    2022-06-30T05:30:19.1+00:00

    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.

    0 comments No comments

  2. Seeya Xi-MSFT 16,571 Reputation points
    2022-06-30T08:35:03.907+00:00

    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.


  3. Erland Sommarskog 116.5K Reputation points MVP
    2022-06-30T21:47:14.58+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.