SQL Server Query Performance Optimization Tips

A query that runs fine against a dev database with a thousand rows can fall over completely once it is running against production data with ten million. We see this pattern constantly during code reviews and support tickets, and the fixes are usually the same handful of things — a missing index, a WHERE clause that quietly disables index use, or a habit of pulling more data than the query actually needs.

Read the Execution Plan First

Before touching any code, get the actual execution plan (Ctrl+M in SSMS, then run the query). Look for Table Scan or Clustered Index Scan operators on large tables — those mean SQL Server is reading every row instead of seeking directly to what it needs. An Index Seek is what you want to see on the tables driving your WHERE clause. The relative cost percentages SSMS shows next to each operator point you at which part of the query is actually expensive, which matters a lot once queries have several joins.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2024-01-01';

-- Look at "logical reads" per table in the Messages tab.
-- A high read count on a table that should be filtered down
-- hard by an index is the clearest sign of a missing index.

STATISTICS IO gives you logical reads per table, which is a far more stable metric than wall-clock time — time varies with server load and caching, logical reads do not. Run the query once to warm the cache, then compare reads before and after an index change; that is the real before/after signal, not "it felt faster."

Sargable WHERE Clauses

A "sargable" predicate is one SQL Server can use directly against an index. Wrapping an indexed column in a function, or comparing it against a mismatched data type, breaks that — SQL Server has to evaluate the expression for every row, which forces a scan even when a perfectly good index exists.

-- Non-sargable: YEAR() wraps the indexed column, forcing a scan
SELECT OrderId, CustomerId, TotalAmount
FROM Orders
WHERE YEAR(OrderDate) = 2024;

-- Sargable: OrderDate is used bare, so an index on OrderDate can be seeked
SELECT OrderId, CustomerId, TotalAmount
FROM Orders
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

The same trap shows up with implicit conversions — comparing an nvarchar column against a varchar literal, or an int column against a value passed in as a string from application code, can silently force a conversion on the column side instead of the literal side, quietly disabling an index that looks correct on paper. If a query with a seemingly fine index still shows a scan, check the data types on both sides of every predicate.

Indexing Strategy

An index on the WHERE clause columns gets you a seek, but if the query also selects columns not in that index, SQL Server has to do a key lookup back into the clustered index for every matching row — expensive once you are past a few hundred rows. A covering index, one that INCLUDEs the selected columns alongside the key columns, avoids that lookup entirely.

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Covering
ON Orders (OrderDate)
INCLUDE (CustomerId, TotalAmount);

• Never SELECT * in application code — it pulls columns you do not need and defeats covering indexes.
• Put the most selective column first in a composite index, unless a specific query pattern needs otherwise.
• Avoid leading-wildcard LIKE searches — they cannot use an index at all; a trailing wildcard still can.
• Check for parameter sniffing on stored procedures whose runtime is wildly inconsistent between calls with different parameter values.
• Update statistics (or rebuild indexes) periodically on tables with heavy write activity — stale statistics lead the optimizer to bad plan choices.

Wrapping Up

Most slow-query tickets we get boil down to one of these three things: a scan where a seek should be, a non-sargable predicate, or a query pulling far more columns or rows than it needs. Check the execution plan and logical reads before guessing, and the fix is usually obvious once you see it laid out.