sys.dm_os_memory_clerks (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Returns the set of all memory clerks that are currently active 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_clerks. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
Column name | Data type | Description |
---|---|---|
memory_clerk_address | varbinary(8) | Specifies the unique memory address of the memory clerk. This is the primary key column. Is not nullable. |
type | nvarchar(60) | Specifies the type of memory clerk. Every clerk has a specific type, such as CLR Clerks MEMORYCLERK_SQLCLR. Is not nullable. |
name | nvarchar(256) | Specifies the internally assigned name of this memory clerk. A component can have several memory clerks of a specific type. A component might choose to use specific names to identify memory clerks of the same type. Is not nullable. |
memory_node_id | smallint | Specifies the ID of the memory node. Not nullable. |
single_pages_kb | bigint | Applies to: SQL Server 2008 (10.0.x) through SQL Server 2008 R2 (10.50.x). For more information, see Changes to Memory Management starting with SQL Server 2012 (11.x). |
pages_kb | bigint | Applies to: SQL Server 2012 (11.x) and later. Specifies the amount of page memory allocated in kilobytes (KB) for this memory clerk. Is not nullable. |
multi_pages_kb | bigint | Applies to: SQL Server 2008 (10.0.x) through SQL Server 2008 R2 (10.50.x). For more information, see Changes to Memory Management starting with SQL Server 2012 (11.x). Amount of multipage memory allocated in KB. This is the amount of memory allocated by using the multiple page allocator of the memory nodes. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes. Is not nullable. |
virtual_memory_reserved_kb | bigint | Specifies the amount of virtual memory that is reserved by a memory clerk. Is not nullable. |
virtual_memory_committed_kb | bigint | Specifies the amount of virtual memory that is committed by a memory clerk. The amount of committed memory should always be less than the amount of reserved memory. Is not nullable. |
awe_allocated_kb | bigint | Specifies the amount of memory in kilobytes (KB) locked in the physical memory and not paged out by the operating system. Is not nullable. |
shared_memory_reserved_kb | bigint | Specifies the amount of shared memory that is reserved by a memory clerk. The amount of memory reserved for use by shared memory and file mapping. Is not nullable. |
shared_memory_committed_kb | bigint | Specifies the amount of shared memory that is committed by the memory clerk. Is not nullable. |
page_size_in_bytes | bigint | Specifies the granularity of the page allocation for this memory clerk. Is not nullable. |
page_allocator_address | varbinary(8) | Specifies the address of the page allocator. This address is unique for a memory clerk and can be used in sys.dm_os_memory_objects to locate memory objects that are bound to this clerk. Is not nullable. |
host_address | varbinary(8) | Specifies the memory address of the host for this memory clerk. For more information, see sys.dm_os_hosts (Transact-SQL). Components, such as Microsoft SQL Server Native Client, access SQL Server memory resources through the host interface. 0x00000000 = Memory clerk belongs to SQL Server. Is not nullable. |
pdw_node_id | int | Applies to: Azure Synapse Analytics, Analytics Platform System (PDW) The identifier for the node that this distribution is on. |
On SQL Server, requires VIEW SERVER STATE
permission.
On Azure SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Microsoft Entra admin account is required. On all other Azure SQL Database service objectives, the VIEW DATABASE STATE
permission is required in the database.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects. These objects are used to allocate memory in an instance of SQL Server.
Memory nodes provide the interface and the implementation for low-level allocators. Inside SQL Server, only memory clerks have access to memory nodes. Memory clerks access memory node interfaces to allocate memory. Memory nodes also track the memory allocated by using the clerk for diagnostics. Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.
CACHESTORE and USERSTORE are memory clerks but function as actual caches. Typically, caches keep allocations until a cache removal policy releases those allocations. To avoid re-creating it, a cached allocation is retained in cache as long as possible and is ordinarily removed from the cache when it is too old to be useful, or when the memory space is needed for new information (for more information, see clock hand sweep). This is one of the two major controls for caches - lifetime control and visibility control.
Cache store and user store differ in the way they control lifetime of allocations. In the case of a cache store, the lifetime of entries is fully controlled by SQLOS's caching framework. With user store, entries lifetime is only partially controlled by a store. The implementation of each user store may be specific to the nature of memory allocations and therefore user stores participate in lifetime control of its entries.
Visibility control manages visibility of an entry. An entry in a cache can exist but might not be visible. For example, if a cache entry is marked for single use only, the entry will not be visible after it is used. In addition, the cache entry might be marked as dirty; it will continue to live in the cache but won't be visible to any lookups. For both stores, entry visibility is controlled by the caching framework.
For more information, see SQLOS Caching.
Object store is a simple pool. It is used to cache homogeneous data. All entries in the pools are considered equal. Object stores implement a maximum cap to control size relative to other caches.
For more information, see SQLOS Caching.
The following table lists the memory clerk types:
Type | Description |
---|---|
CACHESTORE_BROKERDSH | This cache store is used to store allocations by Service Broker Dialog Security Header Cache |
CACHESTORE_BROKERKEK | This cache store is used to store allocations by Service Broker Key Exchange Key Cache |
CACHESTORE_BROKERREADONLY | This cache store is used to store allocations by Service Broker Read Only Cache |
CACHESTORE_BROKERRSB | This cache store is used to store allocations by Service Broker Remote Service Binding Cache. |
CACHESTORE_BROKERTBLACS | This cache store is used to store allocations by Service Broker for security access structures. |
CACHESTORE_BROKERTO | This cache store is used to store allocations by Service Broker Transmission Object Cache |
CACHESTORE_BROKERUSERCERTLOOKUP | This cache store is used to store allocations by Service Broker user certificates lookup cache |
CACHESTORE_COLUMNSTOREOBJECTPOOL | This cache store is used for allocations by Columnstore Indexes for segments and dictionaries |
CACHESTORE_CONVPRI | This cache store is used to store allocations by Service Broker to keep track of Conversations priorities |
CACHESTORE_EVENTS | This cache store is used to store allocations by Service Broker Event Notifications |
CACHESTORE_FULLTEXTSTOPLIST | This memory clerk is used for allocations by Full-Text engine for stoplist functionality. |
CACHESTORE_NOTIF | This cache store is used for allocations by Query Notification functionality |
CACHESTORE_OBJCP | This cache store is used for caching objects with compiled plans (CP): stored procedures, functions, triggers. To illustrate, after a query plan for a stored procedure is created, its plan is stored in this cache. |
CACHESTORE_PHDR | This cache store is used for temporary memory caching during parsing for views, constraints, and defaults algebrizer trees during compilation of a query. Once query is parsed, the memory should be released. Some examples include: many statements in one batch - thousands of inserts or updates into one batch, a T-SQL batch that contains a large dynamically generated query, a large number of values in an IN clause. |
CACHESTORE_QDSRUNTIMESTATS | This cache store is used to cache Query Store runtime statistics |
CACHESTORE_SEARCHPROPERTYLIST | This cache store is used for allocations by Full-Text engine for Property List Cache |
CACHESTORE_SEHOBTCOLUMNATTRIBUTE | This cache store is used by storage engine for caching Heap or B-Tree (HoBT) column metadata structures. |
CACHESTORE_SQLCP | This cache store is used for caching ad hoc queries, prepared statements, and server-side cursors in plan cache. Ad hoc queries are commonly language-event T-SQL statements submitted to the server without explicit parameterization. Prepared statements also use this cache store - they are submitted by the application using API calls like SQLPrepare()/ SQLExecute (ODBC) or SqlCommand.Prepare/SqlCommand.ExecuteNonQuery (ADO.NET) and will appear on the server as sp_prepare/sp_execute or sp_prepexec system procedure executions. Also, server-side cursors would consume from this cache store (sp_cursoropen, sp_cursorfetch, sp_cursorclose). |
CACHESTORE_STACKFRAMES | This cache store is used for allocations of internal SQL OS structures related to stack frames. |
CACHESTORE_SYSTEMROWSET | This cache store is used for allocations of internal structures related to transaction logging and recovery. |
CACHESTORE_TEMPTABLES | This cache store is used for allocations related to temporary tables and table variables caching - part of plan cache. |
CACHESTORE_VIEWDEFINITIONS | This cache store is used for caching view definitions as part of query optimization. |
CACHESTORE_XML_SELECTIVE_DG | This cache store is used to cache XML structures for XML processing. |
CACHESTORE_XMLDBATTRIBUTE | This cache store is used to cache XML attribute structures for XML activity like XQuery. |
CACHESTORE_XMLDBELEMENT | This cache store is used to cache XML element structures for XML activity like XQuery. |
CACHESTORE_XMLDBTYPE | This cache store is used to cache XML structures for XML activity like XQuery. |
CACHESTORE_XPROC | This cache store is used for caching structures for Extended Stored procedures (Xprocs) in plan cache. |
MEMORYCLERK_BACKUP | This memory clerk is used for various allocations by Backup functionality |
MEMORYCLERK_BHF | This memory clerk is used for allocations for binary large objects (BLOB) management during query execution (Blob Handle support) |
MEMORYCLERK_BITMAP | This memory clerk is used for allocations by SQL OS functionality for bitmap filtering |
MEMORYCLERK_CSILOBCOMPRESSION | This memory clerk is used for allocations by Columnstore Index binary large objects (BLOB) Compression |
MEMORYCLERK_DRTLHEAP | This memory clerk is used for allocations by SQL OS functionality Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_EXPOOL | This memory clerk is used for allocations by SQL OS functionality Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_EXTERNAL_EXTRACTORS | This memory clerk is used for allocations by query execution engine for batch mode operations Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_FILETABLE | This memory clerk is used for various allocations by FileTables functionality. |
MEMORYCLERK_FSAGENT | This memory clerk is used for various allocations by FILESTREAM functionality. |
MEMORYCLERK_FSCHUNKER | This memory clerk is used for various allocations by FILESTREAM functionality for creating filestream chunks. |
MEMORYCLERK_FULLTEXT | This memory clerk is used for allocations by Full-Text engine structures. |
MEMORYCLERK_FULLTEXT_SHMEM | This memory clerk is used for allocations by Full-Text engine structures related to Shared memory connectivity with the Full Text Daemon process. |
MEMORYCLERK_HADR | This memory clerk is used for memory allocations by Always On functionality |
MEMORYCLERK_HOST | This memory clerk is used for allocations by SQL OS functionality. |
MEMORYCLERK_LANGSVC | This memory clerk is used for allocations by SQL T-SQL statements and commands (parser, algebrizer, etc.) |
MEMORYCLERK_LWC | This memory clerk is used for allocations by Full-Text Semantic Search engine |
MEMORYCLERK_POLYBASE | This memory clerk keeps track of memory allocations for PolyBase functionality inside SQL Server. |
MEMORYCLERK_QSRANGEPREFETCH | This memory clerk is used for allocations during query execution for query scan range prefetch. |
MEMORYCLERK_QUERYDISKSTORE | This memory clerk is used by Query Store memory allocations inside SQL Server. |
MEMORYCLERK_QUERYDISKSTORE_HASHMAP | This memory clerk is used by Query Store memory allocations inside SQL Server. |
MEMORYCLERK_QUERYDISKSTORE_STATS | This memory clerk is used by Query Store memory allocations inside SQL Server. |
MEMORYCLERK_QUERYPROFILE | This memory clerk is used for during server startup to enable query profiling Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_RTLHEAP | This memory clerk is used for allocations by SQL OS functionality. Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_SECURITYAPI | This memory clerk is used for allocations by SQL OS functionality. Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_SERIALIZATION | Internal use only |
MEMORYCLERK_SLOG | This memory clerk is used for allocations by sLog (secondary in-memory log stream) in Accelerated Database Recovery Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_SNI | This memory clerk allocates memory for the Server Network Interface (SNI) components. SNI manages connectivity and TDS packets for SQL Server |
MEMORYCLERK_SOSMEMMANAGER | This memory clerk allocates structures for SQLOS (SOS) thread scheduling and memory and I/O management.. |
MEMORYCLERK_SOSNODE | This memory clerk allocates structures for SQLOS (SOS) thread scheduling and memory and I/O management. |
MEMORYCLERK_SOSOS | This memory clerk allocates structures for SQLOS (SOS) thread scheduling and memory and I/O management.. |
MEMORYCLERK_SPATIAL | This memory clerk is used by Spatial Data components for memory allocations. |
MEMORYCLERK_SQLBUFFERPOOL | This memory clerk keeps track of commonly the largest memory consumer inside SQL Server - data and index pages. Buffer Pool or data cache keeps data and index pages loaded in memory to provide fast access to data. For more information, see Buffer Management. |
MEMORYCLERK_SQLCLR | This memory clerk is used for allocations by SQLCLR. |
MEMORYCLERK_SQLCLRASSEMBLY | This memory clerk is used for allocations for SQLCLR assemblies. |
MEMORYCLERK_SQLCONNECTIONPOOL | This memory clerk caches information on the server that the client application may need the server to keep track of. One example is an application that creates prepare handles via sp_prepexecrpc. The application should properly unprepare (close) those handles after execution. |
MEMORYCLERK_SQLEXTENSIBILITY | This memory clerk is used for allocations by the Extensibility Framework for running external Python or R scripts on SQL Server. Applies to: SQL Server 2019 (15.x) and later |
MEMORYCLERK_SQLGENERAL | This memory clerk could be used by multiple consumers inside SQL engine. Examples include replication memory, internal debugging/diagnostics, some SQL Server startup functionality, some SQL parser functionality, building system indexes, initialize global memory objects, Create OLEDB connection inside the server and Linked Server queries, Server-side Profiler tracing, creating showplan data, some security functionality, compilation of computed columns, memory for Parallelism structures, memory for some XML functionality |
MEMORYCLERK_SQLHTTP | Deprecated |
MEMORYCLERK_SQLLOGPOOL | This memory clerk is used by SQL Server Log Pool. Log Pool is a cache used to improve performance when reading the transaction log. Specifically it improves log cache utilization during multiple log reads, reduces disk I/O log reads and allows sharing of log scans. Primary consumers of log pool are Always On (Change Capture and Send), Redo Manager, Database Recovery - Analysis/Redo/Undo, Transaction Runtime Rollback, Replication/CDC, Backup/Restore. |
MEMORYCLERK_SQLOPTIMIZER | This memory clerk is used for memory allocations during different phases of compiling a query. Some uses include query optimization, index statistics manager, view definitions compilation, histogram generation. |
MEMORYCLERK_SQLQERESERVATIONS | This memory clerk is used for Memory Grant allocations, that is memory allocated to queries to perform sort and hash operations during query execution. For more information on Query Execution reservations (memory grants), see this blog |
MEMORYCLERK_SQLQUERYCOMPILE | This memory clerk is used by Query optimizer for allocating memory during query compiling. |
MEMORYCLERK_SQLQUERYEXEC | This memory clerk is used for allocations in the following areas: Batch mode processing, Parallel query execution, query execution context, spatial index tessellation, sort and hash operations (sort tables, hash tables), some DVM processing, update statistics execution |
MEMORYCLERK_SQLQUERYPLAN | This memory clerk is used for allocations by Heap page management, DBCC CHECKTABLE allocations, and sp_cursor* stored procedure allocations |
MEMORYCLERK_SQLSERVICEBROKER | This memory clerk is used by SQL Server Service Broker memory allocations. |
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT | This memory clerk is used by SQL Server Service Broker transport memory allocations. |
MEMORYCLERK_SQLSLO_OPERATIONS | This memory clerk is used to gather performance statistics Applies to: Azure SQL Database |
MEMORYCLERK_SQLSOAP | Deprecated |
MEMORYCLERK_SQLSOAPSESSIONSTORE | Deprecated |
MEMORYCLERK_SQLSTORENG | This memory clerk is used for allocations by multiple storage engine components. Examples of components include structures for database files, database snapshot replica file manager, deadlock monitor, DBTABLE structures, Log manager structures, some tempdb versioning structures, some server startup functionality, execution context for child threads in parallel queries. |
MEMORYCLERK_SQLTRACE | This memory clerk is used for server-side SQL Trace memory allocations. |
MEMORYCLERK_SQLUTILITIES | This memory clerk can be used by multiple allocators inside SQL Server. Examples include Backup and Restore, Log Shipping, Database Mirroring, DBCC commands, BCP code on the server side, some query parallelism work, Log Scan buffers. |
MEMORYCLERK_SQLXML | This memory clerk is used for memory allocations when performing XML operations. |
MEMORYCLERK_SQLXP | This memory clerk is used for memory allocations when calling SQL Server Extended Stored procedures. |
MEMORYCLERK_SVL | This memory clerk is used used for allocations of internal SQL OS structures |
MEMORYCLERK_TEST | Internal use only |
MEMORYCLERK_UNITTEST | Internal use only |
MEMORYCLERK_WRITEPAGERECORDER | This memory clerk is used for allocations by Write Page Recorder. |
MEMORYCLERK_XE | This memory clerk is used for Extended Events memory allocations |
MEMORYCLERK_XE_BUFFER | This memory clerk is used for Extended Events memory allocations |
MEMORYCLERK_XLOG_SERVER | This memory clerk is used for allocations by Xlog used for log file management in SQL Azure Database Applies to: Azure SQL Database |
MEMORYCLERK_XTP | This memory clerk is used for In-Memory OLTP memory allocations. |
OBJECTSTORE_LBSS | This object store is used to allocate temporary LOBs - variables, parameters, and intermediate results for expressions. An example that uses this store is table-valued parameters (TVP) . See the KB article 4468102 and KB article 4051359 for more information on fixes in this space. |
OBJECTSTORE_LOCK_MANAGER | This memory clerk keeps track of allocations made by the Lock Manager in SQL Server. |
OBJECTSTORE_SECAUDIT_EVENT_BUFFER | This object store is used for SQL Server Audit memory allocations. |
OBJECTSTORE_SERVICE_BROKER | This object store is used by Service Broker |
OBJECTSTORE_SNI_PACKET | This object store is used by Server Network Interface (SNI) components which manage connectivity |
OBJECTSTORE_XACT_CACHE | This object store is used to cache transactions information |
USERSTORE_DBMETADATA | This object store is used for metadata structures |
USERSTORE_OBJPERM | This store is used for structures keeping track of object security/permission |
USERSTORE_QDSSTMT | This cache store is used to cache Query Store statements |
USERSTORE_SCHEMAMGR | Schema manager cache stores different types of metadata information about the database objects in memory (e.g tables). A common user of this store could be the tempdb database with objects like tables, temp procedures, table variables, table-valued parameters, worktables, workfiles, version store. |
USERSTORE_SXC | This user store is used for allocations to store all RPC parameters. |
USERSTORE_TOKENPERM | TokenAndPermUserStore is a single SOS user store that keeps track of security entries for security context, login, user, permission, and audit. Multiple hash tables are allocated to store these objects. |
Note
Documentation uses the term B-tree generally in reference to indexes. In rowstore indexes, the Database Engine implements a B+ tree. This does not apply to columnstore indexes or indexes on memory-optimized tables. For more information, see the SQL Server and Azure SQL index architecture and design guide.
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
sys.dm_os_sys_info (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)