SQL Server Query Optimization: 10 Techniques Every DBA Should Know

Why Query Optimization Matters

A query that runs in 200ms vs. 20 seconds is the difference between a snappy application and frustrated users filing support tickets. After managing SQL Server environments for Fortune 500 companies and growing startups alike, we've compiled the 10 techniques our team reaches for first.

1. Read the Execution Plan (Actually Read It)

This sounds obvious, but most developers glance at the plan and look for red flags. Senior DBAs do something different: they read it like a story. Enable actual execution plans (SET STATISTICS IO ON) and look at logical reads, not just CPU time. A query doing 50,000 logical reads when it should do 50 is your first clue.

2. Eliminate Table Scans with Targeted Indexes

Table scans on large tables are almost always wrong. If you see a Fat Arrow (→) in your execution plan pointing to a table scan, your WHERE clause columns likely lack an index. Create a covering index that includes both your filter columns and your SELECT columns to eliminate key lookups entirely:

CREATE INDEX IX_Orders_CustomerDate
  ON Orders (CustomerId, OrderDate)
  INCLUDE (TotalAmount, Status);

3. Update Statistics Proactively

SQL Server's query optimizer makes decisions based on statistics. Stale statistics lead to terrible execution plans. Don't rely solely on auto-update (it triggers at 20% rowcount change—too slow for large tables). Schedule this for off-peak hours:

EXEC sp_updatestats;
-- Or targeted for large tables:
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

4. Fix Parameter Sniffing Problems

Parameter sniffing causes your stored procedure to perform great for one user and terribly for another. The plan compiled for CustomerID=1 (10 orders) is wrong for CustomerID=99999 (500,000 orders). Solutions: OPTION (RECOMPILE) for variable-skew queries, or OPTIMIZE FOR UNKNOWN for moderate variance.

5. Avoid Functions on Indexed Columns in WHERE

This kills index usage instantly:

-- BAD: index on OrderDate is ignored
WHERE YEAR(OrderDate) = 2024

-- GOOD: index is used
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

6. Use SET NOCOUNT ON in Stored Procedures

Each INSERT, UPDATE, and DELETE sends a "rows affected" message back to the client. In a loop doing 10,000 iterations, that's 10,000 extra network round trips. SET NOCOUNT ON at the top of every stored procedure eliminates this.

7. Batch Large DELETE/UPDATE Operations

Deleting 5 million rows in a single statement locks the table and fills the transaction log. Batch it:

WHILE 1=1
BEGIN
  DELETE TOP (10000) FROM AuditLog WHERE CreatedAt < '2023-01-01';
  IF @@ROWCOUNT = 0 BREAK;
  WAITFOR DELAY '00:00:01'; -- breathe
END

8. Check for Missing Indexes (But Don't Trust Them Blindly)

SQL Server tracks missing index suggestions in sys.dm_db_missing_index_details. These are useful starting points, not gospel. The engine sometimes suggests creating 15 indexes when 2 well-designed ones do the same job. Review impact score AND your existing index set before adding anything.

9. Rebuild vs. Reorganize Indexes Correctly

Fragmentation over 30%? Rebuild. Between 5-30%? Reorganize. Under 5%? Leave it alone. Most teams rebuild everything on a schedule regardless of fragmentation level—that's wasted I/O and blocking. Script it properly:

SELECT object_name(object_id), index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5;

10. Profile with Extended Events, Not Profiler

SQL Server Profiler is deprecated and has significant performance overhead. Extended Events are lighter and more powerful. Create a session targeting your specific problem (slow queries, blocking, deadlocks) instead of capturing everything and filtering later.

Start Here

If you're overwhelmed, start with #1 and #3. Read your execution plans and keep your statistics fresh. Those two alone resolve 60% of query performance complaints we encounter.