Execution plans are the primary diagnostic tool for understanding query behavior in SQL Server. They show the exact operators chosen by the optimizer, estimated and actual row counts, and resource costs. Reviewing plans before and after changes prevents guesswork when tuning high-volume workloads.

The most useful approach is to capture the actual execution plan rather than the estimated one. Actual plans expose discrepancies between estimates and runtime behavior, especially around cardinality and memory grants. This difference often points directly to missing statistics or parameter sniffing issues.

#Capturing Plans in Production

Enable the query store or use extended events to collect plans without enabling server-wide trace flags. For ad-hoc investigation, the Include Actual Execution Plan option in SSMS or the SET STATISTICS XML ON command provides immediate results. Always capture plans from a representative workload rather than synthetic tests.

#Operators That Indicate Problems

  • Table scans on large tables usually signal missing indexes or poor predicate selectivity.
  • Key lookups combined with high execution counts point to non-covering indexes.
  • Spools and sorts often indicate missing indexes on join or order-by columns.
  • Excessive memory grants frequently trace back to inaccurate cardinality estimates.

#Querying Plan Cache and DMVs

tsql 
SELECT qs.execution_count, qs.total_elapsed_time, qs.total_logical_reads,
       SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
                 ((CASE qs.statement_end_offset
                     WHEN -1 THEN DATALENGTH(st.text)
                     ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS query_text,
       qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 1000000
ORDER BY qs.total_elapsed_time DESC;

The query above surfaces the most expensive cached plans by elapsed time. Combine the results with sys.dm_exec_query_plan to inspect XML plans directly in T-SQL. Regularly clear or flush individual plans only after confirming they are the source of regression.

#Practical Next Steps

Start with the highest-cost operators shown in the plan. Add covering indexes only after verifying the predicates and join conditions. Update statistics on tables referenced by the plan when row counts differ significantly between estimated and actual. Recompile the query or use OPTIMIZE FOR hints only when parameter sniffing is confirmed as the root cause.

Track plan changes over time with Query Store to detect regressions after application deployments or statistics updates. This disciplined review cycle keeps production queries predictable and resource-efficient.