Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Checking the Instance Fill Factor

I was reading a post from Paul Randal recently and noted that he recommends not changing the instance’s default fill factor. I agree, and you shouldn’t alter it. However if you aren’t sure if it’s been changed on any of your instances, here’s how to check it.

I’ll show both the GUI and code ways to do this.

SSMS

In SSMS, right click your server instance in Object Explorer and choose properties.

fillfactor1

This will open a dialog that has a number of tabs. If you click the “Database Settings” item on the left, you will have various settings appear on the right side. One of these is the default fill factor.

fillfactor2

It should be zero or 100. Nothing else.

T-SQL

In T-SQL, we can also check by opening a query window and typing:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sys.sp_configure;

This will return all the settings on the server. This is an advanced setting, so you don’t need to enable those.

If you scroll through the list of items, you will find one that is labeled “fill factor (%)”

fillfactor3

Again, this should be zero or 100 in the config_value and the run_value columns. To change this, run this code:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

Filed under: Blog Tagged: administration, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...