Submitted by Andrey Gamarnik on Wed, 06/13/2012 - 17:28
Recently we were asked to develop a tools set for monitoring SQL queries performance in one of the ASP.NET plus MS SQL project we're working on. In particular, we were required to make some pages with reports containing information about the heaviest stored procedures and any other relevant information.
Customer suggested using Dynamic Management Views and Functions himself. Honestly, I've heard about DMV's first time myself, therefore asked for help from our SQL gurus. We have a very strong SQL Server team, and there was no question remaining unanswered before! Though, strangely, no one know about DMVs, this appeared to be quite a specific thing. «Well, that's even more interesting» I thought, and started reading the manuals. So, below is the overview article for beginners, with examples of interesting queries, which we used when working in our projects.
Execution Related Dynamic Management Views and Functions
And let's start right from the example. Top 10 stored procedures, ordered by amount of processor time, spent for execution. Timer is started from the moment of the last compilation of stored procedure. In this example I've left worker_time only, but it's also possible to retrieve physical_reads, logical_reads, logical_writes, elapsed_time.
SELECT TOP 10 OBJECT_NAME(object_id, database_id) AS [ProcedureName],
ps.cached_time AS [TimeCached],
ps.execution_count AS [ExecutionCount],
ps.total_worker_time AS [TotalExecutionTime],
ps.last_worker_time AS [LastExecutionTime],
ps.total_worker_time / ps.execution_count AS [AverageExecutionTime]
FROM sys.dm_exec_procedure_stats AS ps
WHERE database_id = DB_ID('DatabaseName')
ORDER BY [TotalExecutionTime] DESC
From other tables it's possible to get information about execution plan (plan_handle) and SQL query text (sql_handle). This is one of the most useful and widely used views, but this category contains a lot of other handy views.
Index Related Dynamic Management Views and Functions
Indexes are the corner-stone of the SQL queries performance, and each professional SQL developer creates proper indexes as a must. But how to see if they're working efficiently? How to understand which indexes are missing? What about their fragmentation?
As the first try let's define, which indexes do we have at all. The most easy way is to use well-known stored procedure sp_helpindex, but it returns only three columnds: index_name, index_description and index_keys.
EXEC sp_helpindex 'TableName'
Therefore, if you need additional information, you got to create several queries. Here are the queries, created by us to get included columns amongst all other information.
-- Get table indexes
SELECT
[index_id] AS [IndexID],
[name] AS [Name],
[type_desc] AS [Type],
[is_unique] AS [IsUnique],
[is_primary_key] AS [IsPramaryKey],
[is_unique_constraint] AS [IsUniqueConstraint]
FROM sys.indexes
WHERE object_id=object_id('TableName')
ORDER BY IndexID
-- Get table columns that used in indexes
SELECT
[index_id] AS [IndexID],
c.column_id AS [ColumnID],
[name] AS [Name],
is_included_column AS [IsIncludedColumn]
FROM sys.columns c
INNER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE c.object_id = object_id('TableName')
ORDER BY IndexID, ColumnID
Similar thing can be achieved by sp_helpindex2, written by enthusiasts. It extends capabilities of sp_helpindex and returns additional information about indexes, but for several reasons it didn't work for us.
Oh, and by the way, looks like I forgot about the most important part. Previous two queries weren't the uttermost goal of our work. Really useful information is returned by the query below, which are the columns of the table, for which indexes are not created, but against which the search is executed. How does this work? When SQL Server generates query execution plan, it tries to find the most suitable indexes. But if it doesn't find them, then it records this info into view data.
SELECT
mid.equality_columns AS EqualityColumns,
mid.inequality_columns AS InequalityColumns,
mid.included_columns AS IncludedColumns,
ROUND((avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans), 2) AS Impact,
ROUND(avg_total_user_cost, 2) AS AverageTotalUserCost,
ROUND(avg_user_impact, 2) AS AverageUserImpact,
user_seeks AS UserSeeks,
user_scans AS UserScans
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects so WITH (nolock) ON mid.object_id = so.object_id
WHERE so.name = 'TableName' AND migs.group_handle IN (
SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)
ORDER BY Impact DESC
In addition to other info, views from this category can provide information about the indexes fragmentation and usage statistics. But in most cases, indexes fragmentation problem is usually resolved with periodic defragmentation. So, we didn't need this statistics.
References
So, that's it! There are about 20 categories of different views and functions. In this article I've told only about those, which we used in our projects. Hope, this was an interesting one. And if yes, you can go though the links below and look what else interesting you can get from DMV's.
- Highly recommend to read an article by George Heynen – Best Practices for Dynamic Management Views;
- Few other interesting queries examples in the article Dynamic Management Views – A SQL Server 2005 Feature;
-
And for sure MSDN portal documentation, which I've already referenced several times.