August 4, 2017 at 9:16 am
Hi,
I got alerted that on one of the prod SQL servers, sqlserver.exe was using up 95% memory. Theoretically it a good thing, so that SQL Server can store exec plans in the cache and they will be faster the 2nd time those sprocs are executed.
But if I reduce the max server memory in SQL Server to reduce the over all utilization say from 95% to 85% , then what is the effect on the queries etc? Is it like or equivalent of running DBCC FREEPROCCACHE?
Thanks in Advance.
August 4, 2017 at 9:27 am
Changing max server memory does flush the plan cache, so I'd recommend that you do it at a quiet time.
Drop it in small amounts.
The majority of SQL's memory usually goes to the data cache, not the plan cache. Plan cache will likely be the 2nd largest memory consumer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2017 at 5:30 am
be aware that the max server memory in 2012 onwards encompasses a lot more than the buffer pool and proc cache.
How much memory does the server have?
is sql server the only application running on this server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply