Hello readers,
Here’s a T-SQL script to identify the 10 heaviest instructions executed in a SQL Server.
I must say, this was a script found by a college of mine and passed along, so I can’t give the credit to it’s original programmer, but whoever you are. Thank you very much.
SELECT TOP 10 — You can change number of lines
qs.last_execution_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,
total_worker_time/execution_count AS [Avg CPU Time],
execution_count,
case
when execution_count = 0 then null
else total_logical_reads/execution_count
end as avg_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
case
when execution_count = 0 then null
else total_logical_writes/execution_count
end as avg_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
max_elapsed_time
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE qs.last_execution_time >= dateadd(day, –1, getdate()) — You can change the datepart and number
–ORDER BY max_logical_reads DESC — You can change to max_logical_writes
–ORDER BY max_logical_writes DESC
order by [Avg CPU Time] DESC
You can change the number of instructions and the order of presentation.
So just use it. It’s very handy.
