| title | sys.dm_os_memory_cache_hash_tables (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.dm_os_memory_cache_hash_tables (Transact-SQL) | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 02/27/2023 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]
Returns a row for each active cache in the instance of [!INCLUDEssNoVersion].
Note
To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_memory_cache_hash_tables. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| 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: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] |
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)