Maintaining SQL Server indexes is an uncommon practice. If a query stops using indexes, oftentimes a new non-clustered index is created that simply holds a different combination of columns or the same columns. A detailed analysis on why SQL Server is ignoring those indexes is not explored.
Let's take a look at how clustered and non-clustered indexes are selected and why query optimizer might choose a table scan instead of a non-clustered index. In this tip, you'll learn how page splits, fragmented indexes, table partitions and statistics updates affect the use of indexes. Ultimately, you'll find out how to maintain SQL Server indexes so that query optimizer uses these indexes, and so these indexes are searched quickly.
Index selection
Clustered indexes are by far the easiest to understand in the area of index selection. Clustered indexes are basically keys that reference each row uniquely. Even if you define a clustered index and do not declare it as unique, SQL Server still makes the clustered index unique behind the scenes by adding a 4-byte "uniqueifier" to it. The additional "uniqueifier" increases the width of the clustered index, which causes increased maintenance time and slower searches. Since clustered indexes are the key that identifies each row, they are used in every query.
When we start talking about non-clustered indexes, things get confusing. Queries can ignore non-clustered indexes for the following reasons:
Page splits
To store data, SQL Server uses pages that are 8 kb data blocks. The amount of data filling the pages is called the fill factor, and the higher the fill factor, the more full the 8 kb page is. A higher fill factor means fewer pages will be required resulting in less IO/CPU/RAM usage. At this point, you might want to set all your indexes to 100% fill factor; however, here is the gotcha: Once the pages fill up and a value comes in that fits within a filled-up index range, t
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
hen SQL Server will make room in an index by doing a "page split." In essence, SQL Server takes the full page and splits it into two separate pages, which have substantially more room at that point. You can account for this issue by setting a fill-factor of 70% or so. This allows 30% free space for incoming values. The problem with this approach is that you continually have to "re-index" the index so that it maintains a free space percentage of 30%.
Clustered index maintenance
Clustered indexes that are static or "ever-increasing" should have a fill factor of 100%. Since the values are always increasing, pages will just be added to the end of the index and virtually no fragmentation will occur. For a more detailed explanation, see part 1 of this series, SQL Server clustered index design for performance. This index category does not need to be re-indexed because it doesn't fragment.
Clustered indexes that are either not static or "ever-increasing" will experience fragmentation and page splits as the data rows move around within the data pages. The indexes in this category have to be re-indexed in order to keep fragmentation low and allow queries to efficiently use the index. When you re-index these clustered indexes, you have to decide what the fill factor should be. Normally this is 70% to 80%, giving you 20% to 30% empty space for new records coming into the page. The optimal settings for your environment will depend on how often records shift around, how many records are inserted and how often re-indexing occurs. The goal is to set a fill factor low enough so that by the time you reach your next maintenance cycle, the pages are around 95% full, but not yet splitting, which happens when they hit the 100% limit.
Non-clustered index maintenance
Non-clustered indexes will always have data shifting around the pages. It's not quite as big of an issue like it is with clustered indexes -- the actual row data shifts with clustered indexes, whereas only row pointers shift with non-clustered indexes. That said, the same rules apply to non-clustered indexes as far as fill factors go. Again, the goal is to set a fill factor low enough so that by the time you reach your next maintenance cycle, the pages are only around 95% full.
Non-clustered indexes will always fragment, and to avoid this you must constantly monitor and maintain them.
Partitioned table index considerations
Partitioned tables allow data to be segregated into different partitions, depending on the data in a column. Many tables are partitioned based on date ranges. Let's say your order table is partitioned into years. Assuming the clustered index is aligned (see part 1 of this series), then you could re-index the non-clustered indexes for, say, year 2000 at 100% fill factor, since that data, technically, won't be shifting around. In this scenario, the year 2008 partition may have a fill factor of 70% on non-clustered indexes to allow for data shifts, but the year 2000 will not have any shifts and can be re-indexed at 100% fill factor so you optimize index seeks.
The same concept would apply to clustered indexes that are either not static or ever-increasing. Clustered indexes with shifting data might be set to 70% fill factor for the year 2008 partition and 100% fill factor for the year 2000.
SQL Server statistics
Statistics are maintained on columns and indexes and they help SQL Server determine how "unique" some value may be -- i.e., if statistics say a value will match approximately 80% of the rows, SQL Server will do a table scan instead. If statistics say a value will probably match around 10% of the rows, then the query optimizer will opt for a seek to minimize database impact.
SQL Server statistics can be maintained automatically or you can run them manually. Since re-indexing changes the statistics results, I recommend that after re-indexing, you manually run sp_updatestats or the T-SQL UPDATE STATISTICS command. Statistics are only maintained on the first column of any compound index, so the "uniqueness" of other columns in the index cannot be determined.
Summary
Index maintenance is critical to ensure that queries continue to benefit from index use and to reduce IO/RAM/CPU, which reduces blocking as well.
Run your queries with the option "show execution plan" turned on. If the query is not using your index, then check the following:
[TABLE]
