Optimize a slow SELECT in SQL Server: checklist

A slow SELECT is rarely a mystery once you stop guessing and start measuring. SQL Server already knows where the time went — your job is to ask it the right questions, in the right order, and change one thing at a time. This is a practical checklist for doing exactly that in SQL Server and SSMS, with short notes where other engines differ. Work it top to bottom; most slow queries give up after the first few steps.

Where do you start when a SELECT is slow?

Not by rewriting the query. Start by measuring, so you have a baseline to beat and a number that tells you whether a change actually helped.

Two session settings give you that number directly in SSMS:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- run your SELECT here

SET STATISTICS IO “display[s] information about the amount of physical and logical IO activity generated by Transact-SQL statements”; logical reads is the “number of pages read from the data cache” and physical reads the “number of pages read from disk” (SET STATISTICS IO). SET STATISTICS TIME “[d]isplays the number of milliseconds required to parse, compile, and execute each statement” (SET STATISTICS TIME).

Anchor on logical reads: they do not swing with how warm your cache is or how busy the box is, so when that number falls after a change, the query genuinely does less work — not just less work today. Pair that with the execution plan, which shows why the reads are high. Reading a plan is its own skill, so if scan-versus-seek and estimated-versus-actual rows are not yet second nature, start with how to read a SQL execution plan and come back.

Why won’t SQL Server use my index?

The most common answer: your predicate is not sargable. Microsoft’s own term — “a Search ARGumentable predicate that can use an index to speed up the execution of the query” (Index design guide).

The rule is simple: keep the indexed column bare on its side of the comparison. The moment you wrap it in a function or an expression, the optimizer can no longer use the index as a lookup and falls back to scanning the whole thing.

-- Non-sargable: the function hides the column from the index
WHERE YEAR(order_date) = 2026

-- Sargable: a range the index can seek
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

The same trap is WHERE UPPER(email) = @e, WHERE price * 1.23 > 100, or an implicit type conversion that makes SQL Server convert the column rather than the literal. Rewrite the predicate so the column stands alone; if you genuinely need the function, that is what a computed column plus an index on it is for. The mechanic is cross-engine — in PostgreSQL the symptom is a Seq Scan where you expected an Index Scan, fixed the same way or with a functional index.

Should I avoid SELECT *?

Yes, but for a sharper reason than “it’s untidy.” The columns you ask for decide whether an index can satisfy the query by itself. “When an index contains all the columns referenced by a query it’s typically referred to as covering the query,” and the payoff is concrete: “the query optimizer can locate all the column values within the index; table or clustered index data isn’t accessed resulting in fewer disk I/O operations” (Create indexes with included columns).

SELECT * drags in columns the index does not have, forcing SQL Server back to the table for every row (a key lookup) — often the biggest source of avoidable reads in a well-indexed query. Ask for the columns you actually use, then build an index that covers them: search and join columns in the key, the rest in INCLUDE:

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (customer_id, order_date)
INCLUDE (status, total_amount);

The key columns (customer_id, order_date) make the predicate seekable; the included columns ride along so the query never touches the base table. Keep it honest, though — Microsoft warns to “avoid creating a covering index with too many columns because that diminishes its benefit while inflating database storage, I/O, and memory footprint.”

Should I just create the index SQL Server suggests?

SSMS will often show a green Missing Index hint in the plan, and the same suggestions live in the DMVs sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats (missing index DMV reference). Treat them as a lead, never a command. Microsoft is blunt about the limits:

The suggestions are “based on estimates made during the optimization of a single query, prior to query execution” — not validated afterward.
“Key columns are suggested, but the suggestion doesn’t specify an order for those columns” — and column order is what makes an index seekable.
The feature “suggests only nonclustered disk-based rowstore indexes,” skips trivial plans, and is “less accurate for queries involving only inequality predicates.”

Their conclusion is the one to internalize: “missing index suggestions are best treated as one of several sources of information… [they] aren’t prescriptions to create indexes exactly as suggested” (Tune nonclustered indexes with missing index suggestions). Before running a suggested CREATE INDEX, check whether an existing index already covers it with a small tweak, order the key columns by selectivity (most selective first), and remember every index slows every INSERT, UPDATE and DELETE.

Why is the same query fast one day and slow the next?

Two usual suspects, both about the numbers the optimizer plans against.

Stale statistics. “The Query Optimizer uses statistics to create query plans that improve query performance” — specifically to “estimate the cardinality, or number of rows, in the query result,” which is how it chooses, say, “the index seek operator instead of the more resource-intensive index scan operator” (Statistics). When statistics lag far behind the data, the estimate is wrong and every downstream choice inherits the error. A large gap between estimated and actual rows in the plan is the tell; UPDATE STATISTICS (or letting AUTO_UPDATE_STATISTICS do its job) is the fix.

Parameter sniffing. “Parameter sensitivity, also known as ‘parameter sniffing’, refers to a process whereby SQL Server ‘sniffs’ the current parameter values during compilation… so that they can be used to generate potentially more efficient query execution plans” (Query processing architecture guide). Usually helpful — but the cached plan is shaped by whichever values compiled it. A plan built for a parameter that matches 10 rows can be a poor fit when the next call matches 10 million, and vice versa. If a stored procedure is fast for some inputs and slow for others with no data change, this is where to look.

What about joins and the N+1 problem?

One well-formed query beats a loop of small ones. The “N+1” pattern — fetch a list, then fire a separate SELECT per row — turns one round trip into hundreds. Replace the loop with a single join and let the optimizer pick the order and method; it plans the whole statement at once, which it cannot do across N separate calls. Index the join columns on both sides, and confirm the plan is not fanning rows out before a late filter discards most of them — if the estimates look wrong here, you are back at the statistics step.

The checklist

For any slow SELECT, in order:

Measure first — SET STATISTICS IO/TIME ON, capture logical reads and the actual plan as a baseline.
Make predicates sargable — keep indexed columns bare; turn YEAR(col) = … into a range.
Stop selecting columns you don’t use — then cover the survivors with a key + INCLUDE index.
Treat missing-index hints as a hypothesis — order key columns by selectivity, fold into existing indexes, weigh write cost.
Check the estimates — stale statistics and parameter sniffing explain most “sometimes slow” queries.
Collapse N+1 into one join — and index both sides of it.

Change one thing, re-run, compare logical reads. If the number did not move, revert it and keep going — tuning is proving, not guessing. (Before you reach for schema changes like a new index, it is also worth knowing what else depends on the objects you are about to touch.)

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned here.

SQL Sage for SSMS is an add-in for SQL Server Management Studio 22 on Windows 10/11 that writes, explains, and optimizes SQL inside the editor you already use — the same editor where you run SET STATISTICS IO and read the plan. A few facts stated plainly: it is keyless (you sign in with your own Claude or ChatGPT/Codex account, no API key — or bring your own Anthropic key), it is read-only by default (SELECT runs; any UPDATE/DELETE/DDL waits for your explicit confirmation), and it sends only your schema and SQL text to the model — never your data rows, unless you opt in per session. There is a 30-day trial with no card, then a free tier; paid Personal plans start at $29/year or $39 lifetime, sold through Paddle. It is not affiliated with Microsoft, Anthropic, or OpenAI. But the checklist above is the real takeaway — it works whatever tool you tune with.