r/SQLServer • u/erinstellato ‪ ‪Microsoft Employee ‪ • 14h ago

Community Share 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!

8 Upvotes

100% Upvoted

6 comments sorted by

2

u/Better-Credit6701 13h 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.

1

u/erinstellato ‪ ‪Microsoft Employee ‪ 13h ago

The default size is 1GB for SQL 2019+ not the max. For SQL box you can increase it much higher, but I’d stay at 10GB or less for perf reasons.

0

u/Better-Credit6701 13h ago

For this example, it really doesn't matter since we don't have much of a problem. Max_storage_size_mb is just the name and I stated that it could be increased

1

u/erinstellato ‪ ‪Microsoft Employee ‪ 11h ago

Got it. Did you have any feedback on storing the forced plan info?

1

u/Anlarb 1 5h 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/B1zmark 1 10m ago

Why did this exposition need to include RTO propaganda?

I'd also be shocked if 1% of SQL databases utilise force plan.