SpDev Install free

SQL query optimization in SQL Server: a practical checklist

Ten checks that fix most slow SQL Server queries, from sargable predicates to catch-all searches, each with logical reads measured before and after.

By Areeb Ahmer, . 5 minute read.

Most slow queries are slow for one of a small number of reasons, and the reasons repeat. This checklist goes through them in the order worth checking, with the number that matters for each: logical reads, the pages SQL Server touched. Unlike duration, reads don’t change with the server’s mood, so they tell you honestly whether a change helped.

Turn the measurement on in your session first:

SET STATISTICS IO ON;

Every example runs against the 200,000-row dbo.Orders table of the SpDevDemo database.

1. Find the statement that’s actually slow

Optimizing the wrong query is the most common waste of time. Before changing anything, find the statement responsible: from Query Store, sys.dm_exec_query_stats, or by profiling the procedure. A procedure of 40 statements usually spends most of its time in one or two. See performance tuning for how to find them.

2. Keep functions off indexed columns

An index on OrderDate is sorted by OrderDate, not by YEAR(OrderDate). Wrap the column in a function and SQL Server must compute it for every row before it can compare:

CREATE INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate);

SELECT COUNT(*) FROM dbo.Orders WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 3;
SELECT COUNT(*) FROM dbo.Orders WHERE OrderDate >= '2024-03-01' AND OrderDate < '2024-04-01';
Table 'Orders'. Scan count 1, logical reads 328    -- function on the column: scan
Table 'Orders'. Scan count 1, logical reads 10     -- range on the column: seek

A predicate that can use an index seek is called sargable (Search ARGument ABLE). The same applies to strings:

SELECT COUNT(*) FROM dbo.Orders WHERE LEFT(OrderNumber, 6) = 'SO-012';
SELECT COUNT(*) FROM dbo.Orders WHERE OrderNumber LIKE 'SO-012%';
Table 'Orders'. Scan count 1, logical reads 604
Table 'Orders'. Scan count 1, logical reads 35

Common rewrites:

Instead of Write
YEAR(d) = 2024 d >= '2024-01-01' AND d < '2025-01-01'
CONVERT(date, dt) = '2024-03-05' dt >= '2024-03-05' AND dt < '2024-03-06'
LEFT(s, 3) = 'abc' s LIKE 'abc%'
ISNULL(s, '') = 'x' s = 'x'
col + 1 = @x col = @x - 1
DATEDIFF(DAY, d, GETDATE()) < 30 d > DATEADD(DAY, -30, GETDATE())

LIKE '%abc', with a leading wildcard, can’t seek either; no rewrite fixes that except a different design, such as full-text search.

3. Match data types

A VARCHAR column compared with an NVARCHAR value is converted row by row, and the index can’t be used for a seek. With the default collation, an order lookup went from 6 reads to 607. Details in implicit conversions.

4. Give the query an index it can use

Look at the WHERE and JOIN columns of the slow statement. Columns compared with = go first in the index key, then one range column, then the returned columns in INCLUDE. A covering index took a query for 20,000 orders from 1,340 reads to 68. See indexes.

5. Return only what you need

SELECT * returns every column, so no narrow index can cover the query, and every new column added to the table makes the query heavier. List the columns. The same goes for rows: filter in the query, not in the application. A query that returns 50,000 rows to show 20 on screen is slow however good its plan.

6. Watch out for catch-all searches

A search procedure with optional parameters is often written like this:

CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);
GO
CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @Status     VARCHAR(20) = NULL,
    @CustomerId INT = NULL
AS
SELECT COUNT(*) FROM dbo.Orders
WHERE (@Status IS NULL OR Status = @Status)
  AND (@CustomerId IS NULL OR CustomerId = @CustomerId);
GO

EXEC dbo.SearchOrders @CustomerId = 42;
EXEC dbo.SearchOrders @Status = 'Cancelled';
Table 'Orders'. Scan count 1, logical reads 381       -- customer 42
Table 'Orders'. Scan count 1, logical reads 600354    -- 'Cancelled', reusing the same plan

The cached plan has to work for every combination of NULL and non-NULL parameters, so it can’t seek on either index, and when it’s reused for a parameter it wasn’t built for, it can be catastrophic: 600,354 reads to count 2,000 rows. Add OPTION (RECOMPILE) to the statement and SQL Server compiles it for the actual values each time, removing the branches that don’t apply:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @Status     VARCHAR(20) = NULL,
    @CustomerId INT = NULL
AS
SELECT COUNT(*) FROM dbo.Orders
WHERE (@Status IS NULL OR Status = @Status)
  AND (@CustomerId IS NULL OR CustomerId = @CustomerId)
OPTION (RECOMPILE);
GO

EXEC dbo.SearchOrders @CustomerId = 42;
EXEC dbo.SearchOrders @Status = 'Cancelled';
Table 'Orders'. Scan count 1, logical reads 2
Table 'Orders'. Scan count 1, logical reads 9

For a search called thousands of times a second, build the WHERE clause with dynamic SQL instead, including only the conditions that apply, and run it with sp_executesql and parameters so each combination gets its own cached plan.

7. Check the estimates

In the actual plan, compare estimated and actual rows at each operator. Where they’re 10 times apart or more, the optimizer chose its joins and memory for the wrong amount of data. Update statistics, look for parameter sniffing, and replace table variables with temp tables when they hold many rows.

8. Think in sets, not rows

A cursor or WHILE loop that runs one statement per row pays the cost of a statement each time, and, outside a transaction, a commit each time. In our tests, the set-based version was 5 times faster than a cursor in a transaction, and 85 times faster than one without. See cursors.

9. Don’t repeat work

  • A CTE referenced twice is computed twice: it’s a named subquery, not a temporary result. Store the result in a temp table if it’s expensive and used more than once (CTEs).
  • COALESCE((SELECT ...), 0) evaluates the subquery twice; ISNULL once (COALESCE vs ISNULL).
  • A scalar function in the SELECT list runs once per row unless SQL Server can inline it (functions).

10. Measure, then change one thing

Run the query, note its reads, change one thing, run it again. If the reads didn’t fall, undo the change, however clever it looked. Duration is a useful second number, measured several times, but on a shared server it moves for reasons that have nothing to do with your query.

Tools that help

  • Actual execution plans in SSMS or VS Code: how to read them.
  • Query Store, to find the slow queries and see when their plans changed.
  • SET STATISTICS IO, TIME ON, for reads and CPU per statement.
  • SpDev Pro, for stored procedures: it profiles every statement, checks the actual plans for the problems on this list, and compares the reads of your next run with the last one, statement by statement.