Find Top 50 SQL statements and SP with highest workertime and execution count.

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