Production workloads often suffer from a small number of poorly performing queries rather than overall server capacity. SQL Server's Query Store and execution plan features let you locate those queries quickly and measure the impact of changes.

The approach starts with enabling Query Store, capturing runtime metrics, and then applying targeted index or query rewrites. This workflow replaces broad guesses with data-driven decisions.

#Enabling Query Store for Continuous Capture

Query Store records query text, plans, and runtime statistics at the database level. Once enabled, it retains data across restarts and provides a historical view of plan changes.

  • Set QUERY_STORE with desired retention and flush intervals
  • Query the sys.query_store_runtime_stats view for top resource consumers
  • Force a known-good plan when regressions appear

#Addressing Parameter Sniffing

Parameter sniffing produces a plan optimized for the first parameter value seen. When later values produce different cardinalities, the cached plan can degrade performance.

tsql 
EXEC sp_executesql N'SELECT * FROM Orders WHERE CustomerID = @cid OPTION (RECOMPILE)', N'@cid int', @cid = 12345;

The OPTION (RECOMPILE) hint or the OPTIMIZE FOR UNKNOWN hint can mitigate the issue when a single plan cannot suit all parameter values.

#Index and Statistics Maintenance

Outdated statistics cause the optimizer to select inefficient join orders or access methods. Scheduled maintenance that updates statistics and rebuilds fragmented indexes keeps plans accurate.

  • Update statistics after large data loads or deletes
  • Rebuild indexes only when fragmentation exceeds 30 percent
  • Use filtered statistics for highly skewed columns

Measure the effect of each change by comparing Query Store runtime stats before and after. Consistent measurement prevents unnecessary index proliferation.