SELECT TOP (50) sp.database_id,
dbname= DB_NAME (qt.dbid),
so.name AS StoredProcName,
sp.total_worker_time,
sp.execution_count AS StoredProcedureExecCount,
qs.execution_count AS StatementExecCount,
SUBSTRING(qt.text,qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2
) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_procedure_stats AS sp
ON (sp.sql_handle = qs.sql_handle)
INNER JOIN sys.objects so
ON (sp.object_id = so.object_id) and sp.database_id = DB_ID()
ORDER BY sp.total_worker_time DESC, qs.execution_count DESC
Comments