Execution plans reveal the actual work performed by the query optimizer. Reviewing them first identifies the largest opportunities for improvement before any code changes.

The most common bottlenecks are missing indexes, implicit conversions, and non-sargable predicates. Addressing these three areas yields measurable gains on high-volume workloads.

#Reading Execution Plans

Capture the actual plan with SET STATISTICS XML ON or by enabling query store. Look for expensive operators such as table scans, key lookups, and sorts that dominate cost.

Compare estimated versus actual row counts. Large discrepancies indicate stale statistics or parameter sniffing issues that require plan guides or forced parameterization.

#Indexing for Common Patterns

Create covering indexes for frequent SELECT lists. Include columns referenced in WHERE, JOIN, and ORDER BY clauses to eliminate lookups.

  • Use filtered indexes when queries target a narrow range of values, such as active records only.
  • Avoid over-indexing write-heavy tables; each additional index increases maintenance cost during inserts and updates.

#Writing Sargable Predicates

Keep expressions on the left side of comparisons free of functions. Apply functions only to constants or variables on the right side.

tsql 
SELECT * FROM Orders WHERE OrderDate >= DATEADD(day, -30, GETDATE());

Replace LIKE '%term' patterns with full-text search or a reverse column when prefix searches are required.

#Monitoring with DMVs

Query sys.dm_exec_query_stats and sys.dm_db_missing_index_details regularly. Focus on queries with the highest total worker time and those flagged by missing index suggestions.

Clear the procedure cache only after confirming a plan is harmful; use DBCC FREEPROCCACHE with a specific plan handle when possible.

Track these changes over time with query store to verify that index additions or query rewrites deliver sustained improvement rather than temporary relief.