SQL SERVER – Set Server Level FILLFACTOR Using T-SQL Script

October 20, 2007

As the title is very clear what this post is about I will not write long description. I have listed definition of FILLFACTOR from BOL here.

FILLFACTOR

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made by Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left on the page, when the fill factor is 100.

SQL SERVER - Set Server Level FILLFACTOR Using T-SQL Script fillfactor-800x633

T-SQL Script to set Server level FILLFACTOR to 90

EXEC sys.sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'fill factor (%)', '90'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'show advanced options', '0'
RECONFIGURE WITH OVERRIDE
GO

Here is a quick video about how to change fill factor:

You can subscribe to my YouTube Channel for frequent updates.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Performance, SQL Scripts, SQL Server

SQL SERVER – Explanation and Understanding NOT NULL Constraint

SQL SERVER – 2005 – Limitation of Online Index Rebuld Operation

4 Comments. Leave new

Pinal Dave
April 3, 2010 2:19 am
Hello Kena,
If you are retreiving all records then defragmention of clustered index is enough. For the best use of any index, besides defragmentation it’s statistics should also have updated.
Regards,
Pinal Dave
Reply
daniela
April 28, 2010 7:59 pm
hello.
is there a way to know what value to set the fill factor ??
is there a way to know how much a table is used using T-SQL ??
Reply
Subhro Saha
September 18, 2010 6:31 pm
Hi Daniela
Just to answer your first question- Yes we can see the value of the current Fill Factor by the following query:
SELECT OBJECT_NAME(object_id) AS ‘Table/View’
,name AS ‘Index Name’
,index_id AS ‘ID of Index’
,fill_factor AS ‘Current Fill Factor’
FROM sys.indexes
From SQL Server 2005 and above you can use DMF- sys.dm_db_index_physical_stats to get all the information about your indexes..
I hope that helps.
Thanks
Reply