SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A DMV a Day – Day 25

The DMV for Day 25 is sys.dm_os_memory_cache_counters, which is described by BOL as:

Returns a snapshot of the health of a cache. sys.dm_os_memory_cache_counters provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.

The column single_pages_kb is the amount of memory allocated by using the single-page allocator. This refers to the 8-KB pages that are taken directly from the buffer pool for this cache. The column multi_pages_kb is the amount of memory allocated by using the multiple-page allocator of the memory node. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes.  This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Look at the number of items in different parts of the cache
SELECT name, [type], entries_count, single_pages_kb, 
single_pages_in_use_kb, multi_pages_kb, multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
ORDER BY multi_pages_kb DESC;

This query has information on the single and multi-page allocations made for the SQLCP and OBJCP cache stores. A large number of multi_pages_kb for either of these cache types can lead to decreased performance on builds previous to SQL Server 2005 SP2 (Build 3042).  Since SQL Server 2005 SP2 is no longer a supported service pack, this is yet another reason to get SQL Server 2005 SP3 (Build 4035), and hopefully SQL Server 2005 SP3 CU9 (Build 4294) applied.


Posted by Anonymous on 25 April 2010

Pingback from  Dew Drop – April 25, 2010 | Alvin Ashcraft's Morning Dew

Posted by Dukagjin Maloku on 26 April 2010

With this script I have short & nice info. Thanks!

Posted by Dan Guzman on 26 April 2010

What is your definition of 'A large number of multi_pages_kb for either of these cache types'?  How many is large?  hundreds?  thousands?  millions?

Posted by Glenn Berry on 26 April 2010


That would depend on how much physical memory you have, what you have set for MaxServerMemory, and whether you are running x86 or x64. Multi_pages_kb is allocated ouside of the buffer pool (so it uses a chunk of the memory that is left over after the buffer pool limit set by MaxServerMemory). If I saw CACHESTORE_SQLCP using more than a fairly small percentage of the non-buffer pool memory, I would be investigating what was going on.

Posted by Anonymous on 27 April 2010

Pingback from  Setting up a wireless network with a Power book, PC and USB Printer? | Cheap printers

Posted by Jason Brimhall on 27 April 2010

Thanks Glenn for the clarification on this script.

Leave a Comment

Please register or log in to leave a comment.