Production SQL Server workloads degrade quickly when queries scan large tables or return unnecessary columns. The fastest wins come from rewriting queries to use sargable predicates and covering indexes rather than adding hardware.

Execution plans remain the primary diagnostic tool. Reviewing estimated versus actual rows, key lookups, and sort operators reveals the exact changes needed before touching configuration settings.

#Replace SELECT * and Non-Sargable Predicates

Selecting every column forces SQL Server to read extra pages and prevents index-only access. Replace it with explicit column lists that match the narrowest covering index.

tsql 
SELECT OrderID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerID = @CustomerID
  AND OrderDate >= DATEADD(month, -3, GETDATE());

#Use EXISTS Instead of IN for Large Lists

IN predicates with thousands of values often produce poor cardinality estimates. EXISTS with a correlated subquery usually yields a more stable plan and avoids unnecessary materialization.

  • Test both forms against your data distribution before standardizing.
  • Add OPTION (RECOMPILE) only on ad-hoc reports, not on stored procedures executed thousands of times per hour.

#Monitor with Query Store and Extended Events

Query Store captures runtime metrics per query plan without manual trace setup. Force a better plan only after confirming it reduces CPU and logical reads over a full business cycle.

Capture wait stats alongside query metrics. High PAGEIOLATCH waits point to index or memory pressure rather than query syntax alone.

#Practical Takeaway

Start every tuning engagement by capturing the top ten queries by total CPU or logical reads. Apply the patterns above, measure the delta in Query Store, and retain only the changes that produce repeatable improvement.