Fill Factor, Why Do You Taunt Me? Part 2

So Friday we were discussing the idea of an idea. I think... or an idea of an idea's idea. What she said...

Let's answer the easy question. How far are your fill factors off from the default settings?

First we want to look at what our default setting actually is.

EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO;
EXEC SP_CONFIGURE

0 and 100 are the same thing. I strong advise that you do not use a global setting for fill factor other than 100%. It may take more work to address the indexes and tables on a case by case basis... but doing it right also has it's merits.

 I've got a little script that builds out a table for easy viewing. This is also a part of the larger automated fill factor project I'm working on.

The script can be found here.

Now we have a table showing us how all our fill factors look that are not default.

select DatabaseName, SchemaName, TableName, IndexName, fill_factor from [master].[dbo].[DBA_Reset_Fragmentation_Levels] order by DatabaseName, fill_factor asc

Now, depending on your environment, you could have zero results, or thousands.

This is as script I found some time ago, I do not know the author. If anyone knows who wrote this, I'd like to at least give the correct credit.

I will try and define better what constitutes an (user_update), but for now this still works for good information. you can see what table is taking the most updates. These may be better candidates for lower fill factors than others. The one clearly defined thing is that when the updates are near zero, why do you need a fill factor of 50%?

I know it's not spring yet, but maybe a little house cleaning is in order?