Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Memory Management Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 8:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:28 PM
Points: 157, Visits: 158
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.
Post #1514564
Posted Thursday, November 14, 2013 9:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1514571
Posted Thursday, November 14, 2013 9:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:28 PM
Points: 157, Visits: 158
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.
Post #1514578
Posted Thursday, November 14, 2013 9:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1514579
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse