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

   +'; -- 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.

One thought on “Changing the fill factor on multiple tables/indexes

  1. SQL2017: If the index name has a hyphen in it, you will get an error “Incorrect syntax near ‘-‘.”, encapsulating the index name with [ ] fixes the issue.

Let me hear your thoughts!

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

You are commenting using your 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.