sys.dm_os_memory_cache_counters (Transact-SQL)

 

Updated: August 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a snapshot of the health of a cache in SQL Server. 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.

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_memory_cache_counters.

Column nameData typeDescription
cache_addressvarbinary(8)Indicates the address (primary key) of the counters associated with a specific cache. Is not nullable.
namenvarchar(256)Specifies the name of the cache. Is not nullable.
typenvarchar(60)Indicates the type of cache that is associated with this entry. Is not nullable.
single_pages_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount, in kilobytes, of the single-page memory allocated. This 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. Is not nullable.
pages_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the amount, in kilobytes, of the memory allocated in the cache. Is not nullable.
multi_pages_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount, in kilobytes, of the multipage memory allocated. This 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. Is not nullable.
pages_in_use_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the amount, in kilobytes, of the memory that is allocated and in use in the cache. Is nullable. Values for objects of type USERSTORE_<*> are not tracked. NULL is reported for them.
single_pages_in_use_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount, in kilobytes, of the single-page memory that is being used. Is nullable. This information is not tracked for objects of type USERSTORE_<*> and these values will be NULL.
multi_pages_in_use_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount, in kilobytes, of the multipage memory that is being used. NULLABLE. This information is not tracked for objects of type USERSTORE_<*>, and these values will be NULL.
entries_countbigintIndicates the number of entries in the cache. Is not nullable.
entries_in_use_countbigintIndicates the number of entries in the cache that is being used. Is not nullable.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show: