March 10, 2016 at 6:38 pm
When we create non-clustered indexes, is it a best practice to set fill factor other than default value.
If so, what is the fill factor value to start with - 95/90...?
Please provide your thoughts.
many thanks!
March 11, 2016 at 6:05 am
By and large, when creating a new index, I leave the default in place unless I know going in that I'm likely to see lots of fragmentation. Otherwise, I don't adjust it until I see a problem through monitoring. The default value is far from perfect, but the time it takes to tune every index isn't worth it in most cases. Picking some other arbitrary number as a starting point is just as problematic as using the default. You're still going to have to adjust up & down on individual cases based on monitoring. The lazy approach, in this case, seems to work well enough most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2016 at 6:29 am
Thanks Grant!
March 11, 2016 at 6:59 am
Keying off of a CRITICALLY important point that Grant makes: expecting fragmentation. This one will absolutely harm your system in numerous ways in a number of cases:
1) Non-sequential GUIDs: 85 FF, depending on what my client's index defag interval is. PLEASE DO YOURSELF A FAVOR AND DON'T USE THESE!!! :w00t:
2) "full-range" columns (LastName for example): 90
3) increasing sequentials (date, ubiquitous identity(1,1), etc) that are being updated to larger or non-null values routinely: 95
All of the above are adjustable up and down depending on your index defrag MX cycle. Don't forget to take additional/included columns into account.
You seek a balance between the often DEVASTATINGLY BAD page split(s) in the middle of your transactions and bloating storage/memory use for data. PLEASE use Ola.Hallengren.com's maintenance suite and set it to log the fragmentation it finds and review that information to adjust your fill factor to find that balance.
Most other things you can leave at the 0 default for FF.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2016 at 10:39 am
Thanks Kevin!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply