I’m thrilled to have just finished and published a new course on Automatic Tuning in SQL Server. This feature is available in SQL Server 2017+ and Azure SQL Database.
I am WAY more excited about this feature than I thought I would be
Cynical me assumed…
- This feature won’t be that cool
- It only works sometimes — when you’ve actually had decent performance in the past!
- It’ll probably be really clumsy
I shouldn’t be so cynical, because after working with this even for a short time, I started to see the magic.
I’m glad I was wrong
Yes, the feature only works sometimes. However, that’s because it’s NOT really clumsy. It’s quite thoughtful!
Automatic Plan Correction is smart enough to test and verify its changes. It’s smart enough to back off what it’s done when you add an index, or when your data distribution changes, and then test and verify again.
But this is OK, because it’s not trying to permanently fix things. It’s trying to make things better for a little while until YOU can step in and figure out a permanent fix!
Automatic Tuning makes it much easier to find bad parameter sniffing
One of the trickiest performance problems that developers and DBAs face is figuring out “why was this thing slow two hours ago, but it’s fast now?”
Many times, the answer to this is “bad parameter sniffing”: we had an execution plan in cache that was slow when the query was executed with a variety of different parameter values.
This is hard to identify because often a different plan is currently in cache by the time we look at it. And even if we have fancy monitoring tools, it can be tricky to compare the average costs of the plans and determine which plan may be better.
The Automatic Plan Correction feature is looking exactly for things like that.
And that is a hard enough problem to detect that I see why this is an Enterprise Only feature. I get it.
Best of all, you can try this out in a safe way
You can either let Automatic Plan Correction test out changes for you, or just look at its recommendations.
The recommendations contain pretty rich detail about the problem it observed, the regressed plan, and the plan that looks like it would be better. You can take that information and use it with Query Store to devise your own fix safely.
This course is for subscribers. If you haven’t joined, you can watch the first two video lessons for free
The first two lessons in the course cover:
- Automatic Plan Correction: What It Does & How to Enable It (6 minutes)
- Finding Automatic Plan Correction in DMVs with TSQL (10 minutes)
No signup or login is required to check out those lessons. Click the links and preview away.
Subscribers also get…
Demo scripts, a PDF of the slides, and a quiz, plus modules on:
- 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)
I also talk in the course about things you can do to manually find these problem plans in Query Store if you don’t have Enterprise Edition.
Interested in subscribing?
I’d love to have you join. Sign up here.