Query Store for Procs

Status: Reviewed (deep-dive v1)
Stack note: After you deploy usp_* (SQL-12) or indexes (SQL-16), Query Store is how you catch “board got slow” before users pile into SPIKE-05. Warehouse DB should have QS on; Blazor still calls procs — you hunt plans by proc/query id, not by ad-hoc UI SQL.
Pairs with: SQL-07 / SPIKE-05 (perf triage), SQL-12/16/18 (procs, indexes, blocking), SQL-03 (stats).
Goal: Enable, watch regressions on usp_*, force/unforce plans carefully, feed the triage board.

Quick chooser

Need	Do this
Catch post-deploy regressions	QS ON + look at regressed/ top duration for warehouse
“Which board proc is hot?”	Filter QS by `OBJECT_NAME` / query text `usp_`
Bad plan after stats update	Compare plans in QS; force prior plan only as bridge
Long-term history	Set capture + retention; don’t leave default tiny
Blocking / waits	QS helps plans; pair with waits/XE / SPIKE-05 for locks

Enable (warehouse)

ALTER DATABASE [YourWarehouse]
SET QUERY_STORE = ON
(
  OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 2048,          -- size for estate; monitor
  QUERY_CAPTURE_MODE = AUTO,           -- or ALL on small DBs
  SIZE_BASED_CLEANUP_MODE = AUTO,
  MAX_PLANS_PER_QUERY = 200
);

-- verify
SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Checklist:

[ ] QS on warehouse (and critical instance DBs if you run boards there)
[ ] Not stuck READ_ONLY (disk full / max size)
[ ] Capture mode sane — AUTO usually enough
[ ] Alert if actual_state_desc ≠ READ_WRITE (SQL-10)

Find hot / regressed `usp_*`

SSMS: Query Store → Regressed Queries / Top Resource Consumers → filter.

T-SQL lean (duration example):

SELECT TOP (20)
  q.query_id,
  OBJECT_NAME(q.object_id) AS proc_name,
  qt.query_sql_text,
  rs.avg_duration / 1000.0 AS avg_ms,
  rs.count_executions,
  rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id IN (
  SELECT p.plan_id FROM sys.query_store_plan p WHERE p.query_id = q.query_id
)
WHERE q.object_id IN (
  SELECT object_id FROM sys.procedures WHERE name LIKE N'usp_%'
)
ORDER BY rs.avg_duration DESC;

Tune to your stats interval; prefer SSMS UI for plan shapes when teaching.

Deploy habit: note time of release → compare runtime stats before/after window for touched procs.

Plan forcing (bridge, not lifestyle)

-- after identifying query_id + good plan_id in QS UI or catalog
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;

-- when fixed for real (stats/index/rewrite):
EXEC sys.sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;

Rules:

[ ] Force only with a ticket + owner
[ ] Root-cause: stats (SQL-03), missing IX (SQL-16), sniffing (SQL-12/14), blocking (SQL-18)
[ ] Re-check after next deploy — forced plans can go invalid
[ ] Don’t force on every flicker — capture baseline noise first

Tie to SPIKE-05 (perf triage board)

SPIKE-05 signal	QS contribution
Top queries / wait deltas	Confirm which `usp_*` / statements
“Slow board” incident	Regressed query for that proc
Post-index change	Compare plans for GetOpenBoard / heat map
Evaluate job long	QS on merge/evaluate statements

Optional: nightly PS job → top QS consumers into warehouse table → Blazor triage panel (SPIKE-05 path). Keep it read-only observe.

What QS does not replace

Deadlock graphs / blocking chains (SQL-18, XE)
Index missing analysis alone — still need plans + #temp habits (SQL-13)
App timeouts from connection leaks
“Someone ran ad-hoc in SSMS” — capture mode dependent

Ops checklist

[ ] After each usp_*/index deploy: glance Regressed + Top Duration
[ ] Weekly: storage used vs max; grow before READ_ONLY
[ ] Document forced plans list (query_id, why, remove-by date)
[ ] Same maintenance window as stats: expect plan flips — QS is your recorder
[ ] AG/HA: know QS behavior per replica if you fail over mid-incident (SQL-09)

Common failure patterns

Symptom	Likely cause
Empty QS	Off, or wrong DB
READ_ONLY	Hit max size / disk
Can’t find proc	Looking at text noise; filter `object_id`
Forced plan ignored	Plan invalid after schema change
“Regression” every night	Stats job + sniffing — not a QS bug

Tiny lab

Enable QS on a lab copy of warehouse
Run usp_Alert_GetOpenBoard before/after a bad index drop — see regression
Force prior plan — confirm; unforce; add proper IX

Tie-ins

SQL-07 / SPIKE-05: Triage workflow
SQL-12/16: Proc + index changes are QS events
SQL-03: Stats maintenance ↔ plan churn

Done when

[ ] Warehouse QS ON and monitored for READ_ONLY
[ ] Deploy checklist includes QS regression glance for touched usp_*
[ ] Forced-plan policy written (temporary + tracked)
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-20 Query Store for Procs v1