Why Is My SQL Server Slow? A 4-Step Diagnosis with Blocking, Wait Stats, Execution Plans and Missing Indexes

Your SQL Server is slow — but "slow" is a symptom, not a diagnosis. In this tutorial you will diagnose one genuinely slow query the way a mechanic finds a car fault: with four checks, in a fixed order, each producing evidence instead of a guess. The four checks are: (1) is anything blocking the query, (2) what is the server waiting on, (3) what does the execution plan say, and (4) which index is missing. Our test query starts out touching 12,388 pages to return about 2,061 rows. By the end of this post it touches 15 pages — and you will know exactly which check found the problem, because every number here was measured on the demo server while writing this post.

What you need

• A SQL Server you can practise on — this post uses SQL Server 2025 (17.x), measured build 17.0.5065.9 (RTM-CU11) on Linux, driven from a terminal with sqlcmd. Every query below also runs unchanged in a query window in SQL Server Management Studio (SSMS) 22 on Windows — a section near the end maps each step to SSMS.
• A throwaway database you may break. We create one below called DemoDb.
• Permission to see the server's diagnostic views: the wait and blocking views used here need the VIEW SERVER PERFORMANCE STATE permission (on your own demo instance, your admin login already has it).

No downloads beyond the database engine itself. Everything in this diagnosis is built into SQL Server.

The whole method, on one screen

Keep this map in mind. Each check below answers exactly one question, and each ends with evidence on screen: a row from a diagnostic view, an operator in a plan, or a count of pages read. Nothing is fixed by guessing.

Step 0 — Build the slow scene

We need a table big enough to be slow and a query simple enough to reason about. Create the database and an Orders table with 200,000 rows — orders from eight Canadian cities — with no index on the City column. That missing index is the planted fault; everything else about the setup is ordinary.

CREATE DATABASE DemoDb; GO USE DemoDb; GO CREATE TABLE dbo.Orders ( OrderId INT IDENTITY(1,1) CONSTRAINT PK_Orders PRIMARY KEY, CustomerId INT NOT NULL, City NVARCHAR(40) NOT NULL, OrderDate DATE NOT NULL, Total DECIMAL(10,2) NOT NULL, Notes NVARCHAR(300) NOT NULL ); GO 

Fill it with generated rows (the full setup script, including the row generator, is in the companion code at the end of this post). Toronto is deliberately rare — about one percent of the table — because a city report is exactly the kind of query a real application runs.

The demo instance reporting its version, and the finished table: 200,000 orders, 2,061 of them in Toronto.

Step 1 — Run the hero query and take its fingerprints

Our hero query is the city report: show me the orders from Toronto. Before diagnosing anything, measure it. Two counters matter:

• Elapsed time — how long the query took, in milliseconds.
• Logical reads — how many 8 KB pages of the table SQL Server touched while answering. Pages are the honest yardstick: they do not depend on how fast your disk happens to be.

SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT OrderId, CustomerId, OrderDate, Total FROM dbo.Orders WHERE City = N'Toronto'; 

First run: elapsed **15 ms, and **12,388 logical reads* — the server touched 12,388 pages to fetch about 2,061 rows. That ratio is the smell. Now we find out why, one check at a time.*

Check 1 — Is anything blocking it?

The idea in plain words. When a query changes a row, SQL Server locks that row until the change is finished — like one person editing a shared document while everyone else waits. A transaction is a group of changes treated as one unit: either all of them happen, or none do. If a transaction is left open, its locks stay held, and every other query that needs those rows queues behind it. On a busy server this — not a missing index — is the most common reason a healthy query suddenly "hangs".

Prove it to yourself with two sessions. In session A, open a transaction, update one order, and deliberately leave the transaction open:

BEGIN TRANSACTION; UPDATE dbo.Orders SET Total = Total + 1 WHERE OrderId = 42; -- do NOT commit yet 

In session B, try to update the same order. It will not error and it will not finish — it waits. Now ask the engine who is waiting on whom. sys.dm_exec_requests is the live list of work running right now, and its blocking_session_id column names the session doing the blocking:

SELECT session_id, blocking_session_id, wait_type, wait_time, status FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; 

Session A holding its transaction open; session B's update waiting behind it.

The evidence: one row — session B, blocked by session A, waiting on a lock (an LCK wait). Roll back session A's transaction and B completes instantly.

In our hero query's case this check comes back clean — nobody is blocking it. Run this check first anyway, every time: no index in the world fixes a queue.

Check 2 — What is the server waiting on?

The idea in plain words. SQL Server keeps a diary of waiting. Every time work pauses — for the disk, for a lock, for the processor — it writes down why and for how long. That diary is the view sys.dm_os_wait_stats, accumulated since the server started. Ask it for the largest waits, skipping the idle background waits every healthy server records (the query in the companion code carries the standard filter list):

SELECT TOP (8) wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 /* ...plus the standard idle-wait filter, in the companion code... */ ORDER BY wait_time_ms DESC; 

The top of our diary after the workload above: **LCK_M_X — 36,044 ms of lock waiting* across 3 waits. Further down: PAGEIOLATCH_SH — 337 ms across 285 waits for fetching data pages from disk into memory.*

