sys.dm_os_memory_clerks (Transact-SQL)

Cikk
03/21/2023

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.

Permissions

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.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

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

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.

OBJECTSTORE

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.

Types

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.

Megosztás a következőn keresztül: