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

Memory Consumption -- CACHESTORE_SQLCP and MEMORYCLERK_SQLBUFFERPOOL Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 10:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:49 AM
Points: 124, Visits: 400
We have SQL Server 2008 box with 16GB physical memory. Maximum server memory in SQL Server has been set to 12.5GB. I have recently run of the standard reports -- "Memory Consumption" and I found the Top Memory Consuming Components graph are showing most of the memory are used by CACHESTORE_SQLCP (3100000KB).

When I check at sys.dm_os_memory_clerks using

select * from sys.dm_os_memory_clerks
ORDER by single_pages_kb desc

I found the CACHESTORE_SQLCP in "type" column is showing on the top of the results. While MEMORYCLERK_SQLBUFFERPOOL (in "type" column) is down at the bottom with single_pages_kb= 0 and virtual_memory_reserved_kb = 12615680 and virtual_memory_commited_kb = 8867072.

Could anybody please explain to me
Why "Memory Consumption" report dosen't show MEMORYCLERK_SQLBUFFERPOOL usage?
Why MEMORYCLERK_SQLBUFFERPOOL is only showing the value in virtual_memory_reserved_kb column instead of single_pages_kb column?
Does it mean MEMORYCLERK_SQLBUFFERPOO is using 12615680 kb physical memory?
Many thanks!

Any relevant reading material will also be highly appreciated! Thanks!
Post #1340744
Posted Monday, August 6, 2012 10:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
I'd recommend reading the BOL article on this DMV first:

http://msdn.microsoft.com/en-us/library/ms175019(v=sql.105).aspx

single_pages_kb is one specific type of memory allocation that's actually part of the buffer pool anyway, so you won't see any use of it for the buffer pool itself.

Virtual_memory_committed_kb is probably the most useful column to order by if you're looking for what's using all the memory...

Virtual memory's not always directly related to physical memory, but assuming pages are locked in memory then yes.

Bear in mind that SQL Server won't de-allocate memory it's already taken if it doesn't need to, so it's normal that it will rise to it's maximum value after a period of time and doesn't necessarily mean it's under memory pressure.
Post #1340753
Posted Tuesday, August 7, 2012 5:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:49 AM
Points: 124, Visits: 400
Thanks, HowardW.

But it seems that "Memory Consumption" report (standard report in SSMS) only shows memory allocation for single_pages_kb type, but no MEMORYCLERK_SQLBUFFERPOO. Why is that....

Post #1341169
Posted Wednesday, August 8, 2012 8:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 4,472, Visits: 6,402
You may have plan cache bloat. Glenn Alan Berry has some Diagnostic scripts for 2008 that can show you single use plans and lots of other stuff. I would check for those first, and decide how you want to proceed. If you have lots of dynamic sql coming in (from ORM possibly??), you can deal with that with a variety of approaches such as optimize for ad hoc workloads setting or even dbcc freeproccache. I have clients I had to set up jobs to do that on sql 2005 as frequently as every 30 minutes to aleviate memory pressure from single user ad hoc plans.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1342349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse