Skip to content

Instantly share code, notes, and snippets.

@marcelobrake

marcelobrake/check_top50_execution_time_on_mssql.sql

Created January 24, 2024 13:08
Show Gist options
  • Save marcelobrake/63eaf016902fcec5f48aae21e509e571 to your computer and use it in GitHub Desktop.
Save marcelobrake/63eaf016902fcec5f48aae21e509e571 to your computer and use it in GitHub Desktop.
Download ZIP
[MSSQL] Top 50 - Tempo de execução
Raw
check_top50_execution_time_on_mssql.sql
SELECT TOP 50
st.text,
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,
qp.query_plan,
qs.execution_count,
qs.last_execution_time,
qs.last_worker_time/1000000.0 AS last_worker_time_s, --this is CPU time
qs.last_elapsed_time/1000000.0 AS last_elapsed_time_s --this is clock time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time > DATEADD(minute,-5,GETDATE()) --last 5 minutes
ORDER BY qs.last_elapsed_time DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment