Auto Tuning with Automatic Plan Correction In Query Store (1 hour 8 minutes)
Want a built-in assistant performance tuner in SQL Server?
One who makes recommendations about queries that need speeding up? In SQL Server 2017+ Enterprise Edition and Azure SQL Database, Query Store can be your new Junior DBA. You get to choose whether or not to allow your assistant to try out and test its suggestions, or whether to manually review them yourself.
In just over an hour of videos, we’ll explore how the Automatic Plan Correction feature in Query Store works: how to configure it, what it does, and steps to get started using it.
Not using Enterprise Edition?
If you do a lot of performance testing in test environments with Developer Edition, this feature can help you out there. I’ll also show you how to manually find queries with wide variations in runtimes using built-in reports with Query Store.
Get the scripts
This course originated as a recorded webcast. It’s been edited to only include Q&A that directly correlates to the course content, and additional lessons on RECOMPILE have been added.
- Finding Automatic Plan Correction in DMVs with TSQL (10 minutes)
- The Queries With High Variation Query Store Report (4 minutes)
- How to Tell if a Query Plan Has Been ‘Corrected’ (6 minutes)
- Expiring an Automatic Correction With Update Statistics (10 minutes)
- Automatic Plan Correction Can Re-Correct! Plus: Our Correction Survives a Plan Cache Flush (10 minutes)
- Why Automatic Plan Correction Skips Queries Using Recompile (3 minutes)
- Demo: Recompile, Automatic Tuning, and Manual Plan Forcing in Query Store (14 minutes)
- Observations, Starter Notes, and Final Q&A (5 minutes)