on March 1, 2018
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.
Update: this course is now open and totally free to all.
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.
Pretty cool.