select
cache_address,
name,
type as plan_cache_store,
table_level,
buckets_count,
buckets_in_use_count,
buckets_min_length,
buckets_max_length,
buckets_avg_length,
buckets_max_length_ever,
hits_count,
misses_count,
buckets_avg_scan_hit_length,
buckets_avg_scan_miss_length
from
sys.dm_os_memory_cache_hash_tables
order by
cache_address;
Combinations of name and type
select
name,
type
-- count(*) cnt
from
sys.dm_os_memory_cache_hash_tables
group by
name,
type
order by
name,
type;
--
-- name type
-- -------------------------------------------------------------------------------------------------------------------------
-- Bound Trees CACHESTORE_PHDR
-- Broker dormant rowsets CACHESTORE_BROKERTBLACS
-- ClrProcCache CACHESTORE_CLRPROC
-- ConversationPriorityCache CACHESTORE_CONVPRI
-- EventNotificationCache CACHESTORE_EVENTS
-- Extended Stored Procedures CACHESTORE_XPROC
-- FTSTOPLIST_CACHESTORE CACHESTORE_FULLTEXTSTOPLIST
-- Notification Store CACHESTORE_NOTIF
-- Object Plans CACHESTORE_OBJCP
-- QDSRuntimeStatsManager CACHESTORE_QDSRUNTIMESTATS
-- SEARCH_PROPERTY_LIST_CACHESTORE CACHESTORE_SEARCHPROPERTYLIST
-- Service broker configuration CACHESTORE_BROKERREADONLY
-- Service broker dialog cache CACHESTORE_BROKERREADONLY
-- Service Broker Dialog Security Header Cache CACHESTORE_BROKERDSH
-- Service Broker Key Exchange Key Cache CACHESTORE_BROKERKEK
-- Service broker mapping table CACHESTORE_BROKERREADONLY
-- Service Broker Null Remote Service Binding Cache CACHESTORE_BROKERRSB
-- Service broker routing cache CACHESTORE_BROKERREADONLY
-- Service Broker Transmission Object Cache CACHESTORE_BROKERTO
-- Service Broker user certificates lookup result cache CACHESTORE_BROKERUSERCERTLOOKUP
-- SESHAREDCOLMETADATACACHE CACHESTORE_SEHOBTCOLUMNATTRIBUTE
-- SOS_StackFramesStore CACHESTORE_STACKFRAMES
-- SQL Plans CACHESTORE_SQLCP
-- SystemRowsetStore CACHESTORE_SYSTEMROWSET
-- Temporary Tables & Table Variables CACHESTORE_TEMPTABLES
-- TokenAndPermUserStore USERSTORE_TOKENPERM
-- View Definition Cache CACHESTORE_VIEWDEFINITIONS
-- XMLDBCACHE CACHESTORE_XML_SELECTIVE_DG
-- XMLDBCACHE CACHESTORE_XMLDBATTRIBUTE
-- XMLDBCACHE CACHESTORE_XMLDBELEMENT
-- XMLDBCACHE CACHESTORE_XMLDBTYPE