Jan
24
Written by:
Steve Gray
1/24/2011 9:09 AM
Here’s a quick starter on Dynamic Management Views that is available in SQL 2008. I got this from an article by Greg Larsen over at Red-Gate. If you are reading this and don’t subscribe to their Simple-Talk newsletter… you should. The full article is here
Anyway, I didn’t want to risk loosing these two queries, they’re too good.
This one looks at the data available in sys.dm_exec_procedure_stats
Code Snippet
- SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
- ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
- ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
- ,*
- FROM sys.dm_exec_procedure_stats
This one compares execution times
Code Snippet
- SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
- ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
- ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
- ,cached_time
- ,last_execution_time
- ,execution_count
- ,total_worker_time / execution_count AS AVG_CPU
- ,total_elapsed_time / execution_count AS AVG_ELAPSED
- ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
- ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
- ,total_physical_reads / execution_count AS AVG_PHYSICAL_READS
- FROM sys.dm_exec_procedure_stats
- ORDER BY AVG_LOGICAL_READS DESC
This one is similar to the last but uses sys.dm_exec_query_stats and sys.dm_exec_cached_plans; these are available in SQL 2005
Code Snippet
- SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(dbid)END AS DBName
- ,OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME]
- ,OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME]
- ,MAX(qs.creation_time) AS 'cache_time'
- ,max(last_execution_time) AS 'last_execution_time'
- ,sum(usecounts) AS [execution_count]
- ,SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU
- ,SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED
- ,SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS
- ,SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES
- ,SUM(total_physical_reads) / SUM(usecounts)AS AVG_PHYSICAL_READS
- FROM sys.dm_exec_query_stats qs
- join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
- CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
- WHERE objtype = 'Proc'
- AND text
- NOT LIKE '%CREATE FUNC%'
- GROUP BY cp.plan_handle,DBID,objectid
As always, I welcome your comments!