SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance

I would like to start this post with an interesting question:

Where in MS SQL Server is “100” equals to “0”?  And I am not talking about data types now..

Today I will be presenting the answer to this question and some topics related to it.

Creating Indices in SQL Server is one of the most important tasks of any SQL DBA. Performance of your database is directly depends on your skills and proficiency in creating and maintaining the right number and quality of indices..

As a DBA, you can use “FILLFACTOR,” which is one of the important arguments that can be used while creating an index.

Now, the answer to the above mentioned question–

Fill-factor settings of 0 and 100 are equal!

Points to remember while using the FILLFACTOR argument:

1. If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.

2. The server-wide default FILLFACTOR is set to 0.

3. To modify the server-wide default value, use the sp_configure system stored procedure.

4. To view the fill-factor value of one or more indexes, use sys.indexes.

5. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.

6. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

More about FILLFACTOR

According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.

Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would implies that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table.

The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor setting applies only when the index is created or rebuilt.

The SQL Server Database Engine does not keep the specified percentage of empty space in the pages AFTER the index is created. Trying to maintain the extra space on the data pages would be counterproductive because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered. What it means for you as SQL DBA is that when you set FILLFACTOR at 80 and create Index with these settings, your index has 20% space to grow freely without need for page splits that affect the performance!

You can avoid serious performance issues by providing extra space for index expansion when data is added to the underlying table,.Usually, when a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This is known as a page split.

While making room for new records, page split can take time to perform; it is a resource intensive operation. Also, it can cause fragmentation that leads to increased I/O operations. When frequent page splits occur, it is advisable to rebuild the index while setting an appropriate fill-factor value to redistribute the data. Although a low fill-factor value (>0) may reduce the page splits as the index grows, the index may require more storage space. It will keep creating new, “half-empty” pages, and it can eventually impair performance of your “SELECTs”. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, thus increasing the disk IO operations required to retrieve the data. Specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting.

Summary

It is recommended to keep default fill-factor for the databases that have prevalence of SELECT queries running against them such as in Data Warehouses, and it is highly advisable to consider changing your default fill-factor settings if you are managing database with a large number of INSERT and UPDATE queries hitting it. In such databases, a nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table.

Consider a situation wherein the existing rows are updated with data that lengthens the size of the rows; for instance, scenarios when you add extra columns to the table. You should consider the use of a fill factor that is less than 100. The extra space on each page will help to minimize page splits caused by extra length in the rows. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. In such a situation, you should set your FILLFACTOR to 100.

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

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

,
Previous Post
SQL SERVER – Difference TempTable and Table Variable – Table Variable in Memory a Myth
Next Post
SQLAuthority News – Hub-And-Spoke: Building an EDW with SQL Server and Strategies of Implementation

Related Posts

26 Comments. Leave new

  • my table having bulk recovery model and clustered index. now i insert bulk amount of data what haapen

    Reply
  • Hello Pinal

    You gave this code in another post:

    DECLARE @TableName VARCHAR(255)
    DECLARE @sql NVARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 80
    DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
    FROM sys.tables
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    GO

    Is there any way to identify a Clustered index with Identity column in order to set Fill Factor to 100 automatically?

    Reply

Leave a Reply

Menu