Chapter 5: Indexing Strategy and Maintenance
- sys.indexes indexing catalog view provides metadata at the index level, such as index identifiers (e.g. index name) and configuration settings (e.g. fill factor).
- You can either create clustered index on a key that naturally reflects the way the data will be queried, or you can cluster on a narrow, ever-increasing integer key (to minimize subsequent fragmentation) and use non-clustered indexes for query efficiency. Most of the advice out there points to the latter approach … P180.
- The ratio of unique values within a key column is referred to as index selectivity. The more unique the values, the higher the selectivity, which means that a unique index has the highest possible selectivity. The query engine loves highly selective key columns, especially if those columns are referenced in the WHERE clause of your frequently run queries. The higher the selectivity, the faster the query engine can reduce the size of the result set. If you've chosen a low selectivity column for the index key (i.e. where each key value matches many rows), then the optimizer may decide to simply perform a table scan to return a piece of data. Table scans have a bad reputation, but this is because they often mean reading a huge number of rows; in small tables, scanning all the rows is sometimes quicker than reading the data from the leaf levels of an index. In creating an index, the leading (first) column should be selective. However, this does not mean each index should start with the PK column; it must be a column that is likely to get searched on… P181
- It is a balancing act; having a huge number of single column indexes (narrow indexes) is a bad idea, as there will be too many indexes to maintain. Also wide indexes is bad, as you will fit few on a data page, index will take up a lot of space, and scanning it will be inefficient… P182.
- sys.dm_db_index_usage_stats DMV is used to obtain statistics on how indexes have been used to resolve queries. Data in this DMV is cumulative, and is refreshed when the server is restarted, when the index is dropped and recreated, or rebuilt or reorganized. Listing 5.3 is an example of how to use it, and the following are some important columns there:
- user_seeks – the number of times the index has been used in a seek operation (to find a specific row)
- user_scans – number of times the index has been used by scanning the leaf pages of the index for data.
- user_lookups – for clustered indexes only, this is the number of times the index has been used in a "bookmark lookup" to fetch the full row; this is because non-clustered indexes use the clustered indexes key as the pointer to the base row
- user_updates – number of times the index has been modified due to a change in the table's data.
- User reads – total number of times that the index is used by the optimizer to resolve a query … P182-184
- Identify indexes that have never been accessed: Listing 5.4 finds those indexes that have had no reads or writes, in other words those indexes that do not show up in sys.dm_db_index_usage_stats. Those indexes can potentially be dropped, after some further investigation ... P185
- Listing 5.5 identifies indexes that are being maintained but not used. You can add the following to the select statement to generate the drop index command that drops the index:
- 'DROP INDEX [' + i.[name] + '] ON [' + su.[name] + '].[' + o.[name]+ '] WITH ( ONLINE = OFF )' AS [drop_command]
- it is always advisable to first check how recently the usage stats were cleared, by running the query in listing 5.6, and also after adequate testing in a non-production environment… P188.
- Listing 5.7: Finding rarely-used inefficient indexes.
- The sys.dm_db_index_operational_stats is a DMF provides index usage statistics at a more detailed level than those provided by the sys.dm_db_index_usage_stats DMV, it offers more detailed information about how the index is used at a physical level, via columns such as leaf_insert_count, leaf_update_count and leaf_delete_count, as well as the nonleaf_* equivalents, for modifications above the leaf level. Also it can provide evidence of potential lock or latch contention on the objects, or of excessive I/O being issued by the object. The data returned by this DMF exists only as long as the metadata cache object that represents the heap or index is available. Application for this is listing 5.8 … P190-193.
- Listing 5.9 uses sys.dm_db_index_operational_stats to return records that relate to locking and blocking at the row level for the indexes of the active database.
- Listing 5.10 highlights which of our indexes are encountering latch contention using the page_io_latch_wait_count and page_io_wait_in_ms columns. I/O latching occurs on disk-to-memory transfers, and high I/O latch counts could be a reflection of a disk subsystem issue, particularly when you see average latch wait times of over 15 milliseconds.
- The query in Listing 5.11 uses sys.dm_db_index_operational_stats DMV to provide information on the count of attempts made by SQL Server to escalate row and page locks to table locks for a specific object, and the percentage success in performing the escalation.
- Listing 5.12 identifies indexes associated with lock contention, and it is a good next step after querying sys.dm_os_wait_stats DMV (discussed in Chapter 7), and the outcome points to locking problems.
- Finding Missing Indexes: information about missing indexes is exposed via the following four MDOs: sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_group_stats, and sys.dm_db_missing_index_groups. The data stored by each of these DMOs is reset on a server restart. You can find a brief explanation of those DMOs and a review for the most significant columns in P199-204. You should never just blindly add every index that these DMOs suggest, instead, you need to examine the results of the query carefully and manually filter out results that are not part of your regular workload. Listing 5.13 puts those DMOs to good use and find the most beneficial missing indexes. The following is brief explanation of the returned columns:
- Index_advantage: overall benefit of a suggested index, or the importance of creating the suggested index. The higher this value is, the more important the index is.
- Statement: the table or view name.
- equality_columns: the columns that would have been useful, based on an equality predicate.
- inequality_columns: the columns that would have been useful, based on an inequality predicate (i.e. any comparison other than "column = value")
- included_columns: columns that, if included, would have been useful to cover the query.
- unique_compiles: the number of plans that have been compiled that might have used the index.
- user_seeks: the number of seek operations in user queries that might have used the index.
- avg_total_user_cost: average cost saving for the queries that could have been helped by the index in the group
- avg_user_impact: the estimated percentage by which the average query cost would drop, for the queries that could use this index.
- When creating the suggested indexes (after careful consideration), list the equality columns first (leftmost in the column list), then list the inequality columns after the equality columns (to the right of equality columns listed). Be warned that the order in which these DMOs list missing columns does not accurately suggest the correct column order for an index key. To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first… P205-207.
- Finding Indexes Fragmentation: The sys.dm_db_index_physical_stats DMF provides invaluable information regarding the state of your index partitions, including type of index, index depth, and degree of fragmentation. This DMF accepts a number of parameters, one of them “mode”, which determines the thoroughness with which the statistics are collected. Possible values are:
- LIMITED: is the default and the least costly, will not scan the leaf level of the indexes and the data pages of heaps are not scanned
- SAMPLED: mode returns statistics based only upon a 1% sample of all pages of the indexes and heaps in the scope of the function call; if any page or heap within the scope of the function call has less than 10,000 pages, then DETAILED is automatically used.
- DETAILED provides the most complete result set from this function of the three, but can require ample resources; it scans all pages; it returns all statistics.
- This DMF returns a large number of columns and we'll only review a subset of them in P211. The query in Listing 5.14
- will return fragmentation information for each index in the current database, where the average_fragmentation_in_percent column is greater than 15% and where the page count is greater than 500. On a busy system, this can be a resource-intensive query. The rule of thumb for index reorganization is 15–30% fragmentation. The often recommended process for indexes with fragmentation greater than 30% is a rebuild of the index.
Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476
Chapter 5: Missing Indexes
- Is it better to create multiple indexes, where each individual index has the optimal key column order, based on column selectivity, or to create a single index that covers multiple queries, but has a less selective column order? For a data warehouse, where there are significantly more read operations than write operations, the multiple indexes option may be appropriate. For an OLTP system, where there are more writes than reads, a less selective index that covers multiple queries using a less than optimal column order may be most appropriate … P165.
- A query cannot seek on an index unless the query filters on a left-based subset of the index key. In other words, it is often (though not always) best to order the index key columns such that the most selective column is the first column in the index. This reduces the number of database pages that must be read by the database engine while traversing the index, in order to satisfy the query.
- Listing 5.1 demonstrates this. Listing 5.2 is a solution for Listing 5.1 … P166.
- A covering index is one that contains all of the columns needed by a query, as either key or non-key columns, preventing the need to access the table or clustered index using lookup operations, and so decreasing the number of I/O operations required to return the data … P169.
- In data warehouse environments having indexes with long included columns is acceptable, and it is recommended to disable or drop indexes during ETL to eliminate the impact of indexes maintenance. In contrast, OLTP DB would generally use fewer included columns due to the impact on the performance of data manipulation operations … P170.
- You can use the following to identifying missing indexes:
- Database Engine Tuning Advisor: can be used to analyze a single query or an entire database workload, in the form of a trace file generated by SQL Server Profiler. The quality of the DTA's index analysis will only be as high as the quality of the workload that is provided, thus the trace in SQL Server Profiler should be run for a period that convers a full cycle of the work load and covers most of the queries. P174-180 shows how.
- Missing index feature: the database engine tracks information about indexes that do not exist but that the optimizer could have used during query execution. This information is stored in the missing index DMVs, and in the MissingIndexGroup in showplan for a query. Those missing indexes must be used with due care, to avoid overlapping or duplicate indexes, as those missing index recommendations are not workload based; they are derived from the execution of individual queries …P181. (Listing 5.10 is XQuery to the ShowPlan XML)
- Missing index DMVs:
- sys.dm_db_missing_index_details – stores detailed information regarding indexes the optimizer would have used had they been available, such as columns that could have been used to resolve equality or inequality predicates, and suggested INCLUDE columns for covering a query.
- sys.dm_db_missing_index_columns – accepts an index_handle and returns a list of columns that would comprise the suggested index.
- sys.dm_db_missing_index_group_stats – returns summary information regarding the potential benefit of a "missing" index, based, for example, on the number of seeks and scans that would have benefited.
- sys.dm_db_missing_index_groups – a join view between _group_stats and _index_details … P182.
- Missing index DMVs limitations:
- Its information exists in memory and doesn't exist beyond SQL service restarts.
- Index key columns are not ordered according to cardinality … P182.
- To estimate performance improvement associated with a specific missing index in the database use the query in Listing 5.9 … P183. Focus on the indexes with an impact value higher than 50,000.
- A good rule of thumb is for any FOREIGN KEY columns that are commonly used in JOIN operations to have an associated index, either with the FOREIGN KEY column as the leading column in the index, or as a column further down the index key, depending on the queries. The code in Listing 5.11 can be used to identify non-indexed FOREIGN KEY columns in a database. That query is effective for database using single-column PRIMARY KEYs, yet partially helpful … P188.
- The query shown in Listing 5.12, is any non-clustered index that has never been used for a seek, scan, or lookup operation by SQL Server, but is associated with a significant number of update operations. These indexes can be considered to be unused and should be dropped from the database. This code should only be used when the database has been online for a significant period of time, in order to ensure that the appropriate workload has been executed. … P190.
- In her following two blog posts, Kimberly Tripp offers further insight into the complications of identifying duplicate indexes, and provides a stored procedure for identifying and removing them: "How can you tell if an index is REALLY a duplicate?" (http://sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx) and "Removing duplicate indexes" (http://sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx) … P191.
