Understanding SQL Server Query Optimization – Part 3
Editor’s Note: In the 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
•sys.dm_db_missing_index_groups – Returns information about missing indexes contained in a missing index
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
returned represents, in many cases, statistics collected since the last time
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
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:
SELECT * FROM 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] ,id.[equality_columns] ,id.[inequality_columns] ,id.[included_columns] ,gs.[unique_compiles] ,gs.[user_seeks] ,gs.[user_scans] ,gs.[last_user_seek] ,gs.[last_user_scan] ,gs.[avg_total_user_cost] ,gs.[avg_user_impact] ,gs.[system_seeks] ,gs.[system_scans] ,gs.[last_system_seek] ,gs.[last_system_scan] ,gs.[avg_total_system_cost] ,gs.[avg_system_impact] ,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 OPTION (RECOMPILE);
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
• Gathers statistics for no more than 500 index
• 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
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 OPTION (RECOMPILE);
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 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] ,i.[index_id] ,i.[name] ,iops.[partition_number] ,CASE WHEN i.[is_unique] = 1 THEN 'UNIQUE ' ELSE '' END + i.[type_desc] AS [index _type] ,iops.[leaf_insert_count] ,iops.[leaf_delete_count] ,iops.[leaf_update_count] ,iops.[page_latch_wait_count] ,iops.[page_io_latch_wait_count] ,iops.[page_io_latch_wait_in_ms] 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 OPTION (RECOMPILE);
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
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
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.
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”.