October 4, 2017 at 5:12 pm
Hi All,
So we got alerted that the sql server was using 90% memory.
I think the only fix is to reduce max sql server memory , I reduced it and then the sql server was using 80% memory which is good.
What is the effect of reducing max serve memory on the fly? Does it make sql server recreate the cached plans and store them in memory thereby reducing performance and causing time outs?
Thanks
October 5, 2017 at 2:50 am
sqlguy80 - Wednesday, October 4, 2017 5:12 PMHi All,
So we got alerted that the sql server was using 90% memory.
Normal, expected, desired behaviour.
I think the only fix is to reduce max sql server memory , I reduced it and then the sql server was using 80% memory which is good.
Not a fix, since high memory usage is desired. And desiring 20% of the server's memory idle is weird. Do you also want 20% of the desks in the office permanently empty, 20% of shelf space empty?
What is the effect of reducing max serve memory on the fly? Does it make sql server recreate the cached plans and store them in memory thereby reducing performance and causing time outs?
Less data cached in the data cache, fewer plans cached in the plan cache, overall poorer performance (though usually only slightly)
The only time when lowering max server memory is indicated is when there's less than about 1MB in overall available memory (after SQL and OS have taken what they need)
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply