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.
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.
Thank you! Great solution, here comes the fix for tables & index names with hyphens.
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;
Your query is buggy.
–index_id is not pointing to a clustered index.
–clustered indexes can be altered as well.
This is the fixed query.
DECLARE @target TINYINT = 100;
SELECT t.[name] AS ‘table’
,i.[name] AS ‘index’
,i.fill_factor AS ‘fillfactor’
,i.type_desc
,’ALTER INDEX ‘ + i.[name] + ‘ ON ‘ + s.[name] + ‘.[‘ + t.[name] + ‘] REBUILD WITH (FILLFACTOR=’ + CAST(@target AS VARCHAR(10)) + ‘)’ AS ‘command’
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 t.is_ms_shipped = 0 –no MS own indexes
AND i.type = 2 –1=clustered, 2=non clustered
AND i.fill_factor % 100 != @target % 100
ORDER BY t.[name]
,i.[name];
this was very cool, thank you!