sys.dm_os_memory_cache_counters – Day 23 – One DMV a Day

Hello Geeks and welcome to the Day 23 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have covered sys.dm_os_memory_pools. While pools talk about the memory pools available for use by clerks, the DMV I am covering today will talk about the caches of memory for different objects. Sys.dm_os_memory_cache_counters exposes information which help you understand the cache size and usage. It will help you answer few questions like how big is my plan cache.

As many other DMVs related to memory sys.dm_os_memory_cache_counters is also an independent one. This give you a row for each cache. It gives you the information like memory allocated (pages_kb) and memory used (pages_in_use_kb). This also helps you see any memory pressure related to a particular cache.

Caches can compete for memory and can lead to flushing other caches. This is internal Memory pressure and is handled by SQL OS internally. You may see certain performance degradation when this occurs. SQL Server has hard limit on all caches and soft limit on individual cache. Each cache will grow as required using stepping algorithm. The hard limit changes from version to version and memory available. The soft limit is limited to 62.5% of all cache. This means any cache can grow max up to 62.5% of the total cache limit (hard limit).

(Image curtsey: Google; Idea curtsey: Bob Ward)

To understand how critical this issue can be there is a bug on SQL 2012 RTM. When you set AlwaysON on a high transaction processing machine the complete memory gets filled up and eventually you SQL instance crashes. You will see messages like below in your error log.

Failed allocate pages: FAIL_PAGE_ALLOCATION 8
Error: 701, Severity: 17, State: 107.
There is insufficient system memory in resource pool ‘default’ to run this query.

To troubleshoot above issue you can check which cache is taking up more memory using sys.dm_os_memory_cache_counters. In this case you would observe MEMORYCLERK_SQLLOGPOOL taking up more memory from sys.dm_os_memory_clerks. This clerk is used internally for transaction log activity. This issue is fixed in CU5 for RTM and CU2 for SP1 (Ref: KB 2769594)

/***** SQL Server 2005 through 2008 R2 *****/
SELECT name,
	type,
	SUM(single_pages_kb + multi_pages_kb) AS Size,
	SUM(single_pages_in_use_kb + multi_pages_in_use_kb) AS Used_Size,
	SUM(entries_count) AS Entries,
	SUM(entries_in_use_count) AS Used_Entries
FROM sys.dm_os_memory_cache_counters
GROUP BY name, type
ORDER BY 4 DESC

/***** SQL Server 2012 or later versions *****/
SELECT name,
	type,
	SUM(pages_kb) AS Size,
	SUM(pages_in_use_kb) AS Used_Size,
	SUM(entries_count) AS Entries,
	SUM(entries_in_use_count) AS Used_Entries
FROM sys.dm_os_memory_cache_counters
GROUP BY name, type
ORDER BY 4 DESC

Sys.dm_os_memory_cache_counters can be used as below. There is small change in the columns from 2008 R2 to 2012. As I have already mentioned in my previous blogs in this series, the memory architecture is changed in 2012. The below queries can be run based on the respective version.

One thing to observe is the USERSTORE_xxxx type objects’ used space will not be tracked by sys.dm_os_memory_cache_counters.

Tomorrow I will be covering another memory related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook