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

As 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.

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:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Set Server Level FILLFACTOR Using T-SQL Script

  1. Hello Pinal,

    I have a table which contains 21155716 rows. currently, it takes more than 45 minutes to retrieve data while adding WITH(NOLOCK) on a table.

    I did rebuild and reorganize indexes, and also I have set up fillfactors to 90% on indexes. Is there any other thing, I should look into improve the performance on the table. Please let me know.

    Thank you for all your help!
    Kena

    Like

  2. 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

    Like

  3. 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 ??

    Like

  4. 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

    Like

  5. Pingback: SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069 | Journey to SQL Authority with Pinal Dave

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s