I mentioned in another post how impressed I am with the new DMV's. The sys.dm_exec_query_stats view is very helpful for determining what queries are running long, or consuming a bit heavily on the resources. Per BOL, this view returns aggregate performance statistics for cached query plans. I've posted some examples below. Let me know if you have any questions, or any examples of your own to share.
This one will return the TOP 10 most CPU heavy statements being run:
SELECT TOP 10
SUBSTRING(b.text, (a.statement_start_offset/2) + 1,((CASE
statement_end_offset WHEN -1 THEN DATALENGTH(b.text)
ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS statement_text,
c.query_plan,
total_worker_time [cpu_time]
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) b
CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) c
ORDER BY
total_worker_time DESC
This one will show you the TOP 5 procedures, based on which ones have the greatest execution count:
SELECT TOP 5
b.text AS 'Procedure Name',
a.execution_count AS 'Execution Count',
a.execution_count/DATEDIFF(SECOND, a.creation_time, GETDATE()) AS 'Calls/Second',
a.total_worker_time/a.execution_count AS 'Avg CPU Time',
a.total_worker_time AS 'Total CPU Time',
a.total_elapsed_time/a.execution_count AS 'Avg Elapsed Time',
a.max_logical_reads [MAX Logical Reads],
a.max_logical_writes [MAX Logical Writes],
a.total_physical_reads [Total Physical Reads],
DATEDIFF(MINUTE, a.creation_time, GETDATE()) AS 'Cached How Long'
FROM
sys.dm_exec_query_stats a
sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE
b.dbid = DB_ID() -- <<<ONLY FOR THE CURRENT DATABASE
ORDER BY
a.execution_count DESC
There are many other statistics you could retrieve, such as the top I/O bound statements, or even which ones are recompiled more than the others. This is one that I use to see which queries or procedures are performing slowly:
SELECT
last_execution_time,
execution_count,
total_physical_reads,
total_logical_reads,
total_logical_writes,
total_worker_time,
total_elapsed_time,
total_elapsed_time / execution_count avg_elapsed_time,
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
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) st
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY
total_elapsed_time / execution_count DESC;
NOTES:
Important to remember, it can only retrieve what is cached.
No comments:
Post a Comment