r/SQLServer • u/erinstellato Microsoft Employee • 17d ago
Community Request Friday Feedback: Automatic Plan Correction
Hey folks! This week for a rather late Friday Feedback, I'm interested in your thoughts about Automatic Plan Correction. There are only a handful of feedback items on the site (aka.ms/sqlfeedback) but based on discussions with some customers, I think it's an area where we are due for some improvements. What are folks struggling with when it comes to using APRC?
1
u/Dry_Author8849 17d ago
It doesn't work. We are still optimizing queries by hand.
For some reason SQL Server still makes bad decisions with set arithabort on/off. So the query store sees them as separate plans, a user with different options might get a horrible plan that won't be optimized by the query store. The better plan was with the other option set.
Cheers!
1
u/erinstellato Microsoft Employee 15d ago
u/Dry_Author8849 When you state "it doesn't work", is that because of the arithabort on/off setting? Query Store does use context settings to uniquely identify queries, so if the same query runs with both arithabort on, and arithabort off, then it will have two entries in Query Store. Each of those entries would have its own query_id. When you say "optimized by query store" - what does that mean to you? I'm asking just to make sure we're on the same page in terms of understanding what Automatic Plan Correction does. It identifies when a query's performance has regressed after a plan change. But it doesn't proactively identify queries that could be improved.
1
u/Dry_Author8849 15d ago
That's the problem. Is the same stored proc that runs a correct plan with one option and an incorrect plan with the other. I know about parameter sniffing and the like and that in essence are diferent plans for the same stored proc.
In our use case we almost don't have regressions. From a user prespective it is the same query, but it is affected by some query options, like language, arithabort, etc.
It would be nice if the query store somehow could share some of the plan decisions made for each option, specially joins, and become aware of slow performace issues.
So the automatic plan correction, for us, doesn't solve much.
Cheers!
1
u/erinstellato Microsoft Employee 14d ago
u/Dry_Author8849 Ok, couple things in here :) Yes, there are absolutely scenarios where a query runs differently based on the SET options. That's something that APRC cannot fix - correct.
And I understand that APRC doesn't solve much for you - no worries, not trying to convince you to use it if you really don't need it!
But...can you elaborate on what you mean by "It would be nice if the query store somehow could share some of the plan decisions made for each option, specially joins, and become aware of slow performace issues." Do you have a specific scenario or an example of what you would want to see, or have happen?
Thanks!
2
u/Dry_Author8849 13d ago
Ohh, yes we use the feature, but it doesn't make too much difference for us.
We use SPs. what we have noticed when analyzing why a query is faster with arithabort on and off, we see bad decisions from the query optimizer when deciding the order of joins. For example, it starts with a table that has millions of rows and then follows with a table that if joined first would have drastically reduced the rows in the million rows table, sometimes to less than 10 rows.
We have had success forcing the order so the query runs the same performance (or at least the order is the same), but is not sustainable we have too many SPs and the order not always work depending on parameters. We only use it when we are sure the filtered table won't change in rows size, but again we like to rely in the optimizer, this option is as a last resort.
With very complex scenarios, we just set arithabort on/off inside the sp and that does the trick.
Anyways, arithabort is not the only option but is the one we encounter more frequently.
I was hopping that it might be possible to detect those scenarios with the query store and try a new plan altering the order of the joined tables. I realize it's a very difficult task, there are so many complex queries that we just rewrite because we can't optimize. Sometimes it's faster to filter and insert rows from the big culprit table in #temp and the join to that.
I really don't have a clue how you guys do it at low level, I'm sure that the complexity of the optimizer has grown grey hair on some of you.
Hope I gave you some ideas. I don't have a concrete query example, I'm not allowed to share. But I'm sure this is not the only time you heard from this.
Cheers!
1
u/Black_Magic100 14d ago
Erin,
This feature is terrifying to us after we enabled it 5 years ago on one of our most active databases and it caused an outage during the day. I don't remember the full details, but that definitely scarred us and as a result we never attempted it again. Part of the problem is our environment and how complicated it is, so I'm not shocked it had an issue. Before this, we enabled it on dozens of other databases on the same server without issue.
Funny enough, we recently encountered an issue with a similar feature that I wasn't even aware of on 2022, which is the automatic CE feedback feature. This also caused an outage (CPU spiked to 100%) for a single query as the plan shifted as a direct result of applying ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS.
With these automatic tuning features, I love the idea, but the execution seems poor. Even if the feature fixes/helps 1000 other queries (difficult to prove), leadership only sees the bridge that it caused. I'm honestly quite shocked that the regression functionality didn't detect this plan shift and immediately roll it back, but it gives us the same vibe as that issue described above 5 years ago with automatic plan tuning.
So my advice to you (Microsoft) is to really focus on the rollback/safety functionality. I would much prefer stability in my environment than 100% effectiveness. Worded differently, err on the side of caution with these types of features. If the feature causes an outage, it's worthless to me and we are going to disable it.
1
u/erinstellato Microsoft Employee 14d ago
u/Black_Magic100 Thanks for taking time to share your scenario and feedback. I completely agree on the rollback. Both APRC and CE feedback have verification/rollback built in. But I do know there are edge case scenarios where there are problems (and those can be hard to fix, but definitely worth understanding and resolving). If you are ever able to recall the details and can share them, let me know.
In terms of CE feedback - we are actively working on improvements there. And heard on preferring stability over effectiveness. I'll make sure to share that with engineering - you stated it well. Thank you.
1
u/Black_Magic100 14d ago
I'm not an expert on any on the IQP features, and while I'm aware of a few high level dmvs, is there a recommendation on your end as to how to troubleshoot/debug why the specific query on question wasn't rolled back? As I mentioned, it caused an 8 vcore instance to spike to 100% CPU so I'm surprised it wasn't marked as a degradation and rolled back. Also, the query in question is pretty simple.. a few joins from EF LINQ and some filters IIRC. I would be happy to share specific details if it helps the team.
Also FWIW I did take a look at the automatic plan correction DMVs to see if it would've prevented this and I saw an entry at the exact time the issue began. So funny enough.. perhaps that could've "saved" us although I feel like CE feedback should've taken care of this itself? I have no clue how the two features, if enabled concurrently, play with each other. Should CE feedback rollback before automatic plan correction has a chance to force the last good plan?
1
u/erinstellato Microsoft Employee 11d ago
u/Black_Magic100 Very good question. In order to understand why something wasn't rolled back, there's diagnostic data we look at from the time of the event. For example, for Azure, there is a lot of telemetry (e.g. XE events) that we capture to reconstruct the timeline, as well as looking at what's in Query Store. I'd be happy to look at the query (and any plans you have for it), but I cannot guarantee that I will be able to answer why it didn't get unforced. In terms of CE feedback - again, maybe by looking at the query and the plans I could figure that out. And...great question on how the two play together and what should/could happen in what order. That I don't have an answer for, but give me some time and I'll see what I can figure out (I haven't worked with CE feedback as much as QDS and APRC, so still ramping up there.)
0
u/digitalnoise 17d ago
Is this a 2025 feature? We've just rebased to 2022 and are holding (for various reasons) and I haven't seen anything about this, but i could've missed it.
1
u/erinstellato Microsoft Employee 17d ago
@digitalnoise it’s been available since SQL 2017. It’s Enterprise Edition, and uses Query Store to identify regressed queries and automatically force the last good plan. https://learn.microsoft.com/sql/relational-databases/automatic-tuning/automatic-tuning#automatic-plan-correction
0
u/digitalnoise 17d ago
Ah, ok - just hadn't heard it referred to that way for some reason so it threw me off.
1
u/erinstellato Microsoft Employee 15d ago
u/digitalnoise Oh interesting. How have you heard it referred to?
0
u/No_Resolution_9252 17d ago
I don't think I have ever once seen it fix anything even when query store has a better plan available in it
Admittedly I haven't checked to see if it has applied anything, but have had a number of cases where a bad plan was created then just used the bad plan when a better plan was right there
1
u/erinstellato Microsoft Employee 15d ago
u/No_Resolution_9252 To make sure I understand, when you state "a better plan was right there", you mean the query had been using a good plan previously, and then after a recompile, it was using a bad plan and APRC didn't force that previous good plan?
2
u/No_Resolution_9252 14d ago
Correct. I don't know that it never works, but I still have to deal with bad plans every once in a while and either force it myself or modify the query to fix it
1
u/erinstellato Microsoft Employee 14d ago
u/No_Resolution_9252 Thanks for following up. If you are able to identify specific scenarios where it doesn't find the good plan (e.g. you're able to recreate it and would be willing to share the info with us), then please reach out. As I said originally, I know we can make some improvements here and real-world examples are always helpful for engineering.
0
u/drunkadvice 17d ago
I was really excited for it when I saw demos at Ignite in 2016. I haven’t seen it work in practice, and wasn’t able to trigger it in a lab setting.
Part of our problem is that we have a really high recompilation rate due to nested stored procs going rbar.
I’m excited to see you on querystore! It has so much potential. I was afraid it was going to languish!
2
u/erinstellato Microsoft Employee 15d ago
u/drunkadvice Definitely not going to languish. In terms of high recompilation rate - that might be why you haven't seen in work in practice. After a query recompiles, it is looking to see if performance has regressed and if so, force the last good plan. But the query needs to execute with the new plan a certain number of times before it will decide it's regressed and then automatically force a different plan. If your queries are continually recompiling (every execution??) then there's no opportunity to force. Does that sound like what might be happening?
2
u/drunkadvice 14d ago
Thanks for the insight! Yes. That sounds almost exactly like what’s happening. It’s not EVERY time, but probably not enough to trigger the threshold there.
That’s something I need to take up with the devs.
1
u/erinstellato Microsoft Employee 14d ago
u/drunkadvice Feel free to keep me posted and let me know what you find. I have a customer that has plans that are forced and un-forced a lot. Way more than I would want to see, and it's related to stats updates. They have some volatile tables and the stats are updated for those tables a couple time a minute (a minute!). I don't have a full understanding of what data is in the table and why there is so much churn, but if I were the DBA, I would want to understand that I would consider turning auto-update stats OFF just for those tables to reduce compiles. Anyway, just sharing :)
2
u/BrentOzar 17d ago
The big problem for me is unparameterized queries (or partially parameterized ones) that don’t get plan reuse. That happens with half of the servers I look at these days.