HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How can I alter the fill factor of an index outside of an ALTER INDEX command?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canthefactorcommandoutsidehowindexalterfill

Problem

I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB (I can set them when scheduling rebuilds on an index-by-index basis, but with 1,600+ indexes, I'd like to set them in advance in bulk). Outside of an ALTER INDEX command, is there a way I can redefine existing index fill factors?

Solution

Check your DB Server (or instance) options, it will set every index based on the fill factor you specified.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', x --set the fill factor value here, such as 70 for 70%
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO


edit:

Do note, it will not actually rebuild or alter existing indexes. It will fulfill your request that all future index rebuilds will now have this fill factor, unless specified in the rebuild index command. Anything specified in the rebuild command will override this option.

Code Snippets

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', x --set the fill factor value here, such as 70 for 70%
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Context

StackExchange Database Administrators Q#39528, answer score: 5

Revisions (0)

No revisions yet.