Read the diary honestly. The loudest entry is lock waiting — the fingerprint of Check 1: the blocking we staged on purpose is the biggest single use of waiting time on this young demo server (36 seconds of it, while the blocking transaction sat open). That is the diary doing its job: it confirms the symptom and timestamps it. The page-fetch wait, PAGEIOLATCH_SH, is present but small — 337 ms — because this demo's disk is fast and the table fits in memory after the first read. On a busy production server with a cold cache, a full-table scan like our hero query's pushes exactly that wait to the top of the diary; here, the 12,388 logical reads from Step 1 are the harder evidence. Two cautions that make you better at reading any diary: it accumulates since the server started (know what workload it covers — ours includes our own staged blocking), and waits tell you where time went, not why. The why is the next check.

Check 3 — What does the execution plan say?

The idea in plain words. Before running a query, SQL Server writes an execution plan — the route it will take through the data. The actual execution plan shows the route it really took, operator by operator. Two operators decide today's story:

• Scan — read every row in the table, one by one. With 200,000 rows, that is the whole book, page by page.
• Seek — use an index (a sorted lookup list, like the index at the back of a book) and jump straight to the matching rows.

Ask for the actual plan of the hero query (in SSMS this is the Include Actual Execution Plan button, or Ctrl+M; in a terminal, SET STATISTICS XML ON returns the same plan):

The plan's heavy operator: **Clustered Index Scan. The server read the entire table because, with no index on City, a scan was the only route it had.

Check 4 — Which index is missing?

The idea in plain words. Every time the planner wanted an index that did not exist, it wrote a note: the table, the columns it wanted, and how much it estimated the index would help. Those notes live in three views whose names start with sys.dm_db_missing_index. Ask for the notes about our database:

SELECT migs.avg_total_user_cost, migs.avg_user_impact, migs.user_seeks, mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_group_stats AS migs JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID('DemoDb') ORDER BY migs.avg_user_impact DESC; 

SQL Server's own note: for dbo.Orders, an index whose key is [City], with included columns [CustomerId], [OrderDate], [Total] — estimated improvement **98.19%.

The honesty rule (Microsoft's own documentation says it plainly): a missing-index suggestion is a hint, not a prescription. The estimates are made when the query is planned, before it runs, and the suggestions are forgotten when the server restarts. So read the note critically. Ours passes the common-sense test — the suggested key is exactly our query's filter column — so we build it.

Step 5 — Create the index the server asked for

CREATE NONCLUSTERED INDEX IX_Orders_City ON dbo.Orders (City) INCLUDE (CustomerId, OrderDate, Total); 

The index builds in 524 ms. One line of SQL — chosen from evidence, not from a guess.

Step 6 — Run the same query again (the proof)

SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT OrderId, CustomerId, OrderDate, Total FROM dbo.Orders WHERE City = N'Toronto'; 

After: the plan's heavy operator is now **Index Seek (on the new IX_Orders_City index)* — the server opens the book at the right page.*

Same data. Same query, word for word. The only change is the index — added because four checks, in order, pointed at it.

Doing this in SSMS

• Plans: press Ctrl+M (Include Actual Execution Plan), run the query, and read the Execution Plan tab — the operators you met above appear by name. A plan can also carry a green "Missing Index" suggestion line; right-clicking it offers the same CREATE INDEX the DMVs described.
• Blocking: Activity Monitor (right-click the server → Activity Monitor) shows blocked sessions in its Processes grid; the DMV query in Check 1 is the same information, scriptable.
• Reads: SET STATISTICS IO ON prints its lines on the Messages tab, exactly as captured here.

Common mistakes

• Adding indexes by vibes. Every index speeds some reads and slows every write to the table. Evidence first (Checks 1–4), then one index at a time, then re-measure.
• Creating every suggested index. Suggestions overlap and pile up; Microsoft calls them hints for a reason. Consolidate, and check what your queries actually filter on.
• Blaming the index for a queue. If Check 1 shows a blocker, the fix is the blocking transaction — not an index.
• Tuning from a cold diary. Wait stats and missing-index notes reset when the server restarts. Diagnose on a server that has been running the real workload.

The method, on one card

Blocked? Ask sys.dm_exec_requests. Waiting? Ask sys.dm_os_wait_stats. Why? Read the actual execution plan. Missing? Ask the sys.dm_db_missing_index views. Then fix one thing and measure again. Four questions, in order, evidence at every step.

Your exercise: on a test database, run a query you believe is slow. Take its fingerprints (Step 1), run the four checks, write down what each one says before you change anything — then make the one fix the evidence supports and compare the numbers.

Sources

• Microsoft Learn — sys.dm_exec_requests (Transact-SQL): the blocking_session_id column and the blocked-requests example.
• Microsoft Learn — sys.dm_os_wait_stats (Transact-SQL): "You can use this aggregated view to diagnose performance issues with SQL Server"; lock waits indicate contention, page IO latch waits indicate IO response.
• Microsoft Learn — sys.dm_db_missing_index_details and Tune nonclustered indexes with missing index suggestions: column roles, equality-before-inequality key order, and "suggestions aren't prescriptions".
• All timings, read counts and plan operators in this post were measured on the demo instance described in Step 0 while writing it.

Get the code

The complete setup, diagnosis and fix scripts for this post live in the companion repository, in the folder for this topic, so you can rebuild the slow scene and run every check yourself.