Elevating SQL Server Performance with Query Store Hints

on YouTube ↗20m

" summary

SQL Server 2022 lets Query Store attach hints to a captured query so execution changes without touching application code; demo shows using built-in procedures to list query IDs, apply and clear hints, and verify plans. Two concrete fixes: apply MAXDOP=1 to remove parallelism across all occurrences of a query, and apply RECOMPILE to avoid parameter sniffing that caused a bad plan for one product ID; changes take effect on next execution and persist across restarts.

AI-written summary · about

" chapters (8)

1. 0:00Introduction
2. 1:12What is Query Store
3. 3:00SQL Server hints basics
4. 6:11Query Store hints
5. 9:04Demo: Setup Query Store hints
6. 14:04Demo: Avoid parameter sniffing
7. 18:46Benefits of Query Store hints
8. 19:50Q&A

" related talks

• 20mElevating SQL Server Performance with Query Store HintsRui Carvalho · SQLBits 2024
• 22mA Beginners guide to Query StorePeter Kruis · SQLBits 2024
• 19mStabilizing Performance with Query StoreErin Stellato · SQLBits 2019
• 50mBuilt-in Intelligence with Azure SQL and SQL Server 2022David Pless · SQLBits 2023
• 19mStabilizing Performance with Query StoreErin Stellato · SQLBits 2024
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019
• 48mQuery Store: A RetrospectiveConor Cunningham · SQLBits 2023
• 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 MinutesHaider Raza, Manish Kumar · SQLBits 2019