Please Don’t Do This!

Published On: 2017-09-06By:

Please, please, please Admins do not leave your default index fill factor at 0. This means you are telling SQL Server to fill the page 100% full when creating indexes. This also means you are forcing it to a new page when additional inserts are done. These are called PAGE SPLITS which can take time to perform and is a resource intensive operation. Having a high fill factor will cause more index fragmentation, decrease performance and increase IO.

If you find that this is how your system is configured, all is not lost. You can correct this by changing the default value so that new indexes will be created with proper factor and rebuilding your existing indexes with another fill factor value. I like to use 80 across the board for most, of course there is always the “it depends” scenario that arises but 80 is a pretty safe bet. One of those “it depends” would be on logging table that has the correct clustering key and never gets updates in between values (make sense?), I don’t want a fill factor of 80.  I’d want 0/100 to maximize page density as page splits wouldn’t occur if the clustered key is monotonically increasing.

Note, having the additional 20% free on a page will increase your storage requirements but the benefit outweighs the cost.

Example syntax for changing the default

EXEC sys.sp_configure N'fill factor (%)', N'80'




Example script for rebuilding in index with new fill factor



 --Drops and re-creates the CREATE INDEX IDX_DepartmentID_DepartmentName

 --index on the dbo.Departments table with a fill factor of 80. 


CREATE INDEX IDX_DepartmentID_DepartmentName ON dbo.Departments 

   (DepartmentID, IDX_DepartmentID_DepartmentName)  




Contact the Author | Contact DCAC

One response to “Please Don’t Do This!”

  1. James says:

    Great article. However couple of questions.

    1. For OLTP is 80% a good starting point?
    2. Is there a perfmon counter to measure performance before and after.
    3. If changed at the server level will all existing indexes also change or anything moving forward.
    4. If current indexes are set to 0, it is recommended to change them to 80.



Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link