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)