Introduction
Slow-running queries can severely impact database and application performance. To diagnose them, start by monitoring currently executing statements and then analyze historical execution data. SQL Server provides Dynamic Management Views (DMVs) and other tools to find long queries, as well as indexing and tuning techniques to optimize them. The following sections walk through DMV scripts, monitoring tools, and optimization best practices in detail.
Identifying Currently Running Long Queries
SQL Server’s DMVs reveal active sessions and requests. For example, sys.dm_exec_requests lists executing queries and their resource use. Joining it with sys.dm_exec_sql_text lets you retrieve the SQL text. The following query shows running requests ordered by total elapsed time (longest first):
SELECT
r.session_id,
r.status,
r.total_elapsed_time AS duration_ms,
r.cpu_time AS cpu_ms,
r.logical_reads,
SUBSTRING(
REPLACE(REPLACE(SUBSTRING(st.text,
(r.statement_start_offset/2)+1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1
), CHAR(10), ' '), CHAR(13), ' '),
1, 1000) AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.status = 'running'
ORDER BY r.total_elapsed_time DESC;`
This returns one row per active request. Key columns include session_id (the SPID), total_elapsed_time (ms since query start), cpu_time, logical_reads, and the query_text. For example, if a row shows total_elapsed_time = 120000, that request has been running 120 seconds. By inspecting the query_text you can identify which SQL is long-running. You may also join to sys.dm_exec_sessions for login or application details.
Reviewing Historical Query Performance
DMVs also store aggregate stats for past executions. sys.dm_exec_query_stats contains cumulative data for cached query plans. To find slow queries from the plan cache, average the total time by execution count. For example:
SELECT TOP 10
txt.text AS query_text,
(qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time_ms,
(qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time_ms,
qs.execution_count
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY avg_elapsed_time_ms DESC;
This returns the highest-average-duration queries. Columns include the full SQL text and the average elapsed and CPU times per execution. For example, a row might show avg_elapsed_time_ms = 5000, indicating that query takes ~5 seconds on average.
In newer SQL Server versions, the Query Store captures historical runtime data persistently. Once enabled (using ALTER DATABASE … SET QUERY_STORE = ON), Query Store automatically records queries, plans, and statistics in time windows. You can then use built-in reports (e.g., “Top Resource Consuming Queries”) or query the catalog views (sys.query_store_query, sys.query_store_runtime_stats, etc.) to find high-duration queries. Query Store is especially useful for trend analysis and plan change history.
Monitoring Tools (Execution Plans, Query Store, sp_WhoIsActive)
Beyond DMVs, several tools aid real-time monitoring:
- Execution Plans: Collect the actual execution plan for the query (in SSMS use “Include Actual Execution Plan” or Ctrl+M). The plan diagram will highlight expensive operators (table/index scans, sorts, key lookups, etc.). Look for Clustered Index Scan or Table Scan operators, which often suggest missing indexes or unselective predicates. Examine tooltip statistics (actual rows vs estimates) to spot cardinality estimation issues. Missing index hints may appear in the plan, or you can run the missing index DMVs query to identify recommended indexes.
- Query Store: As noted, once enabled Query Store tracks queries over time. It provides GUI reports in SSMS (e.g., “Regressed Queries”) and exposes views for custom analysis. By querying sys.query_store_query_text and related views, you can search for specific query patterns and see their aggregated durations. This helps identify queries that unexpectedly became slow after code or schema changes.
**
- sp_WhoIsActive: **This popular free stored procedure (by Adam Machanic) offers a richer real-time activity snapshot than the built-in sp_who/sp_who2. It shows each active session’s SQL text, CPU, reads, waits, blocking info, and more. For example, executing sp_WhoIsActive (with default options) yields rows with columns like session_id, sql_text, login_name, wait_info, CPU, reads, etc. sp_WhoIsActive accepts parameters for even more detail such as including execution plans or locks.
Query Optimization Best Practices
Once long-running queries are identified, apply systematic tuning. The following steps summarize key best practices:
- Analyze Query Plans Examine the actual execution plan for each slow query. Look for scans (table or index scans) on large tables, key lookups (which suggest missing covering indexes), expensive sorts or spools. Check for estimated-vs-actual row count discrepancies, which often indicate outdated statistics or non-sargable filters. Use SET STATISTICS IO, TIME ON when running the query in isolation to get I/O and time metrics in the messages pane.
- Add or Tune Indexes Ensure appropriate indexes exist. The plan or DMV may suggest missing indexes. SQL Server’s missing-index DMVs log helpful info when the optimizer thinks an index would improve performance. The following query lists suggested indexes with a create index statement and an improvement measure:
SELECT TOP 5
CONVERT(varchar(30), GETDATE(), 126) AS runtime,
mig.index_group_handle,
mid.index_handle,
CONVERT(decimal(28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX IX_'
+ OBJECT_SCHEMA_NAME(mid.[object_id], mid.database_id)
+ '_' + OBJECT_NAME(mid.[object_id], mid.database_id)
+ '_' + CONVERT(varchar, mig.index_group_handle)
+ ' ON ' + mid.statement
+ '(' + ISNULL(mid.equality_columns,'')
+ CASE
WHEN mid.inequality_columns IS NOT NULL AND mid.equality_columns IS NOT NULL THEN ','
ELSE '' END
+ ISNULL(mid.inequality_columns,'') + ')'
+ ISNULL(' INCLUDE (' + mid.included_columns + ')','')
AS create_index_statement
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;
- Maintain and Rebuild Indexes Fragmented indexes can slow reads and writes. Periodically check fragmentation with sys.dm_db_index_physical_stats or in SSMS index properties. As a rule of thumb, fragmentation 5–30% can be fixed with ALTER INDEX … REORGANIZE (an online, lightweight defragmentation), whereas >30% may require ALTER INDEX … REBUILD.
- Update Statistics The optimizer relies on up-to-date statistics. By default AUTO_UPDATE_STATISTICS is ON, but very large tables or bulk changes can make stats stale. If you suspect bad row estimates, run UPDATE STATISTICS dbo.MyTable WITH FULLSCAN. Or use sp_updatestats to refresh all user tables.
- Rewrite Inefficient SQL Simplify the query logic where possible. Avoid cursor-based or row-by-row processing; use set-based operations instead. Replace SELECT * with only needed columns. Make predicates SARGable (e.g., avoid wrapping columns in functions). Ensure proper JOINs and eliminate unnecessary subqueries or temp tables.
- Monitor and Iteratively Tune After each change, test the query again (with SET STATISTICS IO, TIME ON or an execution plan) to confirm improvement. Sometimes adding an index improves reads but slows writes or vice versa. Use metrics like elapsed time, CPU time, and logical reads to gauge impact.
Top comments (1)
This is really helpful. Thank you