| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_os_memory_cache_counters (Transact-SQL) |
sys.dm_os_memory_cache_counters (Transact-SQL) |
rwestMSFT |
randolphwest |
02/27/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016||>=sql-server-linux-2017||>=aps-pdw-2016||=azure-sqldw-latest |
[!INCLUDE sql-asa-pdw]
Returns a snapshot of the health of a cache in [!INCLUDEssNoVersion]. 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 [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_memory_cache_counters. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| Column name | Data type | Description |
|---|---|---|
| cache_address | varbinary(8) | Indicates the address (primary key) of the counters associated with a specific cache. Is not nullable. |
| name | nvarchar(256) | Specifies the name of the cache. Is not nullable. |
| type | nvarchar(60) | Indicates the type of cache that is associated with this entry. Is not nullable. |
| single_pages_kb | bigint | Applies to: [!INCLUDEsql2008-md] through [!INCLUDEsql2008r2]. 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_kb | bigint | Applies to: [!INCLUDEssSQL11] and later. Specifies the amount, in kilobytes, of the memory allocated in the cache. Is not nullable. |
| multi_pages_kb | bigint | Applies to: [!INCLUDEsql2008-md] through [!INCLUDEsql2008r2]. 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_kb | bigint | Applies to: [!INCLUDEssSQL11] and later. 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_kb | bigint | Applies to: [!INCLUDEsql2008-md] through [!INCLUDEsql2008r2]. 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_kb | bigint | Applies to: [!INCLUDEsql2008-md] through [!INCLUDEsql2008r2]. 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_count | bigint | Indicates the number of entries in the cache. Is not nullable. |
| entries_in_use_count | bigint | Indicates the number of entries in the cache that is being used. Is not nullable. |
| pdw_node_id | int | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)