sys.dm_os_memory_cache_hash_tables (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Returns a row for each active cache in the instance of SQL Server.

Note

To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_memory_cache_hash_tables. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Column name Data type Description
cache_address varbinary(8) Address (primary key) of the cache entry. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache. Is not nullable.
table_level int Hash table number. A particular cache may have multiple hash tables that correspond to different hash functions. Is not nullable.
buckets_count int Number of buckets in the hash table. Is not nullable.
buckets_in_use_count int Number of buckets that are currently being used. Is not nullable.
buckets_min_length int Minimum number of cache entries in a bucket. Is not nullable.
buckets_max_length int Maximum number of cache entries in a bucket. Is not nullable.
buckets_avg_length int Average number of cache entries in each bucket. Is not nullable.
buckets_max_length_ever int Maximum number of cached entries in a hash bucket for this hash table since the server was started. Is not nullable.
hits_count bigint Number of cache hits. Is not nullable.
misses_count bigint Number of cache misses. Is not nullable.
buckets_avg_scan_hit_length int Average number of examined entries in a bucket before the searched for an item was found. Is not nullable.
buckets_avg_scan_miss_length int Average number of examined entries in a bucket before the search ended unsuccessfully. Is not nullable.
pdw_node_id int The identifier for the node that this distribution is on.

Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)

Permissions

On SQL Server 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.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

See also

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