Dynamic Management Views

Returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit. (Database Engine) ID of the audit action.

Returns a table that lists securable classes that can be mapped to the audit log. , see The class type of the entity that was audited.

Returns a row for each stored procedure activated by Service Broker.

Returns a row for each Service Broker network connection. The following table provides more Identifier of the SQL Server Network Interface (SNI) connection used by this connection for TCP/IP communications.

Returns a row for each Service Broker message that an instance of SQL Server is in the process ID of the conversation to which this message Indicates whether this message is from the initiator of the conversation.

Returns a row for each queue monitor in the instance. A queue monitor manages activation for Object identifier for the database that contains the queue that the monitor watches. Object identifier for the queue that the monitor State of the monitor. This value is one of the Last time that this queue monitor activated a stored Number of sessions that are currently waiting within This value includes

2022 (16.x) and later versions Mirrored databases in Microsoft SQL database in Microsoft Fabric Returns activity from the SQL change feed.

Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR. The hosted CLR is initialized by executing any CLR routine, type, or trigger. The whether execution of user CLR code has been enabled on the server. Execution of user CLR columns. Each row in this view provides details about a property of the hosted CL

Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task.

2019 (15.x) and later - Windows only Returns performance counters for the secure enclave for Always Encrypted. For more Always Encrypted with secure enclaves If the enclave is configured and has been correctly initialized after the last restart of SQL Server, the view contains exactly one row.

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns counts of different types of object memory pool usage for columnstore index objects.

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following options: The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time. To protect the key material of the symmetric key, SQL Server and Azure SQL

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns current row-level I/O, locking, and access method activity for compressed rowgroups length of time a user query must wait to read or write to a compressed rowgroup or partition of a columnstore index, and identify rowgroups that are encountering significant I/O activity or In-memory columnstore indexes don't appear

Returns space usage information for each data file in the database. In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a : SQL Server 2012 (11.x) and later versions. : SQL Server 2012 (11.x) and later versions. Total number of pages in the data file. : SQL Server 2012 (11.x) and later versions.

Returns a row for each full-text or semantic index in each table that has an associated full-text Object ID of the table that contains the index. Logical size of the extraction in number of index pages. Logical size of the extraction in number of index pages. Logical size of the extraction in number of index pages. Manage and Monitor Semantic Search For information

Returns the lower level data access, locking, and latching statistics for each partition of a table . Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this Specify NULL to return information for all databases in the instance of SQL Server.

Returns size and fragmentation information for the data and indexes of the specified table or view in the SQL Server Database Engine. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the each partition. For large object (LOB) data, one row is returned for the of each partition.

Returns counts of different types of index operations and the time each type of operation was In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn't belong to the c

2016 (13.x) SP2 and later versions SQL database in Microsoft Fabric information of the transaction log. Note all transaction log files are combined in the table output. Each row in the output represents a VLF in the transaction log and provides information relevant to that VLF in the log. Valid inputs are the ID number of a database, NULL, or DEFAULT. The default is NULL. NULL and DEF

Returns space usage information for the transaction log. In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a The occupied size of the log as a percent of the total The amount of space used since the last log backup SQL Server 2014 (12.x) and later versions, SQL Server 2019 (15.x) and earlier versions require SQ

2016 (13.x) SP2 and later versions SQL database in Microsoft Fabric dynamic management function returns summary level attributes and information on transaction log files of databases. Use this information for monitoring and diagnostics of transaction log health.

Returns information about database table columns that are missing an index. is a dynamic management function. An integer that uniquely identifies a missing index. It can be obtained from the following sys.dm_db_missing_index_details (Transact-SQL) sys.dm_db_missing_index_groups (Transact-SQL) How the column is used by the query. The possible values and their EQUALI

Returns detailed information about missing indexes. In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out. Identifi

Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented. Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for. For more information, see Auto Stat

One missing index group may have several queries that needed the same index. For more information about individual queries that needed a specific index in this DMV, see sys.dm_db_missing_index_group_stats_query To query this dynamic management view, users must be granted the VIEW SERVER STATE permission or any permission that implies the VIEW SERVER STATE permission. Requires VIEW SERVER PERFORMAN

This DMV returns information about indexes that are missing in a specific index group. In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to.

