Query Store Best Practices
summary
Query Store works but only with specific configuration and versioning: apply recent CUs (2019+ adds custom capture mode), keep the max size modest (default 1 GB, start 2–4 GB, Azure enforces 10 GB limit), and avoid size-based cleanup by monitoring and tuning retention. Ad hoc ORM workloads create massive unique entries — parameterize the top 5–10 hot queries or use custom capture thresholds (compile/exec CPU) to filter. Use trace flags 7752 and 7745 on older releases; read-only replica captures still write back to primary.
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
26mUsing Azure Query Store to Understand PostgreSQL Performance
19mStabilizing Performance with Query Store
20mElevating SQL Server Performance with Query Store Hints- 19mStabilizing Performance with Query Store
22mEmpowering SQL: Mastering Intelligent Query Processing in 20 Minutes