May 24, 2023 at 7:42 am
Hi
we have a sql server we use as a data warehouse. It has 1TB of RAM but still suffers from memory pressure. Looking at the plans there are excessive memory grants. I changed the compatibility level to 150 to take advantage of the Adaptive Memory Grants.
It's been in place for around a month now but all the plans with IsMemoryGrantFeedbackAdjusted have a value of No: First Execution
I guess this is because most of the large queries only run daily and they've been aged out by the time they run again. I know this has been resolved using the query store (I think) in 2022, but an upgrade is not on the horizon.
Can anyone suggest another way of keeping the plans long enough to have their memory adapted!
Cheers
Alex
May 24, 2023 at 11:40 am
For the feedback to work, there has to more than one execution. No other possibility exists. There's not a switch or a setting that will affect this. You could look to Query Store, run the plan a second time manually, evaluate if you got the correct feedback, then choose that plan and force it.
HOWEVER...
Part of the feedback process is to adjust the memory up, or down, on the fly. So forcing a larger, or smaller, memory allocation may work some of the time, but it won't work all the time. You're probably better off letting it run as is. But, testing is your buddy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2023 at 12:19 pm
Thanks Grant, is there a way to configure SQL to make it more likely that the plans will remain in the cache from day to day
May 24, 2023 at 1:10 pm
Not really. More memory. Parameterization so that queries aren't ad hoc. Optimize for ad hoc so plan stubs are used for the one-off queries, reducing memory use, increasing the chances of plans getting more than one execution over time.
Plans will remain in cache forever unless acted upon by an outside force (Newton's law of plan cache?). So they're not seeing reuse because memory pressure is finding the plans that aren't being used and removing them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2024 at 3:49 pm
Addressing memory pressure in your data warehouse is crucial. Since an upgrade is not an immediate option, consider employing Plan Guides to enforce stable memory grants for specific queries. Additionally, exploring Plan Freezing may help retain adapted plans for longer periods like nsfas , mitigating the impact of daily executed large queries. This could provide a workaround until a more comprehensive solution becomes feasible.
January 25, 2024 at 3:52 pm
oops posted 2 times due to internet issue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply