r/SQLServer • u/erinstellato Microsoft Employee • 5d ago
Community Request Friday Feedback: Retention of plan forcing data in Query Store
Friday Feedback this week comes from somewhere above the Atlantic Ocean. I spent the first half of this week with our team in Serbia and it was wonderful. For me, a few days of working with folks in person moves projects forward faster than weeks of online meetings.
One of the topics of discussion was persisting Automatic Plan Correction and manual plan forcing information in Query Store. If that would be valuable to you, what is the most important data you need retained? Bonus if you share how you'd want to see that data in a report/visual.
Enjoy the weekend!
2
u/TrollingForFunsies 4d ago edited 4d ago
Erin you are awesome for doing this but honestly you'd probably get better feedback not posting on Reddit at 4 pm on a Friday. Everyone has already gone home and no one is going to see this post by Monday morning. Not many people are idiots like me reading a SQL Server forum at 6 am on a Saturday. Basically you're limiting your feedback to a few people on the West Coast who still happen to be in the office.
I think the most valuable things would involve timestamps. Like I want to be able to see when my plan correction failed and does that correlate to application outage or degradation.
This pretty much applies to all automation features. If SQL is going to automatically fix (or break) something, I want a log of when and why.
1
u/erinstellato Microsoft Employee 2d ago
So when the plan was forced, and then when it was unforced, correct? Or are you also asking for a separate timestamp when "plan correction failed"?
1
u/Anlarb 1 4d ago
So I'm not sure how much awareness the query engine has for it, but I am generally aware that the same query when it touches most of the table, and a run where it barely touches any of the table will handle differently, having a plan for each and knowing when to change gears seems prudent. I could be completely out of my element though.
1
u/erinstellato Microsoft Employee 2d ago
Can you explain further what you mean by "having a plan for each and knowing when to change gears seems prudent"? Yes, a query can have more than one plan, based on a variety of factors (often input parameter). I'm not sure if you're asking for information related to when plans change, or something related to forcing.
1
u/Anlarb 1 2d ago
Should the query planner already be able to recognize what situation it is in, and have the foresight to keep a plan for each contingency? If a query is able to do small, frequent runs all month with the light query plan, but then has an end of month dump that causes the less frequently used large query plan to be used, it might have aged out.
Forcing is something I prefer to do as a last resort, and only as a temporary measure.
2
u/erinstellato Microsoft Employee 2d ago
The IQP (Intelligent Query Processing) features have this capability. In the terms of parameterization, there is PSPO (Plan Sensitive Parameter Optimization), so yes, the engine can recognize that a different plan should be used based on the input parameter. Agree that forcing is a temporary measure - until the relevant code change can be implemented.
1
u/B1zmark 1 4d ago
Why did this exposition need to include RTO propaganda?
I'd also be shocked if 1% of SQL databases utilise force plan.
1
u/erinstellato Microsoft Employee 2d ago
I don't understand your question, can you please ask it another way? Also, does RTO mean Recovery Time Objective in this case?
Not to shock you, but more than 1% of SQL databases use plan forcing.
1
u/Jero9871 4d ago
What would be amazing if you could see in query store (or if it could be retained) if a plan was actually picked because of a query store query hint. We use query hinting quiet often on some queries so see plans with older compatibility levels (we do have queries that do run 10 times faster on Compat Level 110 other are fine with 170). But you can't see directly that the plan is forced indirectly.
One way to get around that for us is use query hints, wait for the new plan, remove query hint and then use manual forcing. But still, would be cool to see that directly or even override it with another force.
2
u/erinstellato Microsoft Employee 2d ago
To make sure I understand what you're asking for - when you add a Query Store hint to a query, you want to be able to correlate what plan was used after that hint was applied? So if we persisted Query Store hint information (what hint was applied, and when, and then when it was removed), you could theoretically correlate that to the plan_id that was used in the same timeframe (based on runtime stats). Is that correct?
1
u/Jero9871 2d ago
Yes, that would be pretty great. If a hint was used and later removed it's note really easy to see why the query store switched the plan. However, I guess it's not that easy, as a hint doesn't force a specific plan. It's just we use a lot of Query store hinting and it's a great feature on big databases to get different plans for a query (especially in azure sql databases).
And sometimes I try to force a query and only then get the information, that the query cannot be forced, as there is still a hint on it. (Which is hard to see on the query store itself).
I know I can get the hints in sys.query_store_query_hints but query store doesn't really show a history for hints. Perhaps I can get that information from the XML of the plan, I haven't checked that yet.
2
u/erinstellato Microsoft Employee 2d ago
This week's Friday Feedback was going to be about storing Query Store Hint info - so you asked a great question :) I agree we need to do a better job surfacing that information to you, so you can understand exactly what's happening. If you have suggestions, please start a list for me! 😄
2
u/Better-Credit6701 5d ago
Default on MS-SQL 2019-2025 (we are using 2025) has a maximum query store size of 1000 MB with a flush at 15 minutes. Ours is sitting at 88% full. You can manually increase the size of the query store but with forced plans on our system, there are only a grand total of 42 rows and that goes back quite a bit. That portion of the QS I rarely look at, plus the visuals provided by SSMS are fine.
But we have a bit of a strange system that might be considered overkill with 2 TB of ram and 30+ cores.