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 QueryTextFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE 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