sys.dm_os_memory_cache_hash_tables (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

Note

To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_memory_cache_hash_tables.

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 SQL Data Warehouse, Parallel Data Warehouse

Permissions

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

See Also

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