SQL Server 2005 and SQL Server 2008 have many useful Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) that have been introduced that provide insight into how SQL Server is operating. The difference between a DMV and DMF is that a DMF takes parameters like any other function and a DMV works just a regular view.
The collection of Dynamic Management Objects (DMOs), the combination of DMVs and DMFs, is pretty vast and maybe overwhelming to determine which ones you should be using. So in this tip we will focus on DMOs realted to query statistics and current connections that allow you to see what statements have been run, how frequently and the resources they are using.
In this tip we will cover these DMOs. The first 3 provide data similar to Activity Monitor or runnig sp_who2. The last 4 provide detailed info about the actual queries that have been run.
Note: sys.dm_exec_query_stats stores cumulative data since the last time SQL Server was started, so when you do a restart of SQL Server the data in this view is reset.
These DMOs can be queried just like any other view as shown below.
You can see for the last two examples we used the DMFs along with the CROSS APPLY operator. The CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function . You can read more about this in Books Online.
SELECT * FROM sys.dm_exec_connections
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_exec_query_stats
SELECT * FROM sys.dm_exec_cached_plans
SELECT * FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
SELECT * FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Here is a query that uses a combination of DMOs. This query returns the command text for what is runnnig, the query plan as well as some additional columns from the views.
P.[query_plan],
S.[program_name],
S.[host_name],
S.[client_interface_name],
S.[login_name],
R.*
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS P
This query will return the top 5 statements that have the highest average CPU time.
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC
This query returns how frequently a cached query has been used. You can use this to see which statements are used most on your server.
SELECT usecounts,
cacheobjtype,
objtype,
text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC;
I hope this gives you enough information to get started using these DMOs. You can find plenty of additional examples on the internet or in SQL Server Books Online.
Tip provided by Greg Robidoux
