How Often are Indexes Rebuilt?
Over time, indexes get fragmented, which causes SQL Server to work harder in order to access them, hurting performance. The only solution to this is to defragment all indexes in your databases on a regular basis. There are a variety of ways to do this, and the how-to process won’t be discussed here, as this is explained elsewhere on this website and in the SQL Server Books Online.
The goal of your audit is to find out whether or not the indexes in the databases you are auditing are being defragmented on a regular basis. How often you defragment them can range from daily, weekly, or even monthly, and depends on how often modifications are done, along with the size of the database. If a database has many modifications made daily, then defragmentation should be performed more often. If a database is very large, this means the defragmentation will take longer, which may mean that it cannot be performed as often because the defragmentation process takes too many resources and negatively affects users. As part of your audit, you may want to also evaluate how often the fragmentation is currently being done, and to find out if this is the optimal frequency.
At the very least, if indexes aren’t currently being rebuilt now, they need to be, and as part of your audit, you need to ensure that some sort of an appropriate index rebuilding scheme is put into place.
What is Your Index Fillfactor?
Closely related to index rebuilding is the fillfactor. When you create a new index, or rebuild an existing index, you can specify a fillfactor, which is the amount the data pages in the index are filled when they are created. A fillfactor of 100 means that each index page is 100% full, a fillfactor of 50% means each index page is 50% full.
If you create a clustered index (on a non-monotonically ascending column) that has a fillfactor of 100, that means that each time a record is inserted (or perhaps updated), page splits will occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server’s performance.
Here’s an example: Assume that you have just created a new index on a table with the default fillfactor. When SQL Server creates the index, it places the index on contiguous physical pages, which allows optimal I/O access because the data can be read sequentially. But as the table grows and changes with INSERTS, UPDATES, and DELETES, page splitting occurs. When pages split, SQL Server must allocate new pages elsewhere on the disk, and these new pages are not contiguous with the original physical pages. Because of this, random I/O, not sequential I/O access must be used, which is much slower, to access the index pages.
So what is the ideal fillfactor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:
-
Low Update Tables (100-1 read to write ratio): 100% fillfactor
-
High Update Tables (where writes exceed reads): 50%-70% fillfactor
-
Everything In-Between: 80%-90% fillfactor.
You may have to experiment to find the optimum fillfactor for your particular application. Don’t assume that a low fillfactor is always better than a high fillfactor. While page splits will be reduced with a low fillfactor, it also increase the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is I/O overhead increased with a too low of fillfactor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page (including empty space) is moved to the buffer. So the lower the fillfactor, the more pages that have to be moved into SQL Serve’s buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance.
If you don’t specify a fillfactor, the default fillfactor is 0, which means the same as a 100% fillfactor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages).
As part of your audit process, you need to determine what fillfactor is being used to create new indexes and rebuild current indexes. In virtually all cases, except for read-only databases, the default value of 0 is not appropriate. Instead, you will want a fillfactor that leaves an appropriate amount of free space, as discussed above.
Now What?
Your goal should be to perform this part of the performance audit, described on this page, for each of the tables in each of databases in each of your SQL Servers (that sounds like, and it is, a lot of work), and then use this information to make changes as appropriate.
Once you have completed this part of the performance audit, you are now ready to take a look at how Transact-SQL and your application can affect performance.
Read the Next Article in this Series
Return to Previous Article in the Series
]]>