Fill Factor

Introduction

In my previous article, I am trying to discuss about the MS SQL Server Index and how the Index Boost up the performance of SQL Server Query.

Fill Factors is directly related to Index and Index is related to performance. So without the proper knowledge of Fill Factor, understanding of Index is not completed.  So I in this article I am trying to discuss related to Fill Factors.

 Point in Focus

1.    What is Fill Factor

2.    How it Effects

3.    Measurement of Fill Factors % Value

4.    How to Set Fill Factor

5.    Is There anything New in MS SQL 2008/2012

6.    Related tropics

What is Fill Factor

Before going to Fill Factor we have to understand the architecture of Index. In the MS SQL Server the Indexes are organised by B-Tree structure.

Please go to my previous article "Related to Cluster Index" to find the details of it.

http://sqlknowledgebank.blogspot.in/2012/07/related-to-clustered-index.html

In the MS SQL Server the smallest unit is the Page (8K) where one on more rows stores depends on the size of the rows.

Fill Factors is the value that determines the percentage of the spaces in each leaf-level pages to be field by data.

The range of the Fill Factor starts from 0% to 100% and the default value is 100%. Here the 0% and 100% means the same

 How it Effects

I think the above example of Fill Factor is quite complex to understand to make it simple, let's take an example.

Index is fragmented due to DML operation on the table objects. Defragmented index is causes to bad performance of query in MS SQL Server.

 Stuation-1

 In this example we are taking an Index with Fill Factor of 0 or 100 % and the Leaf-level data pages are completely full. Now we are trying to update a row which is located in the same pages. The size of the new value that is updated is bigger than the previous one.

So In this situation the page size cannot support the new value as the page is full.  So it create a new leaf-level pages and the 50% of the data moves to the new page and in the previous pages 50% data exists so the page filling of the old page is only 50% and the Index is fragmented.  To find the desired rows SQL Server have to move multi pages as the Index is fragmented by Page splitting.

Situation-2

Now take above example with FILL FACTOR vale is between 0 to 100%. For example we are taking the FILL FACTOR Value = 70%.

Now for the above example (Situation-1) for the new updated value, MS SQL Server try to adjust the new data within 30% of the leaf-level pages as the 70% of the pages is full and the rest 40% is blank pages and the page is not split.

Measurement of Fill Factors % Value

It is hard to say that what percentage of fill factor that we use. It depends on situations to situations or we can say it how often the DML operation is done on the table objects.

Here I am trying to explain some situations and there fill factor percentage

Situation for Fill Factor	Fill Factor %
Static Table – The value of the table never changed	100
Tables Updated Less Often	95
Frequently Updated Table	70 to 90

If a table containing clustered Index with Primary key and use IDENTITY columns than we can use the Higher percentage vale of the Fill factor.

 If the table containing clustered Index with  Primary key and the value of the Primary key is NOT sequential like Auto incremental value than, we can use the Lower Percentage Value of the Fill Factor.

In case of OLTP systems we can take the lower percentage value of Fill Factor. But in case of OLAP system we can take the higher percentage value of Fill Factor.

Don't set the Fill Factor Value to 50% others your index is fragmented.

How to Set Fill Factor

To Set the Default Fill Factor

[A]  MS SQL Server Enterprise Manager.

1.    In the MS SQL Server console tree, right-click on your server.

2.    From the shortcut menu, choose Properties.

3.    Select the Database Settings tab.

4.    Change the fill factor by choosing Fixed below Settings, and then specify the fill factor percentage by using the slider bar.

[B]  By T-SQL

To set the Fill Factor 90%

sp_configure 'show advanced options', 1
GO

--Here is the message:

Configuration option 'show advanced options' changed from 0 to 1.

Run the RECONFIGURE command to install.

RECONFIGURE

GO

sp_configure 'fill factor', 90

GO

RECONFIGURE

GO

Specify Fill Factor of an Existing Index

USE AdventureWorks2012;

GO

-- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index

-- with a fill factor of 80 on the HumanResources.Employee table.

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode 

ON HumanResources.Employee

REBUILD WITH (FILLFACTOR = 80);

GO

Another way to specify a fill factor in an index

USE AdventureWorks2012;

GO

/*

 Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index on

the HumanResources.Employee table with a fill factor of 80.

*/

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee

   (OrganizationLevel, OrganizationNode)

WITH (DROP_EXISTING = ON, FILLFACTOR = 80);

GO

Is There anything New in MS SQL 2008/2012

I don't find anything new on MS SQL Server 2005 and MS SQL Server 2012. If you have anything please give me some notes related to it.

Related tropics

1.    Index defragmentation

http://sqlknowledgebank.blogspot.in/2012/07/index-defragmentation.html

Hope you like it.

Posted by: MR. JOYDEEP DAS