Analyzing the performance of your MSSQL indexes with Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs)

What are Dynamic Management Views (DMVs) and Dynamic Management Functions

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are system views and functions in SQL Server that provide information about server state, database state, and various performance-related metrics. They allow administrators and developers to monitor and troubleshoot SQL Server instances, optimize query performance, and gather information about system health, resource usage, and internal operations.

DMVs are system views that return information in the form of a result set, similar to a standard SQL view. They typically have names that start with “dm_” (e.g., sys.dm_exec_requests, sys.dm_db_index_usage_stats).

DMFs are system functions that require input parameters and return a table of values. They typically have names that start with “dm_” as well (e.g., sys.dm_db_index_physical_stats, sys.dm_exec_sql_text).

The primary use cases for DMVs and DMFs

Performance monitoring and tuning

They provide information on query performance, execution plans, missing indexes, and index usage, which can help identify performance bottlenecks and optimize query execution.

Resource monitoring

DMVs and DMFs can provide insights into resource utilization, such as CPU, memory, and I/O usage, allowing administrators to identify resource bottlenecks and allocate resources more efficiently.

Troubleshooting

They can help identify and diagnose issues related to blocking, deadlocks, and other contention problems in the SQL Server instance.

Security and auditing

DMVs and DMFs provide information about user connections, sessions, and permissions, which can be useful for security and auditing purposes.

Internal operations

They expose information about SQL Server’s internal processes, such as the buffer cache, query compilation, and execution, allowing administrators and developers to gain deeper insights into the inner workings of SQL Server.

How to use DMVs and DMFs

The information provided by DMVs and DMFs is transient and represents the current state of the SQL Server instance or database. To maintain historical data for analysis, you’ll need to capture and store the information periodically.

Some DMVs and DMFs may require specific permissions to access. In many cases, you’ll need VIEW SERVER STATE or VIEW DATABASE STATE permissions to query these objects.

In this post we will list the most useful DMVs and DMFs for analyzing index performance.

sys.dm_db_index_usage_stats

This DMV provides information about the usage of each index in the database. You can use this DMV to see how many times each index has been used for user queries and which indexes are not being used at all.

SELECT OBJECT_NAME(ius.object_id) AS TableName,
       i.name AS IndexName,
       ius.user_seeks,
       ius.user_scans,
       ius.user_lookups,
       ius.user_updates
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ius.database_id = DB_ID()
ORDER BY TableName, IndexName;


user_seeks: The number of times the index has been used in a seek operation. An index seek reads only the required rows from the index, which is an efficient way to access data.

user_scans: The number of times the index has been used in a scan operation. An index scan reads all the rows in the index, which can be less efficient compared to an index seek.

user_lookups: The number of times the index has been used in a bookmark lookup operation. A bookmark lookup is when the query retrieves additional columns from the base table or clustered index that are not part of the non-clustered index being used.

user_updates: The number of times the index has been updated due to data modifications (INSERT, UPDATE, DELETE) in the underlying table.

sys.dm_db_index_operational_stats

This DMV provides detailed information about index performance, such as the number of lock requests, page latches, and other index-related operations. This can help you identify which indexes might be causing contention or resource issues.

SELECT OBJECT_NAME(ios.object_id) AS TableName,
       i.name AS IndexName,
       ios.range_scan_count,
       ios.singleton_lookup_count,
       ios.page_latch_wait_count,
       ios.page_io_latch_wait_count,
       ios.page_lock_wait_count,
       ios.row_lock_wait_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
ORDER BY TableName, IndexName;

range_scan_count: The number of range scans initiated on the index. A range scan retrieves a range of rows based on a search predicate.

singleton_lookup_count: The number of singleton lookups performed on the index. A singleton lookup retrieves a single row based on a search predicate.

page_latch_wait_count: The number of page latch wait requests on the index.

page_io_latch_wait_count: The number of page I/O latch wait requests on the index.

page_lock_wait_count: The number of page lock wait requests on the index.

row_lock_wait_count: The number of row lock wait requests on the index.

sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups:

These DMFs provide information about potential missing indexes, which the SQL Server query optimizer has identified as potentially beneficial for query performance.

SELECT mid.statement AS TableName,
       mid.equality_columns,
       mid.inequality_columns,
       mid.included_columns,
       migs.user_seeks,
       migs.user_scans,
       migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

equality_columns: The list of columns in the table that are used in equality predicates (e.g., column = value) in the query and would benefit from being part of the missing index.

inequality_columns: The list of columns in the table that are used in inequality predicates (e.g., column > value) in the query and would benefit from being part of the missing index.

included_columns: The list of columns that are not part of the search predicate but are included in the query’s SELECT, GROUP BY, or ORDER BY clauses and would benefit from being included in the missing index.

user_seeks: The number of user queries that could have benefited from using the missing index.

user_scans: The number of user scans that could have benefited from using the missing index.

avg_user_impact: The average percentage improvement in query performance that could be achieved by using the missing index. The value is a percentage, ranging from 0 to 100. Higher values indicate a higher potential impact on query performance.

How do I know what DMVs and DMFs values mean

Here is a table for you. But please consider the optimal values mentioned in the table are general guidelines. It’s essential to analyze the specific context and workload of your SQL Server instance to determine the best course of action for index optimization.

Column NameDescriptionOptimal Value
user_seeksNumber of times the index has been used in a seek operationHigher
user_scansNumber of times the index has been used in a scan operationLower
user_lookupsNumber of times the index has been used in a bookmark lookup operationLower
user_updatesNumber of times the index has been updated due to data modifications in the underlying tableLower
range_scan_countNumber of range scans initiated on the indexHigher
singleton_lookup_countNumber of singleton lookups performed on the indexHigher
page_latch_wait_countNumber of page latch wait requests on the indexLower
page_io_latch_wait_countNumber of page I/O latch wait requests on the indexLower
page_lock_wait_countNumber of page lock wait requests on the indexLower
row_lock_wait_countNumber of row lock wait requests on the indexLower
equality_columnsColumns used in equality predicates that would benefit from being part of the missing indexN/A
inequality_columnsColumns used in inequality predicates that would benefit from being part of the missing indexN/A
included_columnsColumns to be included in the missing index that are not part of the search predicateN/A
user_seeks (missing index)Number of user queries that could have benefited from using the missing indexHigher
user_scans (missing index)Number of user scans that could have benefited from using the missing indexHigher
avg_user_impactAverage percentage improvement in query performance by using the missing indexHigher
DMVs and DMFs values explanation for SQL indexes

By analyzing the information provided by these DMVs and DMFs, you can identify which indexes are being used effectively, which indexes might need optimization, and if there are any missing indexes that could potentially improve query performance.

Keep in mind that these tools provide information based on the current workload of your SQL Server instance, so it’s essential to analyze them during periods of typical workload to get an accurate understanding of index usage and performance. Additionally, always test any index changes in a non-production environment before implementing them in production to ensure that the changes have the desired effect.

Leave a comment

Your email address will not be published. Required fields are marked *