Production SQL Server performance problems are rarely solved by increasing hardware. The fastest improvements come from rewriting queries to reduce logical reads and from maintaining indexes that match actual query patterns.

Recent SQL Server releases continue to emphasize the same fundamentals: accurate statistics, sargable predicates, and minimal data movement. The techniques below apply across current supported versions without requiring preview features.

#Start with the Execution Plan

Capture the actual execution plan for slow queries using SET STATISTICS XML ON or Query Store. Look first for high-cost operators such as table scans, key lookups, and sorts that spill to tempdb.

#Write Sargable Predicates

Avoid wrapping columns in functions. The following pattern forces a scan even when an index exists on the date column.

tsql 
WHERE YEAR(OrderDate) = 2025

Rewrite the predicate to allow index seeks:

tsql 
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'

#Index Maintenance That Matters

  • Rebuild indexes only when fragmentation exceeds 30 percent and page count is above 1,000.
  • Update statistics after large data loads or deletes; do not rely solely on the default auto-update threshold.
  • Use filtered indexes for queries that select a small, stable subset of rows.

#Batch Updates and Deletes

Large single-statement updates generate excessive log growth and blocking. Break work into batches of 5,000–10,000 rows using a loop or a numbers table.

Measure the impact of each change with Query Store before and after. Retain the baseline for at least 30 days so regressions are visible when data volume or parameter values shift.

Apply these patterns consistently and you will see sustained reductions in CPU and I/O without constant tuning cycles.