Lists the indexes and constraints that will be disabled as a result of changing compatibility level in SQL Server. Indexes and constraints that contain persisted computed columns whose expressions use spatial UDTs will be disabled after upgrading or changing compatibility level. Use this dynamic management function to determine the impact of a change in compatibili

2019 (15.x) and later versions SQL database in Microsoft Fabric Returns information about a page in a database. The function returns one row that contains the header information from the page, including the function replaces the need to use . Valid input is the ID number of a database. The default is NULL, however sending a NULL value for this parameter will result in an error. Valid

Returns page and row-count information for every partition in the current database.

SQL database in Microsoft Some features of the Database Engine change the way that information is stored in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features can't be moved to an edition of SQL Server that doesn't support

Returns the number of pages allocated and deallocated by each session for the database. In Azure SQL Database, the values are unique within a single database or an elastic pool, but Number of pages reserved or allocated for user Number of pages deallocated and no longer reserved for user objects by this session.

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns the statistics histogram for the specified database object (table or indexed view) in the current SQL Server database. Similar to The ID of the object in the current database for which properties of one of its statistics is The ID of statistics for the specified .

filtered index, the number of rows might be less than the number of rows in the table. Total number of rows sampled for statistics calculations. Number of steps in the histogram. For more information, see DBCC SHOW_STATISTICS (Transact-SQL) unfiltered_rows Total number of rows in the table before applying the filter expression (for filtered statistics). If statistics are not filtered, unfiltered_r

Returns page allocation and deallocation activity by task for the database. Request ID within the session. A request is also called a batch and may contain one or more queries. A session may have multiple requests active at the same time. Each query in the request may start multiple threads (tasks), if a parallel execution Execution context ID of the task.

2017 (14.x) and later versions SQL database in Microsoft Fabric Returns detailed information about automatic tuning recommendations. For more information, Unique name of recommendation.

Displays information about In-Memory OLTP checkpoint files, including file size, physical location and the transaction ID. A memory-optimized file group internally uses append-only files to store inserted and deleted rows for in-memory tables. There are two types of files. A data file contains inserted rows while a delta file contains references to deleted rows. SQL Server 2014 (12.x) is substanti

Returns statistics about the In-Memory OLTP checkpoint operations in the current database. If the database has no In-Memory OLTP objects, In-Memory OLTP (In-Memory Optimization) SQL Server 2014 (12.x) is substantially different from more recent versions, and is discussed The following table describes the columns in Server 2016 (13.x) and later versions. Last LSN seen by the controller. Log bytes u

Outputs the current state of committed transactions that deleted one or more rows. The idle garbage collection thread wakes every minute or when the number of committed DML transactions exceeds an internal threshold since the last garbage collection cycle. As part of the garbage collection cycle, committed transactions move into one or more queues associated with generations.

These statistics are useful for understanding and tuning the bucket counts for . It can also be used to detect cases where the index key has many A large average chain length indicates that many rows are hashed to the same bucket. This If the number of empty buckets is low or the average and maximum chain lengths are similar, it is likely that the total bucket count is too low.

Contains statistics collected since the last database restart. In-Memory OLTP (In-Memory Optimization) Using Indexes on Memory-Optimized Tables ID of the object to which this index Internal ID corresponding to the current Note: Applies to SQL Server 2016 (13.x).

