Understanding SQL Server Query Optimization – Part 3

first of this four part series, I gave an overview of the tools and techniques of query optimization in SQL Server. In the second part, I talked about the process of displaying and interpreting query execution plans. I also showed how to generate query execution plans usin gSQL Server Management Studio, SHOWPLAN_TEXTand SHOWPLAN_ALL. In this third part, I will discuss about the index-related dynamic management functions (DMFs) and views (DMVs).


A critical part of database design and management is index design. Index design involves balancing space requirements and the resource overhead required to keep indexes up-to-date compared to the performance improvements resulting from the indexes. You can either use dynamic management functions (DMFs) and views (DMVs) or Database Engine Tuning Advisor to identify index needs based representative server activity.

Index Related Dynamic management functions and views

Microsoft SQL Server provides database administrators (DBAs) and developers with several index related dynamic management views and functions that are especially helpful in query tuning. You can use these DMFs and DMVs to maintain, modify, tune and identify missing indexes. Some of the views you might use include:

sys.dm_db_index_operational_stats – Returns information about low level input/output (I/O) operations, locking and latching contention, and access methods.
sys.dm_db_index_usage_stats – Returns an index operations count, identifying operations performed and the last time each operation was performed.
sys.dm_db_index_physical_stats – Returns information about table and index size and fragmentation, including both in-row and LOB data.
sys.dm_db_missing_index_details – Returns detailed information about missing indexes, including the table, columns used in equality operations, columns used in inequality operations, and columns used in include operations.
sys.dm_db_missing_index_group_stats – Returns information about groups of missing indexes, which SQL Server updates with each query execution (not based on query compilation or recompilation).
sys.dm_db_missing_index_groups – Returns information about missing indexes contained in a missing index group.

You use the SELECT statement to query DMVs and DMFs and retrieve current information. For example, to view missing index details, you run:

SELECT * FROM sys.dm_db_missing_index_details

Data returned represents, in many cases, statistics collected since the last time SQL

Server was restarted. You might need to restart the server to get accurate information, especially when verifying the effect of changes to the database and indexes. Because dynamic management functions and views return implementation-specific information, details for what they return and required parameters could change in future SQL Server releases. You should limit your use of dynamic management functions and views to ad hoc queries rather than embedding them in stored procedures, functions, or application calls to prevent compatibility problems with future releases.

The dynamic management parameters vary by function or view. A detailed discussion of required parameters of all these DMVs and DMFs are beyond the scope of this article, and the main focus of this article is to cover missing indexes and index usage dynamic management functions and views.

Missing Index dynamic management function (DMFs)

The missing index dynamic functions are especially helpful in query tuning. You can use the information returned by these functions to create covering indexes for queries. The only missing index function that requires an additional parameter is sys.dm_db_missing_index_columns, which requires an index ID using the syntax:

sys.dm_db_missing_index_columns( indexID )

You can retrieve the indexID value from sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, or sys.dm_db_missing_index_groups. You use the information from sys.dm_db_missing_index_columns to identify suggested key columns. For example, you can use the following query, which I wrote using these dynamic management functions to identify missing in the current database by index advantage:

        SELECT id.[statement] AS [database.schema.table]
       ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [index_advantage]
       ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
             WHEN id.[equality_columns] IS NOT NULL
                    AND id.[inequality_columns] IS NOT NULL
                    THEN '_'
             ELSE ''
             END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
             WHEN id.[equality_columns] IS NOT NULL
                    AND id.[inequality_columns] IS NOT NULL
                    THEN ','
             ELSE ''
             END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [proposed_index]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE id.[database_id] = DB_ID()
ORDER BY [index_advantage] DESC

The information provided by missing indexes dynamic management functions is recommended as a general guideline only. The indexes identified are the ones that SQL Server optimizer identified during compilation, and these missing index recommendations are specific recommendation targeting a specific query. You shouldn’t blindly add indexes or use the information returned by these dynamic functions to for fine-tuning your database. This is because, there are some concerns relating to using missing indexes dynamic management views:

