Changing the fill factor on multiple tables/indexes

Typically, the advice on fill factor is “if it ain’t broke, don’t fix it”. But occasionally, you’ll find a database or even a server with a crazy default setting that just fills your disk and buffer pool without any real benefit. Here’s a nifty script to rebuild any tables and indexes to a fill factor of 100%.

The code

DECLARE @target tinyint=100;

SELECT (CASE i.index_id
    --- ALTER TABLE REBUILD (for clustered indexes)
    WHEN 1 THEN 'ALTER TABLE '+s.[name]+'.'+t.[name]
               +' REBUILD WITH (FILLFACTOR='+CAST(@target AS varchar(10))+')'

    --- ALTER INDEX (for non-clustered indexes)
           ELSE 'ALTER INDEX '+i.[name]+' ON '+s.[name]+'.'+t.[name]
               +' REBUILD WITH (FILLFACTOR='+CAST(@target AS varchar(10))+')'
    END)

   +'; -- used to be '+CAST(i.fill_factor AS varchar(10))
FROM sys.schemas AS s
INNER JOIN sys.tables AS t ON s.[schema_id]=t.[schema_id]
INNER JOIN sys.indexes AS i ON t.[object_id]=i.[object_id]
WHERE i.index_id>0
  AND i.fill_factor%100!=@target%100
ORDER BY s.[name], t.[name], i.index_id;

What it does

This script won’t actually change anything – it queries the DMVs for indexes that have a fill factor other than 100 and outputs the ALTER TABLE and ALTER INDEX statements you need, so you can copy and paste the output and run it in any order you like.

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.