Reports the database-level memory consumers in the In-Memory OLTP database engine. The view returns a row for each memory consumer that the database engine uses. Use this DMV to see how the memory is distributed across different internal objects. In-Memory OLTP overview and usage scenarios ID (internal) of the memory consumer. 0 = Aggregation. (Aggregates memory usage of two or more consumers. It

system dynamic management view includes statistics about operations on nonclustered indexes in contains one row for each nonclustered index on a memory-optimized table in the current memory index structure is created. In-memory index structures are recreated on database to understand and monitor index activity during DML operations and when a database is brought online.

Reports the number rows affected by operations on each of the In-Memory OLTP objects since the last database restart. Statistics are updated when the operation executes, regardless of whether the transaction commits or was rolled back. system dynamic management view can help you identify which memory-optimized tables are changing the most.

Returns memory usage statistics for each In-Memory OLTP table (user and system) in the current database. The system tables have negative object IDs and are used to store run-time information for the In-Memory OLTP engine. Unlike user objects, system tables are internal and only exist in-memory, therefore, they are not visible through catalog views.

Reports the active transactions in the In-Memory OLTP database engine. In-Memory OLTP (In-Memory Optimization) Internal ID for this transaction in the XTP The transaction ID. Joins with the for XTP-only transactions, such as transactions started by natively compiled stored of the session that is executing Begin transaction serial number of the End transaction serial number of the transaction The d

2025 (17.x) Preview and later versions Returns a single row reflecting the status of the In-Memory OLTP (XTP) database engine when removing the engine from a database. XTP engine removal, or undeployment, is a multi-step process initiated by the statement that removes the last remaining memory-optimized container from step in the process.

Returns a row for each query processor job that is scheduled for asynchronous (background) Time when the job was added to the queue. Database on which the job is to execute. Value depends on the job type. For more information, see the Remarks Value depends on the job type. For more information, see the Remarks Value depends on the job type.

Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution. Number of requests successfully posted to the queue. Number of requests that started execution. Number of requests serviced to either success or failure.

Execution Related Dynamic Management Views and Functions (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL) sys.syscacheobjects (Transact-SQL)

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

2016 (13.x) and later Returns errors that occur on PolyBase compute nodes. Unique across all query errors in the sys.dm_exec_compute_nodes (Transact-

2016 (13.x) and later Holds additional information about the performance and status of all PolyBase nodes. Lists one Unique across scale-out cluster regardless of Any string of appropriate length. Total time elapsed since system start or restart. If total_elapsed_time exceeds the maximum value for an integer (24.8 days in milliseconds), it will cause materialization

2016 (13.x) and later versions Holds information about nodes used with PolyBase data management. It lists one row per Use this DMV to see the list of all nodes in the scale-out cluster with their role, name and IP Unique numeric id associated with PolyBase troubleshooting with dynamic management views Dynamic Management Views and Functions (Transact-SQL) Database Related Dynamic Managem

SQL analytics endpoint in Microsoft Fabric SQL database in Microsoft Fabric Returns information about the connections established to this instance of the database engine and the details of each connection. Returns server wide connection information for SQL Server and Azure SQL Managed Instance. Returns connection information for the current database in Azure SQL Database. Returns connection inform

This dynamic management function takes a Transact-SQL statement as a parameter and returns the metadata for the first result set of the statement. returns the same result set definition as sys.dm_exec_describe_first_result_set_for_object One or more Transact-SQL statements. The provides a declaration string for parameters for the Tra

This dynamic management function takes an @object_id as a parameter and describes the first result metadata for the module with that ID. The @object_id specified can be the ID of a Transact-SQL stored procedure or a Transact-SQL trigger.

2016 (13.x) and later versions Holds information about all steps that compose a given PolyBase request or query. It lists one row per query step. sys.dm_exec_requests (Transact-SQL) 0 to (n-1) for a request with n steps.

2016 (13.x) and later versions Holds information about all requests currently or recently active in PolyBase queries. It lists one Based on session and request ID, a user can then retrieve the actual distributed requests generated to be executed - via sys.dm_exec_distributed_requests.

2016 (13.x) and later Holds information about all SQL query distributions as part of a SQL step in the query. This view shows the data for the last 1000 requests; active requests always have the data present in sys.dm_exec_requests (Transact- sys.dm_exec_distributed_request_steps sys.dm_exec_compute_nodes (Transact-SQL) Set to -1 for requests that run at the node scope not the distribut

2016 (13.x) and later versions Holds information about all of the DMS services running on the PolyBase compute nodes. It lists one row per service instance. sys.dm_exec_compute_nodes (Transact- PolyBase troubleshooting with dynamic management views Dynamic Management Views and Functions (Transact-SQL) Database Related Dynamic Management Views (Transact-SQL)

2016 (13.x) and later versions Holds information about all workers completing DMS steps. This view shows the data for the last 1000 requests and active requests; active requests always have the data present in this view. sys.dm_exec_distributed_request_steps sys.dm_exec_dms_workers (Transact-SQL) sys.dm_exec_compute_nodes (Transact-

2016 (13.x) and later versions Captures information about external PolyBase operations. sys.dm_exec_requests (Transact- sys.dm_exec_distributed_request_steps 0-1 - multiplied by factor 100 (completed) 0-1 - multiplied by factor 100 (completed) PolyBase troubleshooting with dynamic management views Dynamic Management Views and Functions (Transact-SQL) Database Related Dynamic Management

2016 (13.x) and later versions Returns information about the workload per worker, on each compute node. to identify the work spun up to communicate with the external data source (for example, Hadoop or MongoDB). sys.dm_exec_requests (Transact- sys.dm_exec_requests (Transact- sys.dm_exec_dms_workers (Transact-SQL) sys.dm_exec_compute_nodes (Transact-SQL) 'File Split' (for Hadoop and Azur

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns aggregate performance statistics for cached functions. The view returns one row for each cached function plan, and the lifetime of the row is as long as the function remains cached. When a function is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics S

2014 (12.x) SP2 and later versions SQL database in Microsoft Fabric Returns information about statements submitted to an instance of SQL Server. Is the session ID executing the batch to be looked up.

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan.

sys.dm_exec_procedure_stats (Transact-SQL) sys.dm_exec_trigger_stats (Transact-SQL) ID of database. For static SQL in a stored procedure, the ID of the database containing the stored procedure. Null otherwise. Is NULL for ad hoc and prepared SQL statements. For a numbered stored procedure, this column returns the number of the stored procedure. For more information, see sys.numbered_procedures Is

Returns information about all queries that have requested and are waiting for a memory grant or have been given a memory grant. Queries that do not require a memory grant will not appear in this view. For example, sort and hash join operations have memory grants for query execution, while queries without an clause will not have a memory grant. In Azure SQL Database,

On Azure SQL Database service objectives, and for databases in Microsoft Entra admin account, or membership in the is required. On all other SQL Database service objectives, either the permission on the database, or membership in the server role is required. contains the following properties (counters). All occurrence values are cumulative and are set to at system restart.

Requires VIEW SERVER PERFORMANCE STATE permission on the server. SQL Server uses a tiered gateway approach to reduce the number of permitted concurrent compilations. Three gateways are used, including small, medium, and big. Gateways help prevent the exhausting of overall memory resources by larger compilation memory-requiring Waits on a gateway result in delayed compilation. In addition to delays

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns worker availability information per node. Number of schedulers on this node. Maximum number of workers for parallel queries. Number of workers reserved by parallel queries, plus number of main Number of workers available for tasks.

Monitors real time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the description field. Or, join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns.

Returns the information about the current query-resource semaphore status in SQL Server. provides general query-execution memory status and allows you to determine whether the system can access enough memory. This view complements memory information obtained from complete picture of server memory status.

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.

Associates up to 128 bytes of binary information with the current session or connection. constant, or a constant that is implicitly convertible to , to associate with the current session or connection. variable holding a context value to associate with the current session , SET CONTEXT_INFO affects the current session. T

2016 (13.x) and later versions SQL database in Microsoft Fabric Returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches. This view returns the same information that is aggregated for Name of the wait type. For more information

Associates up to 128 bytes of binary information with the current session or connection. constant, or a constant that is implicitly convertible to , to associate with the current session or connection. variable holding a context value to associate with the current session , SET CONTEXT_INFO affects the current session. T

Number of characters into the currently executing batch or stored procedure at occurs.

Indicates whether the corresponding stored procedure is encrypted. 0 = not encrypted Column is not nullable. Contains the compile-time Showplan representation of the query execution plan that is specified with . The Showplan is in text format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls. Col

sys.dm_exec_procedure_stats (Transact-SQL) sys.dm_exec_trigger_stats (Transact-SQL) ID of database. For static SQL in a stored procedure, the ID of the database containing the stored procedure. Null otherwise. Is NULL for ad hoc and prepared SQL statements. For a numbered stored procedure, this column returns the number of the stored procedure. For more information, see sys.numbered_procedures Is

Returns information about active handles that have been opened by is specified, this function returns information about XML handles If 0 is specified, the function returns information about all XML handles for all sessions.

2016 (13.x) and later Returns one row for each type of external script request. The external script requests are grouped by the supported external script language. One row is generated for each registered external script function. Arbitrary external script functions aren't recorded unless sent by a Name of the registered external script language.

2016 (13.x) and later Returns a row for each active worker account that is running an external script.

Returns information on the full-text catalogs that have some population activity in progress on ID of the database that contains the active full-text ID of the active full-text catalog. Address of memory buffers allocated for the population activity related to this full-text catalog.

Returns information on the current activity of the filter daemon host or hosts on the server Windows process ID of the filter daemon host. Type of document being processed by the filter daemon host, one Maximum number of threads in the filter daemon host. Number of batches that are being processed in the filter daemon On SQL Server and SQL Managed Instance, require

Returns information about the document-level content of a full-text index associated with the sys.dm_fts_index_keywords_by_document is a dynamic management function. sys.dm_fts_index_keywords (Transact-SQL) sys.dm_fts_index_keywords_by_property (Transact-SQL) function.

Returns information about the full-text index and semantic key phrase populations currently in ID of the database that contains the full-text ID of the full-text catalog that contains this full- ID of the table for which the full-text index is Memory address of the internal data structure that is used to represent an active population. Type of population.

Returns information about memory buffers belonging to a specific memory pool that are used as part of a full-text crawl or a full-text crawl range.

Returns information about the shared memory pools available to the Full-Text Gatherer component for a full-text crawl or a full-text crawl range.

Returns information about each full-text indexing batch.

Returns information about the specific ranges related to a full-text index population currently Address of memory buffers allocated for activity related to this subrange of a full-text index population. Address of memory buffers representing the parent object of all ranges of population related to a full-text index. If the value is 1, this subrange is responsible f

If the Windows Server failover cluster (WSFC) node hosts a local instance of SQL Server that is enabled for Always On availability groups and has WSFC quorum, the view returns a row for each member that constitutes the quorum, and the state of that member. This set includes all nodes in the cluster (returned with the disk or file-share witness, if any.

Analytics Platform System This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty The name of the drive (the drive letter) that represents an individual disk taking part in the cluster shared disk array. Column is not nullable.

Returns information on all valid shared disks, including clustered shared volumes, for a SQL Server failover cluster instance. If the instance isn't clustered, an empty rowset is returned. Volume mount point or drive path that can be used as a root directory for database and log files. Not nullable. Current owner of the drive. For cluster shared volumes (CSV), the owner is the node which is hostin

Returns a row for each pending I/O request in SQL Server. Memory address of the IO request. Is not nullable. Type of pending I/O request. Is not nullable. Internal use only. Is not nullable. Indicates whether the I/O request is pending (1) or has been completed by the operating system (0). An I/O request can still be pending even when OS has completed the request, b

Returns I/O statistics for data and log files. This dynamic management function replaces the Syntax for SQL Server and Azure SQL Database: Syntax for Azure Synapse Analytics: : SQL Server 2008 (10.0.x) and later, Azure SQL Database is int, with no default.

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

Returns a row for each user instance that has been created from the parent server instance.

'Analytics Platform System (PDW) Returns one row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly "virtual server") has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset. Name of a node in the '

Returns information about session dispatcher pools. Dispatcher pools are thread pools used by system components to perform background processing. The address of the dispatcher pool. dispatcher_pool_address The type of the dispatcher pool. Is not nullable. There are two types of dispatcher pools: Query the DMV for the full list The name of the dispatcher pool.

Starting with SQL Server 2017 (14.x) CU 1, volumes mounted to drive letters like For SQL Server 2019 (15.x) and previous versions, requires VIEW SERVER STATE permission on For SQL Server 2022 (16.x) and later versions, requires VIEW SERVER PERFORMANCE STATE Dynamic Management Views and Functions (Transact-SQL) I/O Related Dynamic Management Views and Functions (Transact-SQL)

2017 (14.x) and later Returns one row that displays operating system version information. The type of operating system.

Returns all the hosts currently registered in an instance of SQL Server. This view also returns the resources that are used by these hosts. Internal memory address of the host object. Type of hosted component. For example, SOSHOST_CLIENTID_SERVERSNI= SQL Server Native SOSHOST_CLIENTID_SQLOLEDB = SQL Server Native SOSHOST_CLIENTID_MSDART = Microsoft Data Access Total

Returns information about all latch waits organized by class. Number of waits on latches in this class. This counter is incremented at the start of a latch wait. Total wait time, in milliseconds, on latches in this class. This column is updated every five minutes during a latch wait and at the end of a latch wait.

Returns a row for each module loaded into the server address space. Address of the module in the process. Version of the file. Appears in the following format: Version of the product. Appears in the following format: 1 = Module is a debug version of the loaded module. 1 = Module is a pre-release version of the loaded module.

Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from counters can indicate memory use from external components in the SQL Server memory space. Memory brokers fairly distribute memory allocations between various components within SQL Server, based on current and projected usage. Me

Returns the status of each hand for a specific cache clock. Address of the cache associated with the clock. Is not Name of the cache. Is not nullable. Type of cache store. There can be several caches of the Type of hand. Value is one of the following: Status of the clock.

Returns a snapshot of the health of a cache in SQL Server. provides run-time information about the cache entries allocated, their use, and the source of Indicates the address (primary key) of the counters associated with a specific cache. Is not nullable. Specifies the name of the cache. Is not nullable.

Returns information about all entries in caches in SQL Server. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache Address of the cache. Not nullable.

Returns a row for each active cache in the instance of SQL Server. Address (primary key) of the cache entry. Is not Name of the cache. Is not nullable. Type of cache. Is not nullable. Hash table number. A particular cache may have multiple hash tables that correspond to different hash Number of buckets in the hash table. Is not nullable. Number of buckets that are c

Returns the set of all memory clerks that are currently active in the instance of SQL Server. Specifies the unique memory address of the memory clerk. This is the primary key column. Is not nullable. Specifies the type of memory clerk. Every clerk has a specific type, such as CLR Clerks MEMORYCLERK_SQLCLR. Is not nullable. Specifies the internally assigned name of t

Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from counters can indicate memory use from external components in the SQL Server memory space. Nodes are created per physical NUMA memory nodes.

Returns memory objects that are currently allocated by SQL Server. You can use to analyze memory use and to identify possible memory leaks. Address of the memory object. Is not nullable. Address of the parent memory object. Is nullable. : SQL Server 2008 (10.0.x) through SQL Server Number of pages that are allocated by this object.

Returns a row for each object store in the instance of SQL Server. You can use this view to monitor cache memory use and to identify bad caching behavior Memory address of the entry that represents the ID of a specific pool within a set of pools. Is not nullable. Type of object pool. Is not nullable. For more sys.dm_os_memory_clerks (Transact- System-assigned name o

An internal component named the SQLOS creates node structures that mimic hardware processor locality. These structures can be changed by using The following table provides information about these nodes. Description of the node state. Values are displayed with the mutually exclusive values first, followed by the combinable values. For example: Online, Thread Resource

Returns a row per performance counter maintained by the server. For information about each Category to which this counter belongs.

Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations. However, memory allocations might be performed in the SQL Server address space that bypasses internal memory management routines. Values are obtained through calls to the base operating system. The

Each row represents a record in a ring buffer of a specific type. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Not nullable. The type of the ring buffer record. Not nullable. The time when a ring buffer record was added, in milliseconds since the computer started. No

Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks. For more information about schedulers, see the Thread and Task Architecture Guide Memory address of the scheduler. Is not nullable.

Returns configuration information about the SQL Server failover cluster diagnostics log.

This dynamic management view is used internally by SQL Server to do the following: Keep track of debug data such as outstanding allocations. Assume or validate logic that is used by SQL Server components in places where the component assumes that a certain call has been made. Unique address for this stack allocation. Is not nullable. Each line represents a function

Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server. Specifies the current CPU check count. CPU ticks are obtained from the processor's RDTSC counter. It's a monotonically increasing number. Not nullable. Specifies the number of milliseconds since the computer started. Not nullable. Sp

Returns memory information from the operating system. SQL Server is bounded by, and responds to, external memory conditions at the operating system level and the physical limits of the underlying hardware. Determining the overall system state is an important part of evaluating SQL Server memory usage. Total size of physical memory available to the operating system,

Returns one row for each task that is active in the instance of SQL Server. A task is the basic unit of execution in SQL Server. Examples of tasks include a query, a login, a logout, and system tasks like ghost cleanup activity, checkpoint activity, log writer, parallel redo activity. For more information about tasks, see the Thread and Task Architecture Guide State

Returns a list of all SQL Server Operating System threads that are running under the SQL Server Memory address (Primary Key) of the thread. Indicates the thread initiator. 1 = SQL Server started the thread. 0 = Another component started the thread, such as an extended stored procedure from within SQL Server.

Returns information about a range of pages in the virtual address space of the calling process. Pointer to the base address of the region of pages. Is Pointer to the base address of a range of pages allocated by the VirtualAlloc Windows API function. The page pointed to by the BaseAddress member is contained within this allocation range. Is not nullable. Protection

Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries sys.dm_exec_session_wait_stats provides similar information by session.

Returns information about the wait queue of tasks that are waiting on some resource. For more information about tasks, see the Thread and task architecture guide ID of the session associated with the task.

Returns a row for every worker in the system. For more information about workers, see the Thread and Task Architecture Guide 1 = Worker is running with preemptive scheduling. Any worker that is running external code is run 1 = Worker is running with lightweight pooling. For 1 = Worker is stuck trying to obtain a spin lock. If this bit is set, this might indicate a p

Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics. The ID of the resource pool. Not nullable.

Returns one row for each user-defined entity that is referenced by name in the definition of the specified referencing entity in SQL Server.

Returns one row for each entity in the current database that references another user-defined entity by name. A dependency between two entities is created when one entity, called the , appears by name in a persisted SQL expression of another entity, called the .

2019 (15.x) and later versions SQL database in Microsoft Fabric Returns information about unresolved, aborted transactions in the Database Engine instance. In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a logical server. The starting LSN of the aborted transaction. The ending LSN of the aborted transaction.

Unique identification number assigned for the transaction. The transaction ID is primarily used to identify the transaction in locking operations. Transaction sequence number. This is a unique sequence number that is assigned to a transaction when it starts. Transactions that do not generate version records and do not use snapshot scans will not receive a transaction Sequence number that indicates

dynamic management view returns information about transactions for the instance.

Displays one row for each transaction that is committed for a table that is tracked by SQL management view, which is provided for supportability purposes and exposes the transaction-related information that change efficient persistent mapping from a database-specific transaction ID to the transaction's commit log sequence number (LSN) and commit timestamp.

Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts. If the current transaction is not a snapshot transaction, this the active transactions for the current snapshot transaction. Transaction sequence number of the active transaction. : Azure Synapse Analytics, Analytics Platform System The identifier

Returns a single row that displays the state information of the transaction in the current Transaction ID of the current snapshot. Sequence number of the transaction that generates the Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0.

dynamic management view returns information about transactions at the database level.

Returns information about MSDTC statistics in SQL Server. The number of transactions that were shut down before they were The highest number of aborted transactions since DTC last started. The number of aborted transactions that were manually shut down The number of committed transactions for the instance. The highest number of committed transactions since DTC last started. The number of committed

Returns information about currently active lock manager resources in SQL Server. Each row represents a currently active request to the lock manager for a lock that has been granted or is The columns in the result set are divided into two main groups: resource and request.

2019 (15.x) and later versions SQL database in Microsoft Fabric Returns information for accelerated database recovery (ADR) persistent version store (PVS) In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a The size of the off-row versions in PVS, in kilobytes. Does not include the size of row versions stored in- row.

The identifier for the node that this distribution is on. On SQL Server and SQL Managed Instance, requires In Microsoft Fabric, membership in the or more privileged role is needed to query On SQL Database service objectives, and for databases in Microsoft Entra admin account, or membership in the is required. On all other SQL Database service objectives, permission on the database, or membership i

Returns a virtual table for the objects that are producing the most versions in the version store. returns the top 256 aggregated record lengths that are is an inefficient view to run because this view queries the version store, and the version store can be very large.

Transaction sequence number (XSN) of a snapshot transaction. Snapshot ID for each Transact-SQL statement started under read- committed using row versioning. This value is used to generate a transactionally consistent view of the database supporting each query that is being run under read-committed using row Transaction sequence number of a transaction that was active when the snapshot transaction

Returns a virtual table that displays all version records in the version store. is inefficient to run because it queries the entire version store, and the version store can be very large. Each versioned record is stored as binary data together with some tracking or status information. Similar to records in database tables, version-store records are stored in 8192-

2016 (13.x) SP2 and later versions SQL database in Microsoft Fabric Returns a table that displays total space in used by version store records for each is efficient and not expensive to run, as it doesn't navigate through individual version store records, and returns aggregated version store space consumed in tempdb per database. Each versioned record is stored as binary data, together

Outputs information about each garbage collection worker queue on the server, and various statistics about each. There is one queue per logical CPU. The main garbage collection thread (the Idle thread) tracks updated, deleted, and inserted rows for all transactions completed since the last invocation of the main garbage collection thread. When the garbage collectio