Find the slowest queries in SQL Server database

Identify the slowest queries in the database

SQL Server Management Studio to identify the ten slowest queries with sys.dm_exec_query_stats, reviewing total and average execution times alongside query text. Enable STATISTICS TIME and IO for performance metrics, then select Include Actual Execution Plan from the Query menu and rerun the query to analyze its graphical execution plan.

In SSMS, open a new query window and run the following query to identify the slowest queries by using this dynamic query:

SELECT TOP 10
qs.total_elapsed_time / 1000000.0 AS TotalElapsedTimeSeconds,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AvgElapsedTimeSeconds,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text NOT LIKE '%sys.dm_exec_query_stats%'
ORDER BY qs.total_elapsed_time DESC;

If query find then run the query by setting the following:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
To view the graphical execution plan
1. click the Query menu
2. choose Include Actual Execution Plan,
3. then re-run the query

Leave a Reply