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)
26 Comments. Leave new
Hi Pinal, Great Post. I totally agree “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”
I just believe there is not a rule to say that the if index key column is an IDENTITY column, you can configure the fillfactor with 100%
Even if you have index key column is an IDENTITY and configuring fillfactor to 100% if you have variable-length columns (varchar, nvarchar, varbinay) and they suffer constant change or change more than insertion, you will probably have problems with page splits . In these cases perhaps, even though the identity column, leaving a smaller fillfactor may be more appropriate.
Hello Laerte,
Yes, a fillfactor to 100 for any table that can be updated would definately cause page split issue. FillFactor 100 is only suitable for read-only tables.
Thank you.
Regards,
Pinal Dave
Hi Pinal , its great post.
You read my mind Sir, I was thinking to dig almost this topic but time doesn’t permit me. thank you very much for this great post. :)
Nice One
Thanks for the explanation
Suhas
Thanks for this post. this is very informative
It proved to be very informative
Nice tip on the fill factor for inserting rows with columns as identity. It is a key parameter to keep in mind while designing databases.
Hi Pinal,
You have nicely summerised a complicated as well as important subject.
Rama
Pinal,That is very clear explanation. before I never know why we care about fillfactor. I ‘ve always used your site for help. Thank you so much for your all very awesome articles.
Hi Pinal,
Nice Post.
But, I have a question here. In order to avoid page splits we use fill factor. If we don’t specify fill factor on a table, inorder to accommodate a new record page split happens, and while page split happening, it moves half of the page to another page and this is costly.
Lets say if i have mentioned as fillfactor 80, means it will reserve 20% of every page for new records. what if this 20% of space also filled with new records, what will happen at that time?
If it results in a page split, what is the difference of having a page split at the insertion or very first record or after sometime?
Please answer my question…
Hello Srinivas,
Yes, when free space specified by fillfactor, fills after few insertion, page split wll occure. The benifit of fillfactor is to allow insert/update until the free space not filled. The free space specified by fill factor is reserved everytime the index is rebuild or defragmented. So we just have to plan a proper schedule to degrag/rebuild the indexes to maintain the free space and to avoid the page split.
Kind Regards,
Pinal Dave
very nice explanation and all the comments are very good.
Hey, I am in the process of preparing for 70-450 exam and I came across this question on the net. I eliminated the two answers, B,C. Index is performing fine, so no need to rebuild or reorganize. Can you please help.
QUESTION::::
You are designing index maintenance strategies. One large table has a clustered index which never has a fragmentation percentage of more than 2 percent. There is an XML column in the table, and INSERTs are run frequently against this table. You want to minimize the impact of reindexing operations on performance.
A) Drop the clustered index on the large table.
B) Rebuild the clustered index of the large table monthly.
C) Exclude the clustered index from reorganizing and rebuilding.
D) Reduce the fillfactor on the clustered index.
Let me know how sql index is performing during select and insert into.
For query optimization we are using SQL Index. When we insert value into the database with or without using sql index. Which one will be faster..using sql index to insert or without index to insert?
How can we specify Fill Factor when creating a table? I mean for Primary Key?
Hi Pinal,
This is very use full information about fill factor, can you please give me some tips for the selectivity of non clustered indexes on very big table in a data warehouse.
I have one table with approx 90 GB data size & 150 GB index size. Should I remove some nonclustered indexes from my table because all scripts are taking long time to run. Doing horizontal data partitioning on this table with partitioned indexing.
From SLQ BOL: “When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created.”
I have several indexes that seem to stay above 80% fragmentation. I can rebuild them and get them down around 30%, but within minutes they are back above 80%. I noticed that all of these indexes have a FillFactor of 0. Could this be the problem?
you are copying text directly from Microsoft’s website. While I know at one point in this article, you said according to Microsoft. If you are going to quote them, please put quotes around it. Otherwise it is a copyright infringement.
Is there any way for coding java-script with mysql/sql database please write some thing about it.
Thank you , as always trying to explain simple,
that can be understood well. instead of posting complex explanations that is hard to understand.