Phil Factor

4 June 2020

1 Comment

• Redgate Monitor
• Database Performance Monitoring

When SQL Server Performance goes Bad: the Fill Factor and Excessive Fragmentation

There is nothing particularly ‘wrong’ with index fragmentation: any index will gradually become fragmented, as data is updated, added, or removed. If you find that some indexes fragment rapidly, it is likely to be an inevitable consequence of the table design, such as the choice of datatypes and keys, and the rate at which their data changes. However, it is worth checking the fill factor allocated to the indexes of a table, particularly the clustered index, because a bad setting can occasionally, due to fragmentation, double the time taken to scan the table.

There are many symptoms of excessive index fragmentation. Certain queries may deteriorate in performance because they perform excessive IOs (both logical and physical) to return the data, modifications might slow down, the use of space in the buffer cache can become less efficient, transaction log backups can grow.

SQL Server provides ways to check for highly fragmented indexes, as well as performance counters to detect one of its primary causes, page splits. However, they are both rather blunt instruments. Not all fragmented indexes cause problems, and choosing to regularly rebuild, reorganize, or re-create any index that strays above some defined level of fragmentation will probably do more harm than good. Similarly, the counters detect page splits that don’t cause fragmentation, as well as those that do.

With SQL Monitor you can take a more nuanced approach. You can identify the large indexes, frequently read into memory for data modifications and queries, perhaps already prone to contention and blocking, and waits on resources such as locks and latches. These are the ones where severe index fragmentation can make the situation much worse and for this limited list of indexes, you can consider a bespoke index maintenance strategy, including setting a lower fill factor value for the index.

The Fill Factor

When you define an index, you have a server-wide default fill factor that is normally set to 100 (or 0, which has the same meaning). It is a sensible default in most cases, best left alone by mortals. This is allocated to any index you create unless you specify otherwise. You can, and sometimes should, choose to specify a different fill factor for some specific indexes.

The fill factor is a number, representing the percentage of space on each leaf-level page that should be filled with data, and it is applied only when the index is created, rebuilt or reorganized. It only applies to in-row data pages. When you have LOB data that is not stored in-row, fill factor settings don’t apply to those pages. It also doesn’t apply to a heap: only when it is transformed into a table with a clustered index.

With the default setting of 100%, SQL Server will fill each leaf-level page to the brim, maximizing storage efficiency. However, this means that if SQL Server need to insert new rows, randomly, into an existing page, or update some rows so that they increase in size (such as updating a variable length column), then a page split will occur. When this happens frequently, it can slow down data modifications. Also, the index fragmentation that results from page splits can affect storage efficiency, both on disk and in memory, and can sometimes affect the performance of queries on that table.

The default fill factor is fine in many cases, such as for a clustered index that has an IDENTITY column or DATETIME as the primary key, but in other circumstances, you may need to fine-tune the fill factor for an index, to find the ideal compromise between data storage and performance, minimizing page splits.

If you decide, for example, that, given the design and usage of a table, your index will work better in practice if 15% of each leaf-level page were left empty, then you would choose a fill factor of 85. When that index is subsequently rebuilt or reorganized, the fill factor is applied, and 15% free space will be left on each existing leaf-level page, between the index rows, to accommodate future growth. Of course, as updates and insertions continue, this free space will be used and pages will start to split again, unless you reorganize or rebuild the index before that happens. This will remove fragmentation and resets the empty space back to the fill factor setting.

The Page Split

As the data changes, SQL Server relies on either recycling space that is no longer used or in performing page splits. These page splits are part of the normal operation of a database. They happen when more space is required on an existing index page, to add a new row, or if existing rows are updated with data that lengthens the size of the rows, but the page is full up.

When faced with this problem, the database engine solves it by splitting the page to make room, so it moves about half the rows to a new page, leaving both the original and new pages about 50% full.

A page split can also occur if you update the key column of an index, since for key columns SQL Server has to delete the existing row and insert a new one with the new value (which may need to go on a page that is full). While updating key columns is a bad practice on clustered indexes, it can happen on non-clustered indexes.

You don’t want page splits to happen too often because the physical act of splitting a page and moving 50% of the data takes time and resources and will slow down your INSERTs and UPDATEs, if it’s happening a lot. Also, all the actions required to split pages and move data will be logged, so your transaction log (and the size of log backups, where relevant) will grow as well.

Other problems are caused by the index fragmentation, that results from excessive page splits.

Index Fragmentation

What is the advantage of adjusting the fill factor to allow extra space when an index is created, rebuilt, or reorganized? Firstly, it reduces the frequency of page splits for a period because it allows space for indexes to expand when the data that is being indexed is added to or amended. Secondly, as a result, it delays the onset of index fragmentation that can result from page splits.

