SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069

“I want to change fill factor to another value – how do I do that?”

The other day I received this question in via Facebook. 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.

I decided to build a quick video which explains how to change fill factor with the help of T-SQL as well as SSMS.

The code which I have used in this code is over here:

EXEC sys.sp_configure N'fill factor (%)', N'80'
GO
RECONFIGURE WITH OVERRIDE
GO


Action Item

Here are the blog posts I have previously written on the subject of Fillfactor. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

,
Previous Post
Database – Taking Backup of MySQL and MongoDB on Cloud
Next Post
SQL SERVER – Keeping MSDB System Database Lean and Fit – Notes from the Field #033

Related Posts

3 Comments. Leave new

  • Pinal,

    I am learning OLAP concept for creating cube with dim & fact table. But most of time fact and dim table are already created. Do you know some-one who can guide me or any good books which i can refer to?

    Reply
  • Hi Pinal,
    I just want to know is there any feature in SQL Server which will return the number of rows affected by the select upto that row.
    For Ex:
    Select * from #temp
    Select @@ROWCOUNT will give number of records affected but i want row count when i am executing above query itself. If i am done with fifth row can i get the five , sixth row can i get six like wise. I want to know anything apart from row count

    Reply
  • To make sure there’s no confusion – this is changing the default fill factor for the server for creation of new indexes (SQL Server says indexes, so I’ll do that and argue that we can suspend proper English in database land and that the plural form of a database index is different than the English language plural form of index) and does not change the fill factor of any existing indexes.

    Reply

Leave a Reply

Menu