Turn: a new tool for diagnosing index soundness

Overview:

• Index fragmentation
• Index usage
• Index operation activity

Download this article Article Of Code : Dyesssqlindex2007_03.exe (151kb)

One of the main items in the SQL server performance adjustment checklist is to adjust the indexes in the database. Whether the SQL Server Query Optimizer correctly uses indexes during query execution depends not only on whether a valid index is created, but also on whether the index is sound. With a series of dynamic management views (DMV) and functions (DMF) introduced in SQL Server 2005, database administrators can determine the validity of their indexes and discover any performance problems. DMV and DMF allow you to gain an in-depth understanding of the server and return server status information, which helps you monitor the running status and performance of the server instance and diagnose problems. Database Administrators familiar with earlier SQL Server versions will find that these DMV and DMF will replace the following operations: use DBCC commands, execute certain system stored procedures, query many system tables, and capture events using SQL event probes. Three important functions and views (SYS. dm_db_index_physical_stats, SYS. dm_db_index_usage_stats, and SYS. dm_db_index_operational_stats) are provided to help you understand whether indexes work as expected. You can use these to view the index I/O and lock modes, and check whether the query optimizer uses the index method to avoid unnecessary contention issues in the database.

Index fragmentation: SYS. dm_db_index_physical_stats DMF is created to replace DBCC showcontig and display the index fragmentation. However, DBCC showcontig places the shared lock (s) on the table containing the index, while sys. dm_db_index_physical_stats only places one intent share lock (is), which greatly reduces table blocking during function execution. To determine the index fragmentation found by using SYS. dm_db_index_physical_stats, combine the three columns in the output result of this function for inspection. Check the value returned in the avg_fragmentation_in_percent column to determine the logical fragmentation of the index (heap partition fragmentation ). Logical fragmentation is the percentage of page with disordered order at the leaf level of the index, while partition fragmentation is the percentage of area with disordered order at the leaf level of the index. Because the head can only read pages in sequence from left to right, the logical fragment and partition fragment will affect the index performance because it requires additional I/O and head movements. Make sure that the logical and partition fragments are close to zero. The internal fragmentation of the index is the percentage of page padding. Of course, you want to fill up the index pages as much as possible, but you also need to balance the fill degree based on the number of pages inserted into the index page to ensure that the number of page partitions is absolutely the minimum. Check the avg_page_space_used_in_percent parameter of SYS. dm_db_index_physical_stats to determine the index page padding. To correctly configure this number so that it is close to 100% as much as possible, observe the number of page splits while adjusting the index fill factor. At a certain moment, the number of page segments will start to increase dramatically, which indicates that the index fill factor value you set is higher than the expected value. Adjusting the index fill factor takes a certain amount of time and needs to be tested, and must be properly planned in advance. (If the index is not inserted at will, you can set the index fill factor to 100 without worrying about the number of page splits .) To determine the fragmentation level of all indexes in the adventureworks. HumanResources. Employee table, you can use the following statement:

Copy code

 
Select * From sys. dm_db_index_physical_stats (db_id ('adventureworks'), object_id ('humanresources. employee '), null -- null to view all indexes; -- otherwise, input index number, null -- null to view all partitions of an index, 'detailed') -- we want all information
 

With this DMF, You can automatically determine which indexes need to be rebuilt, which need to be restructured, and which do not need to be maintained. Check avg_page_space_used_in_percent and avg_fragmentation_in_percent of this DMF column to obtain the index fragmentation that exceeds the recognized logical and density threshold, which helps you determine which operation to perform on the index. Based on your index status,Figure 1The example shown in may not return data in the copy of the adventureworks sample database, but it can be applied to other databases after a simple adjustment.

Figure 1 Check page filling and fragmentation

Copy code

-- Reorganize the following indexes in the adventureworks databaseuse adventureworksgoselect object_name ([object_id]) as 'table name', index_id as 'index id' from sys. dm_db_index_physical_stats (db_id ('adventureworks'), null -- null to view all tables, null -- null to view all indexes; otherwise, input index number, null -- null to view all partitions of an index, 'detailled') -- we want all informationwhere (avg_fragmentation_in_percent> 10 and avg_fragmentation_in_percent <15) -- Logical fragmentationor (avg_page_space_used_in_percent <75 and percentage> 60) -- page densityand page_count> 8 -- we do not want indexes less than 1 extent in sizeand index_id not in (0) -- only clustered and nonclustered indexes -- rebuild the following indexes in the adventureworks databaseuse adventureworksgoselect object_name ([object_id]) as 'table name', index_id as 'index id' from sys. dm_db_index_physical_stats (db_id ('adventureworks'), null -- null to view all tables, null -- null to view all indexes; otherwise, input index number, null -- null to view all partitions of an index, 'detailled') -- we want all informationwhere (avg_fragmentation_in_percent> 15) -- Logical fragmentationor (avg_page_space_used_in_percent <60 )) -- page densityand page_count> 8 -- we do not want indexes less than 1 extent in sizeand index_id not in (0) -- only clustered and nonclustered Indexes