The fragmentation takes two forms. When most people refer to index fragmentation, they mean the logical fragmentation (sometimes called external) that occurs when “indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering of pages inside the data file“. This is a consequence of a lot of page splits where, on disk storage, the new page is not physically contiguous to the original page. The result is increased random I/O, rather than the quicker and more efficient way of reading the data sequentially. In effect, SQL Server reads data from disk, for fragmented indexes, in smaller IO block sizes.

The other consequence of pages splits is that it reduces the page density, simply because each time it happens you end up with two half empty pages, instead of one full one. This is called low page density (or internal) fragmentation. Sparsely populated data pages increase cache size because caching is done at the page level. For subsequent queries and data modifications, more pages will need to be read in memory, or into memory if they are not already there, meaning that highly fragmented indexes need more memory allocation in the buffer cache. Also, any queries that need to scan the index, for example, will likely take longer, because they need to perform many more logical reads, and more, less efficient disk IO operations. Additionally, the query optimizer may unfairly devalue the worth of an index because of the cost of reading all the extra pages that result from fragmentation and chooses instead an alternative plan.

Tweaking the Fill Factor

All indexes, even well-designed ones, are fated to gradually suffer fragmentation as the data changes; but well-designed tables with wisely chosen clustered indexes aren’t as prone to it. The hurly-burly of data updates, causing variable width columns to grow, will eventually spoil any neatness in the physical ordering of data on the leaf pages of any index, because your specified level of initial free-space isn’t maintained.

Sometimes you can reduce fragmentation by changing the underlying design of the table or index (changing the clustering key or changing datatypes) but this is often not possible. Similarly, you will minimize page splits in any index if you avoid updating data in index key columns (not always possible for non-clustered indexes) and minimize changes that cause existing rows to grow. However, if you can’t control fragmentation in these ways, then you can adjust down the fill factor for some of these indexes, once you’ve identified those indexes where the degree of fragmentation, and the problems caused by it, are most acute.

If the index key column is an IDENTITY column, DATETIME or other sequence number, then the key for new rows is always increasing and so the index rows are always added to the end of the index (monotonic increasing key entries). This means that these indexes will never suffer from page splits caused by ‘random’ inserts of new rows into existing pages. In such a design, it is best to stay with the default fill factor value of 100, although these indexes can still become fragmented over time because of updates to variable length columns. Conversely, a large clustered index with a non-sequential primary key such as a GUID may fragment badly, and you may need to consider a lower fill factor, assuming changing the clustering key in not an option.

Always remember though that, as you tweak the fill factor down in order to allow more air into the leaf-level pages of your indexes and so delay page splits, you will then introduce low page density fragmentation every time that index is rebuilt or reorganized! This fragmentation will often go away as the table fills with data, and your allocated spare space is used up, but not if the index key column is an IDENTITY column where new data is always added to the end. Similarly, a high volume of DELETEs can cause low page density too, in indexes with a sequential primary key, because the resulting free space will not be reused.

In addition to all of this, you can also mitigate, or even ignore, the effects of fragmentation by using a larger buffer cache, fast storage such as SSDs, tuning queries to reduce scans, and so on.

Using SQL Monitor to watch for signs of trouble

You’ll only notice the symptoms of fragmentation on the larger indexes with more pages. You’ll notice increasing memory demands, more and slower IO, and more general sluggish performance. Although the clustered index will have more pages, the non-clustered indexes tend to be more prone to fragmentation, but these indexes are generally smaller, so the symptoms are less likely to be a problem.

SQL Monitor can be configured to check all the databases, on all the servers you are monitoring, for signs of larger indexes that are highly fragmented, and tracks page splits. It can also help you narrow down a list of tables whose indexes might need a custom maintenance policy, based on usage and performance statistics, and occurrences of blocking, contention, and waits.

Fragmented indexes

There is a built-in alert (disabled by default) called Fragmented Indexes, which is

“Raised when the fragmentation level of one or more indexes in the database, calculated using sys.dm_db_index_physical_stats, is above a percentage threshold. You can specify that only indexes with more than a specified number of pages are checked for fragmentation.”

If it finds an index that exceeds that threshold, it will fire an alert, the severity of which you can define.

There is also a custom metric variant, Percentage of fragmented indexes, which counts the number of moderate to large indexes (page values > 100) with fragmentation levels greater than 5%, and then divides that number by the number of indexes in the database. Again, you can decide how large the index should be, and what figure constitutes ‘highly fragmented’.

