August 6, 2012 at 10:30 am
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!
August 6, 2012 at 10:42 am
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.
August 7, 2012 at 5:42 am
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....:ermm:
August 8, 2012 at 8:24 pm
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 on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply