SQL Server Portal

sys.dm_os_memory_cache_counters is one of the dynmaic management views shipped with SQL Server 2005. The major benefits of this dynamic management view is to provide the list of summary stating how cache is allocated against each request.

Given below is a simple query that will give the details about cache.

--This query is compatibale with SQL Server 2005 and 2008 only.
Select [name],[type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

A few days ago I tried using the same query in SQL Server 2012 to check out the summary of cache but the above query did not work. Given below is the error detail.

Select [name],[type]
,SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘single_pages_kb’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘multi_pages_kb’.

Oooops……………
It generated an error. The first impression I got was that these two fields have been eliminated from sys.dm_os_memory_cache_counters in SQL Server 2012 ?

But the answer is No, these field are not eliminated but renamed. Given below are the details.

Lets rename the column in the above query and execute it again in SQL Server 2012.

--This query is compatible with SQL Server 2012 and above.
Select [name],[type]
,SUM([pages_kb]) As [pages_kb]
,SUM([pages_in_use_kb]) As [pages_in_use_kb]
from sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Conclusion :
In SQL Server 2012, sys.dm_os_memory_cache_counters has renamed the two columns single_pages_kb, multi_pages_kb to pages_kb, pages_in_use_kb respectively. Remember to implement this renamed column change effects to avoid such errors.

Reference : MSDN