Obviously, an alert from either is just an indication that you need to investigate further (more in the next section) and does not necessarily mean that you need to immediately adjust fill factors, or schedule index rebuilds, for all of them. Using sys.dm_db_index_physical_stats DMF is not cost-free, even in LIMITED mode, which is why the built-in alert in SQL Monitor is off by default. Use it cautiously, especially on large databases.

Page splits

If the table on which the index is created is a large one that is heavily updated and where new rows are frequently inserted, then it may even affect the server metrics such as SqlServer.Page splits/sec or the calculate metric SqlServer.Page splits/Batch request. The problem with the page split metrics, based on the Access Methods perfmon counters (as well the those in sys.dm_db_index_operational_stats) is that SQL Server also counts a normal “new page allocation” as a page split. However, because SQL Monitor tracks these metrics automatically, over time, you can at least plot these metrics on an analysis graph, and determine the trend, as well as the current behavior of either one to its baseline, so you will know if page splits are abnormally high over a period, as a result of high insert or update activity.

Other metrics

The nice thing about using a tool like SQL Monitor, is that you can take decisions on changes, such as reducing fill factor, with all the facts in front of you. You’ll know which are your biggest indexes, as well as the ones subject to frequent writes or heavy reads, or both, based on the observed waits (wait types) and queues (resource metrics) reported by SQL Monitor. For example, if you suspect high page splits are slowing down data modification statements on already-busy indexes, you might expect to observe in SQL Monitor increases in the volume and length of wait types such as PAGELATCH_EX, associated with those statements.

You’ll learn which tables and indexes are already prone to contention and blocking, on resources such as locks and latches, and so where fragmentation will make the problems much worse. If these indexes are also showing high fragmentation, you’ll want to consider adjusting the fill factor.

Alongside this, your SQL Monitor metrics and alerts will be likely to indicate other ways to mitigate the performance impact of page splits and fragmentation. Their impact is far less noticeable, for example, if there is sufficient memory to allow caching of all indexes, or at least your largest, busiest indexes, if possible. Similarly, the performance of queries that read a lot of data from large, fragmented indexes will probably suffer due to the increased IO. Again, alongside consideration of fill factor, you might examine the execution plans of these queries (presented in SQL Monitor’s top 10 queries list) to look for ways to improve existing non-clustered indexes, or add new ones, to replace big range scans with quick index lookup operations.

Investigating the fragmented critters

Once you are alerted to the fact that you may have some highly-fragmented indexes, with page order fragmentation, excessive bloat, or both, how do you detect that, and how do you know that this large index has a poorly-set fill-factor? If you know where to look, you can, of course, check the ‘page fullness’ and total fragmentation of any index by using SSMS and clicking on the properties of the index in the browser pane.

The next stage is to try out some SQL, and the sys.dm_db_index_physical_stats DMV is the place to start. You can also quickly see the fragmentation in a table use the venerable dbcc showcontig command. I use this query.

You can also use sys.dm_os_buffer_descriptors, plus other views, to find indexes that are using memory but have a lot of empty space, but in my tests, this was slower than the above.

Fragmentation and Index Maintenance plans

All the fill factor can do is to postpone the build-up of fragmentation. You will then need to periodically refresh the free space by doing scheduled index rebuilds or reorganizations. The trick is in finding the right fill factor to use, along with the right frequency of index maintenance.

Regardless of the fill factor, you will probably need to defragment certain susceptible indexes at least once a week in a working database to catch fragmentation before it becomes a problem. Your database should already have an index maintenance plan. I use, and recommend, Ola Hallengren’s SQL Server Index and Statistics Maintenance plan. This will just use the fill factor that you provide for each index. No index maintenance plan will help you to provide the right fill-factor.

Redgate also offers a tool, SQL Index Manager, which can help you detect and manage index fragmentation, by rebuilding or reorganizing the index based on configurable threshold values.

Finding the best Fill Factor

The indications for the correct fill-factor are as follows: if the underlying table of an index is almost read-only, only occasionally updated, then a high fill-factor is best. The same is true of an index that is sorted on a column such as an identity column, sequence number or datetime, and whose rows are seldom updated after they are created.

Otherwise, it may be better to experiment with a slightly lower value, making a small change at a time. A fill factor of 90 is a good place to start. The various factors that determine the best value are complicated and on a production server it is best to make gradual adjustments over an extended period, monitoring the results carefully.

It is probably best in a development environment, staging is the ideal place to do this, but it would require a good performance test that simulates typical usage patterns and stresses the server sufficiently for it to be unable to keep the entire index in cache. It is certainly worth doing: You can expect the performance of a table to double, the time to scan a table taken halved, if you manage to get it right.