• Gathers statistics for no more than 500 index groups.
• Doesn’t specify a suggested column order.
• Columns can appear in multiple groups.
• Is less accurate when inequality operations are involved.

For accurate identifying database missing index, consider submitting your workload and the proposed index to the Database Tuning Advisor for further evaluation that include partitioning, choice of clustered versus non-clustered index, and so on. I will cover the Database Tuning Advisor tool in detail in the fourth part of this article series.

Index usage dynamic management views and functions

We have the following dynamic management view and function that provides the existing indexes usage stats:sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats. The information return by them is useful to identify the potential bad indexes. For example, the following query uses sys.dm_db_index_usage_stats, and returns the list of possible bad non-clustered Indexes within the given database:

        -- Ensure a USE statement has been executed first.
SELECT OBJECT_NAME(d.[object_id]) AS [TableName]
              ,i.[name] AS [IndexName]
              ,i.[index_id] AS [IndexID]
              ,d.[user_updates] AS [TotalWrites]
              ,d.[user_seeks] + d.[user_scans] + d.[user_lookups] AS [TotalReads]
              ,d.[user_updates] - (d.[user_seeks] + d.[user_scans] + d.[user_lookups]) AS [Difference]
FROM [sys].[dm_db_index_usage_stats] AS d WITH (NOLOCK)
INNER JOIN [sys].[indexes] AS i WITH (NOLOCK)
    ON d.[object_id] = i.[object_id]
       AND i.[index_id] = d.[index_id]
WHERE OBJECTPROPERTY(d.[object_id], 'IsUserTable') = 1
       AND d.[database_id] = DB_ID() --Returning the database ID of the current database
       AND d.[user_updates] > (d.[user_seeks] + d.[user_scans] + d.[user_lookups])
       AND i.[index_id] > 1
       AND i.[is_primary_key] = 0
       AND d.[last_system_scan] IS NOT NULL
ORDER BY [Difference] DESC
        ,[TotalWrites] DESC
        ,[TotalReads] ASC

This is because, sys.dm_db_index_usage_stats returns an index operations count, identifying operations performed and the last time each operation was performed. This query is a good point for identify bad non-cluster indexes that exists inside user databases because in this query, I took the www.gulfportpharmacy.com/valium approach of finding the possible non-clustered indexes by identifying non-clustered indexes that have high number of writes (user_updates), but have comparatively very low number of reads (user_scans + user_seeks + user_lookups) as compared to writes.

You can also use sys.dm_db_index_operational_stats dynamic management function to determine number of update, insert, and delete operations against each index of the database. This information is useful to identify and remove unused indexes accurately. See example query below:

-- Ensure a USE statement has been executed first.
SELECT DB_NAME([database_id]) AS [database]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id]))
        + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [object_name]
        WHEN i.[is_unique] = 1
            THEN 'UNIQUE '
        ELSE ''
        END + i.[type_desc] AS [index _type]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i ON i.[object_id] = iops.[object_id]
    AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC

There are also other uses of this dynamic management function. For example, you can use it to how many times SQL Server database Engine row or page lock occurred (see below):

The sys.dm_db_index_operational_stats dynamic management function provides us the current low-level I/O, locking, latching, and access method for each partition of the table. This information is really useful to troubleshoot SQL Server performance issues.

Key considerations for using these index-related dynamic management functions (DMFs) and views (DMVs)

SQL Server cache is flushed whenever you restart SQL Server instance, and is not persisted across instance restarts. All cache entries for indexes in a database are removed when the database status is changed to read_only. Moreover, rebuilding an index clears the stats from SQL Server cache for the index involved.

In addition to this, it is also important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. Consider your complete workload and investigate further before dropping an index.

What Next?

For more information about index related views and functions, refer to SQL Server Books Online for the article “Index Related Dynamic Management Views and Functions”.


Leave a comment

Your email address will not be published.