Start by capturing the actual execution plan rather than estimated plans. This reveals cardinality misestimates and missing index warnings that directly affect runtime cost.

Focus on sargable predicates and avoid wrapping columns in functions. These two changes alone frequently drop logical reads by an order of magnitude on tables exceeding one million rows.

#Reading Actual Execution Plans

Enable query store or use SET STATISTICS XML ON to retain plans across executions. Compare CPU time and elapsed time to identify whether the bottleneck is CPU-bound or I/O-bound.

#Index Selection Patterns

  • Create a clustered index on the most selective column used in range predicates.
  • Add non-clustered indexes only for columns appearing in WHERE, JOIN, and ORDER BY clauses together.
  • Include frequently selected columns in the index key or as INCLUDE columns to avoid key lookups.

#Sargable Predicate Examples

tsql 
SELECT OrderID, OrderDate
FROM Sales.Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2026-02-01';

Rewrite non-sargable expressions such as YEAR(OrderDate) = 2026 into explicit date range comparisons shown above.

#Practical Takeaway

Measure before and after every change using sys.dm_exec_query_stats. Retain only indexes that demonstrably reduce logical reads or elapsed time on representative workloads.