Thursday, December 26, 2013

Define Fill Factor. what is SQL data page

Define Fill Factor. what is SQL data page

Find the solution of following queries-

As we know the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. It means When the value of clustered or non clustered index is created or changed, SQL Server reorganizes the storage of the data in the table.

What is Fill Factor
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 of  Page with size 8K.Avery page can store many rows it is tottaly depends upon the size of the row.

What is the default value for a Fill Factor
The default value for a Fill Factor is 100. If the value of fill factor is 100, It means there is no or very little empty space left in the page.

Measurement of Fill Factor at the server level
1: Use the following script to  measure the Fill Factor at the server level
Syntax-
              SELECT   * FROM sys.configurations  WHERE name ='fill factor (%)'

What is fill factor in sql server


2: Use the following script to  measure the Fill Factor at the table/index level
Syntax-
              USE YourDatabaseName;
              SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
              FROM sys.indexes

measure the Fill Factor at the table/index level


Note: Never forget the fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table.

0 comments:

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine