SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Please Don’t Do This!

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'

GO

RECONFIGURE WITH OVERRIDE

GO

Example script for rebuilding in index with new fill factor

USE DEMO;

-------------------------------------------------------------------------

 --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)  

WITH (DROP_EXISTING = ON, FILLFACTOR = 80);  

GO

 

SQLEspresso

I am Monica Rathbun. I’m currently a Sr. Database Administrator/BI Architect at Massimo Zanetti Beverage, USA in Virginia. I’ve been a Lone DBA for 15 years, working with all aspects of SQL Server and Oracle. I am currently the co-leader for the Hampton Roads SQL Server User Group and can be found on Twitter daily as @SQLEspresso. I am passionate about SQL Server and the #SQLFamily, doing anything I can to give back to such a wonderful community. As a new speaker at SQL Saturdays and a new blogger, I hope to earn my place as a valued member. When I’m not busy with work, you will find me playing taxi to my two daughters back and forth to dance classes.

Comments

Leave a comment on the original post [sqlespresso.com, opens in a new window]

Loading comments...