Start by capturing the actual execution plan for any slow query instead of relying on estimated plans or intuition. The plan reveals which operators consume the most resources and whether indexes are being used or created on the fly.

In production workloads the biggest gains usually come from removing unnecessary sorts, reducing key lookups, and replacing scalar UDFs with inline table-valued functions. These changes often deliver order-of-magnitude improvements without hardware upgrades.

#Reading Actual Execution Plans

Enable query store or use SET STATISTICS XML ON to collect real runtime metrics. Look first at the estimated versus actual row counts; large discrepancies indicate stale statistics or parameter sniffing issues.

Focus on operators with high CPU or I/O cost. A hash join spilling to tempdb or a clustered index scan on a multi-million-row table are immediate signals that an index or rewrite is required.

#Indexing for Read-Heavy Workloads

  • Create covering indexes only for queries that execute frequently and return modest result sets.
  • Include filter columns in the key and output columns in the INCLUDE clause to eliminate key lookups.
  • Drop unused indexes identified by sys.dm_db_index_usage_stats before adding new ones.

#T-SQL Patterns That Scale

tsql 
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Sales.Orders o
JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= DATEADD(month, -3, GETUTCDATE())
OPTION (RECOMPILE);

Avoid wrapping columns in functions inside WHERE clauses. The example above keeps the predicate sargable so an index on OrderDate can be used.

Replace multi-statement table-valued functions with inline equivalents when possible. Inline functions allow the optimizer to push predicates and produce better cardinality estimates.

#Ongoing Maintenance

Rebuild or reorganize indexes only when fragmentation exceeds 30 percent and page density is low. Update statistics with FULLSCAN on large tables after bulk loads rather than relying on the default sampling rate.

Schedule regular query store cleanup and force plans only for the small set of queries whose performance is stable and critical. Over-forcing plans reduces the optimizer's ability to adapt to data changes.

Measure end-to-end duration and resource use before and after each change. Track waits in sys.dm_os_wait_stats to confirm that CPU or PAGEIOLATCH waits have decreased.