Skip to content

Latest commit

 

History

History
60 lines (50 loc) · 5.04 KB

sys-dm-os-memory-cache-entries-transact-sql.md

File metadata and controls

60 lines (50 loc) · 5.04 KB
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_entries (Transact-SQL)
sys.dm_os_memory_cache_entries returns information about all entries in caches in SQL Server.
rwestMSFT
randolphwest
01/11/2024
sql
system-objects
reference
dm_os_memory_cache_entries
sys.dm_os_memory_cache_entries
dm_os_memory_cache_entries_TSQL
sys.dm_os_memory_cache_entries_TSQL
sys.dm_os_memory_cache_entries dynamic management view
TSQL
>=sql-server-2016 || >=sql-server-linux-2017 || >=aps-pdw-2016 || =azure-sqldw-latest

sys.dm_os_memory_cache_entries (Transact-SQL)

[!INCLUDE sql-asa-pdw]

Returns information about all entries in caches in [!INCLUDE ssNoVersion]. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache entries.

Note

To call this from [!INCLUDE ssazuresynapse-md] or [!INCLUDE ssPDW], use the name sys.dm_pdw_nodes_os_memory_cache_entries. [!INCLUDE synapse-analytics-od-unsupported-syntax]

Column name Data type Description
cache_address varbinary(8) Address of the cache. Not nullable.
name nvarchar(256) Name of the cache. Not nullable.
type varchar(60) Type of cache. Not nullable.
entry_address varbinary(8) Address of the descriptor of the cache entry. Not nullable.
entry_data_address varbinary(8) Address of the user data in the cache entry.

0x00000000 = Entry data address isn't available.

Not nullable.
in_use_count int Number of concurrent users of this cache entry. Not nullable.
is_dirty bit Indicates whether this cache entry is marked for removal. 1 = marked for removal. Not nullable.
disk_ios_count int Number of I/Os incurred while this entry was created. Not nullable.
context_switches_count int Number of context switches incurred while this entry was created. Not nullable.
original_cost int Original cost of the entry. This value is an approximation of the number of I/Os incurred, CPU instruction cost, and the amount of memory consumed by entry. The greater the cost, the lower the chance that the item will be removed from the cache. Not nullable.
current_cost int Current cost of the cache entry. This value is updated during the process of entry purging. Current cost is reset to its original value on entry reuse. Not nullable.
memory_object_address varbinary(8) Address of the associated memory object. Nullable.
pages_allocated_count bigint Applies to: [!INCLUDE sql2008-md] through [!INCLUDE sql2008r2].

Number of 8-KB pages to store this cache entry. Not nullable.
pages_kb bigint Applies to: [!INCLUDE ssSQL11] and later versions.

Amount of memory in kilobytes (KB) used by this cache entry. Not nullable.
entry_data nvarchar(2048) Serialized representation of the cached entry. This information is cache store dependant. Nullable.
pool_id int Applies to: [!INCLUDE sql2008r2] and later versions.

Resource pool ID associated with entry. Nullable.
pdw_node_id int Applies to: [!INCLUDE ssazuresynapse-md], [!INCLUDE ssPDW]

The identifier for the node that this distribution is on.

Permissions

For [!INCLUDE ssNoVersion_md] and [!INCLUDE ssazuremi-md], you require VIEW SERVER STATE permission.

[!INCLUDE sssql22-md] and later versions, you require VIEW SERVER PERFORMANCE STATE permission on the server.

On [!INCLUDE ssazure-sqldb] 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.

Related content