How to Identify Slow Queries in SQL Server

Galaxy Glossary

How do I quickly find and fix slow SQL Server queries?

Spot, measure, and troubleshoot long-running SQL Server statements with DMVs, Query Store, and Extended Events.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why does SQL Server run slow queries?

Slow queries often stem from missing indexes, outdated statistics, parameter sniffing, or inefficient joins. Pinpointing the real cause requires inspecting execution metrics and plans rather than guessing.

How can I list the worst-performing queries?

Use dynamic management views (DMVs) such as sys.dm_exec_query_stats to surface queries with the highest average or total duration. Joining dm_exec_sql_text returns the SQL statement for quick review.

Example DMV script

SELECT TOP 20 qs.total_elapsed_time/qs.execution_count AS avg_ms, qs.execution_count, qs.total_logical_reads/qs.execution_count AS avg_reads, DB_NAME(st.dbid) AS db_name, st.text, qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY avg_ms DESC;

How do I capture slow queries in real time?

Create an Extended Events session filtering duration > 2 seconds. This lightweight trace stores captured events in a ring buffer for immediate inspection with sys.fn_xe_file_target_read_file.

When should I enable Query Store?

Turn on Query Store in production databases running SQL Server 2016+. It logs runtime stats per plan, allowing regressions to be detected and fixed by forcing stable plans.

Which indexes fix most slow queries?

Start with composite indexes covering join and filter columns. Verify effectiveness with actual execution plans; a reduced logical read count signals success.

Best practices for preventing slow queries

Keep statistics current, write sargable predicates, avoid SELECT *, and monitor query performance regularly through automated jobs that alert when duration thresholds are exceeded.

Why How to Identify Slow Queries in SQL Server is important

How to Identify Slow Queries in SQL Server Example Usage


-- Slow query on ecommerce database
SELECT c.name,
       COUNT(o.id)        AS order_count,
       SUM(o.total_amount) AS lifetime_value
FROM   Customers AS c
JOIN   Orders    AS o ON o.customer_id = c.id
WHERE  c.created_at &lt; DATEADD(year, -1, GETDATE())
GROUP  BY c.name
HAVING SUM(o.total_amount) &gt; 10000
ORDER  BY lifetime_value DESC;

-- Diagnose why it is slow
SET STATISTICS IO, TIME ON;
GO
-- View actual plan; look for missing index recommendations

How to Identify Slow Queries in SQL Server Syntax


-- DMV to find top average duration queries
SELECT TOP 20
       qs.total_elapsed_time / qs.execution_count AS avg_duration_ms,
       qs.execution_count,
       DB_NAME(st.dbid)               AS database_name,
       st.text                        AS sql_text,
       qs.total_logical_reads        AS total_reads,
       qs.total_worker_time          AS total_cpu_ms
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_duration_ms DESC;

-- Query Store to list recent long-running statements
SELECT TOP 20
       q.query_sql_text,
       rs.avg_duration,
       rs.last_execution_time
FROM sys.query_store_query_text q
JOIN sys.query_store_query     qq ON q.query_text_id = qq.query_text_id
JOIN sys.query_store_plan      qp ON qq.query_id     = qp.query_id
JOIN sys.query_store_runtime_stats rs ON qp.plan_id  = rs.plan_id
WHERE rs.avg_duration > 2000     -- &gt;2 s
ORDER BY rs.avg_duration DESC;

-- Extended Events session targeting slow Orders query
CREATE EVENT SESSION slow_orders ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sql_text, plan_handle )
    WHERE ( sql_text LIKE '%FROM Orders%') AND duration &gt;= 2000000 ) -- microseconds
ADD TARGET package0.asynchronous_file_target (SET filename = 'C:\XE\slow_orders.xel');
ALTER EVENT SESSION slow_orders ON SERVER STATE = START;

Common Mistakes

Monitoring only total duration and ignoring execution_count. A query that runs 1× for 10 s matters less than one that runs 10,000× for 20 ms. Sort by avg_duration and total_elapsed_time.
Rebuilding every index after spotting a slow query. Over-maintenance wastes IO and may degrade performance. Instead, create targeted indexes identified by execution plans.