Elevating SQL Server Performance with Query Store Hints

on YouTube ↗20m

" summary

Demonstrates how SQL Server 2022 Query Store Hints let DBAs change plan behavior without touching application code by registering hints against captured query IDs and persisting them across restarts. Uses the built‑in sp_query_store_hints procedure to apply MAXDOP=1 and OPTION(RECOMPILE) to two example queries (product IDs 897 vs 870, one filter returning ~4,000 rows) and shows the bad cached plan is replaced at next execution, eliminating unwanted parallelism and parameter‑sniffing regressions.

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 2022
• 51mMaking the SQL Query Processor Work for youJoe Sack, Ajay Jagannathan, Derek Wilson · SQLBits 2022
• 19mStabilizing Performance with Query StoreErin Stellato · SQLBits 2022
• 19mStabilizing Performance with Query StoreErin Stellato · SQLBits 2019
• 22mA Beginners guide to Query StoreKevin Kline · SQLBits 2022
• 50mBuilt-in Intelligence with Azure SQL and SQL Server 2022David Pless · SQLBits 2023
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019
• 48mQuery Store: A RetrospectiveConor Cunningham · SQLBits 2023