Store the query results in a table variable and traverse the table variable to generate a dynamic string for the correct alter index statement. This is easy to implement (seeFigure 2).

Figure 2 generate a dynamic alter index string

Copy code

-- Rebuild the following indexes in the adventureworks databaseuse adventureworksgo -- table to hold resultsdeclare @ tablevar table (lngid int identity (1,1), objectid int, index_id INT) insert into @ tablevar (objectid, index_id) select [object_id], index_idfrom sys. dm_db_index_physical_stats (db_id ('adventureworks'), null -- null to view all tables, null -- null to view all indexes; otherwise, input index number, null -- null to view all partitions of an index, 'detailled') -- we want all informationwhere (avg_fragmentation_in_percent> 15) -- Logical fragmentationor (avg_page_space_used_in_percent <60 )) -- page densityand page_count> 8 -- we do not want indexes less than 1 extent in sizeand index_id not in (0) -- only clustered and nonclustered indexesselect 'alter Index' + ind. [name] + 'on' + SC. [name] + '. '+ object_name (objectid) + 'rebuilt' from @ tablevar tvinner join sys. indexes indon TV. objectid = ind. [object_id] and TV. index_id = ind. index_idinner join sys. objects obon TV. objectid = ob. [object_id] inner join sys. schemas scon SC. schema_id = ob. schema_id

Index usage despite replacing the sys of DBCC showcontig. dm_db_index_physical_stats is powerful and helps to check whether the index is sound. However, you may frequently encounter more complex problems, such as determining which indexes can be used for queries on tables. Generally, database developers or administrators generate indexes for the tables they think will be used by the query optimizer during query execution. In earlier versions of SQL Server, it is difficult to know whether these indexes are in use. You can only delete the corresponding index and check whether the query performance is affected, or capture the query execution plan and then scan the index usage. Now you have a new dynamic management view-SYS. dm_db_index_usage_stats, so that you can easily learn about the index used by the query optimizer and query execution for tables. You can check this view to determine the usefulness of indexes, so that you can delete any indexes that the query optimizer is not using. You don't have to worry about whether the index is just a waste of storage space, or the maintenance of useless indexes will reduce the database performance. By checking the DMV output results, you can obtain indexes that have not been searched and scanned, so that you can determine whether an index has been used since the last time SQL Server was started. However, remember that many DMV and DMF are not permanently valid. Once the SQL Server is restarted, it will reset itself to zero. Consider this when using DMV or DMF to determine the index usage. An index may not have been used since the last restart of the service, but this index is required for weekend, end of month, or quarterly report query. To view all indexes that have never been used in an instance since the last SQL Server service was restarted, use the following statement:

Copy code

Select db_name (database_id), object_name ([object_id]) from SYS. Required user_seeks = 0and user_scans = 0and user_lookups = 0and system_seeks = 0and system_scans = 0and system_lookups = 0
 

If you want to know the index operation activity, SYS. dm_db_index_operational_stats DMF will be helpful to you. You can use it to view the I/O, locking, locking, and access method activities of each index in the database, they help you understand the usage of indexes and diagnose index locking problems caused by excessive I/O activities or "hot spots" in indexes. Using this DMF lock wait column can help determine the amount of time the read and write operations have taken to obtain access permissions for an index resource. This helps you determine whether the disk subsystem used to store indexes is sufficient for index I/O activities. It can also indicate whether the design and usage of indexes have triggered hot spots. The hot spot is caused by frequent activity on one or more pages of the index, resulting in competition for the data contained in these pages. Such contention often causes a lot of blocking for the read or write operations attempted in the region.Figure 3Shows how to determine the locking and I/O modes of all indexes in the adventureworks. HumanResources. Employee table.

Figure 3 determine the locking and I/O Modes

Copy code

Select page_latch_wait_count -- page latch counts, comment -- page latch wait times, comment -- Row lock wait times, comment -- page lock wait times, row_lock_count -- Row lock counts, page_lock_count -- page lock counts, page_io_latch_wait_count -- I/O wait counts, page_io_latch_wait_in_ms -- I/O wait timesfrom sys. dm_db_index_operational_stats (db_id ('adventureworks'), object_id ('humanresources. employee '), null -- null to view all indexes; otherwise, input index number, null -- null to view all partitions of an index)
 

In addition to the functions described here, DMV and DMF described in this article have many other functions. In the "SQL Server books online" article, I introduced the functions and views in the "Other Resources" sidebar. Please take a moment to review them, to learn more information that can be obtained and accessed using them. To learn about some of the other indexes DMF and DMV that are not discussed in this article, refer to the blog post published by the SQL Server Query Optimization Team at blogs.msdn.com/queryoptteam/570176.aspx.