Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Memory Management


Memory Management

Author
Message
SQLDBAPerth
SQLDBAPerth
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 167
Thanks Jonathan - I think on average I learn something new for every 5 words you write when you respond to something.

I don't know if that's purely you being a good teacher or partly because I'm a dumbass.

I assume what you are talking about when saying look at the ring buffer entries is checking out the sys.dm_os_ring_buffers at the RING_BUFFER_RESOURCE_MONITOR type. If that is correct and I'm looking at the data correctly, the RESOURCE_MEMPHYSICAL_LOW events all have an IndicatorsSystem value of 0 and an IndicatorsProcess value of 2. Based on what you are saying is this suggesting that we are not on this server experiencing any issues with a grow/shrink cycle and that those events seem to be limited to individual processes needing more memory? In this case the server has only been up for 2 days now after installing the CU6 update so it might not be long enough for it to get to the point that memory becomes a problem.

Cheers,
Paul.
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 1807
It depends, and without a lot of added information I can't say for sure. You previously said that buffer pool was only using 51GB after the patch so you might not have ramped up completely or the workload just doesn't require all of the memory available in which case, this is all a wash. Your internal pressure or IsProcess = 2 for the low notifications are likely due to an ad hoc workload or plan caching issues. It could be something else, but this is most commonly the area that triggers internal memory pressure issues, and you might check your plan cache usage with the queries from Kimberly's blog post:

http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

If you find heavy single use plans with lots of wasted cache space, 'optimize for adhoc workloads' should be enabled to reduce the effects on the server. This is the most common problem I see with IsProcess = 2 but others could still exist that aren't related to plan caching.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
SQLDBAPerth
SQLDBAPerth
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 167
Optimise for ad-hoc is turned on on this server already - but there is still a lot of single use plans in the cache - presumably these have all been run twice so the second time the plan is persisted

CacheType  Total Plans  Total MBs    Avg Use Count  Total MBs - USE Count 1  Total Plans - USE Count 1
Proc 13721 3105.820312 31 842.914062 3282
Prepared 14232 1502.687500 13 569.960937 4502
Trigger 559 288.375000 12 91.898437 150
Adhoc 2972 100.975097 5 46.225097 2035
UsrTab 384 15.390625 56 0.523437 12
Check 14 0.484375 2 0.187500 5
View 1957 206.054687 3 0.000000 0



I'll keep an eye on the memory and if it looks like it's having issues then I'll tune back the max memory setting but for the time being I think it looks like it might be ok.

Thanks heaps for your advice Jonathan - very much appreciated.
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 1807
The 'optimize for adhoc workloads' sp_configure option only affects the adhoc cache, so all of the other caches will still keep the full plan for single use executions, which is where more of the memory is going, not directly to the adhoc cache. There is no option for minimizing caching of single use procs or prepared plans, so this may just be the nature of things for your workload.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search