http://www.sqlservercentral.com/blogs/glennberry/2010/04/25/a-dmv-a-day-_1320_-day-25/

Printed 2014/10/02 01:47AM

A DMV a Day – Day 25

By GlennBerry, 2010/04/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
WHERE [type] = 'CACHESTORE_SQLCP' 
OR [type] = 'CACHESTORE_OBJCP'
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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.