Query Store Best Practices
summary
Explains concrete rules and knobs: Query Store cost depends on SQL version and CU (many fixes backported), default max size 1 GB (recommend 2–4 GB), Azure caps at 10 GB, and ad hoc workloads can blow the in‑memory hashmap (demo shows 100k executions producing ~20 plans for parameterized code versus thousands of unique entries and memory jumping from ~3 MB to ~100 MB for ad hoc). Recommends custom capture mode (2019+) with compile/execution thresholds, use TF 7752 and TF 7745 as needed, and monitor before enabling replicas.
AI-written summary · about
chapters (10)
- 0:00Introduction and basics
- 1:48How query store works
- 7:13Performance and versioning
- 14:01Workload types and impact
- 21:07Demonstration and tuning
- 28:34Best practices and tips
- 35:49Configuration recommendations
- 39:24IQP and replicas
- 43:58Final recommendations and QA
- 45:53Detailed Q&A session
related talks
50mQuery Store Best Practices
48mQuery Store: A Retrospective
22mA Beginners guide to Query Store- 22mA Beginners guide to Query Store
26mUsing Azure Query Store to Understand PostgreSQL Performance
19mStabilizing Performance with Query Store- 19mStabilizing Performance with Query Store
51mMaking the SQL Query Processor Work for you