Auto Tuning with Automatic Plan Correction in Query Store (1 hour 8 minutes)

WARNING: I do NOT currently recommend enabling the Automatic Plan Correction feature due to issues described in my post, Automatic Plan Correction Could be a Great Auto Tuning Feature for SQL Server. Here’s why it Isn’t.

Want a built-in assistant performance tuner in SQL Server?

Try the course quiz anytime.

Your new assistant 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

https://github.com/LitKnd/SQLWorkbooks/tree/main/automatic_plan_correction

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.

Lessons

Automatic Plan Correction: What It Does & How to Enable It (6 minutes)

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)

Quiz on Automatic Tuning in SQL Server