Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
This section documents dynamic management views (DMVs) that are associated with SQL Server Operating System (SQLOS). SQLOS manages operating system resources that are specific to SQL Server.
Memory management and memory topology
These DMVs show how SQL Server allocates, tracks, caches, and structures memory across clerks, caches, NUMA nodes, and virtual address spaces.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_memory_clerks | Reports memory usage by memory clerks - internal SQLOS objects allocating memory - useful for pinpointing memory consumers. |
| sys.dm_os_memory_nodes | Displays memory distribution across NUMA nodes, showing how memory is allocated per node. |
| sys.dm_os_nodes | Returns NUMA node information visible to SQL Server, including node IDs and memory partitioning. |
| sys.dm_os_memory_brokers | Shows memory broker objects within SQLOS that manage allocation units for different consumers. |
| sys.dm_os_memory_cache_clock_hands | Provides clock sweep positions for memory cache objects, useful for diagnosing cache eviction behavior. |
| sys.dm_os_memory_cache_counters | Returns size and usage metrics for memory caches, aiding analysis of cache utilization patterns. |
| sys.dm_os_memory_cache_entries | Shows details about individual entries in memory caches, enabling granular analysis of what data and objects are cached. |
| sys.dm_os_memory_cache_hash_tables | Lists hash tables used by memory caches and shows metrics for distribution and load factors. |
| sys.dm_os_sys_memory | Provides system memory metrics reported from the OS that SQLOS uses for memory allocation decisions. |
| sys.dm_os_process_memory | Provides process-level memory metrics such as physical and virtual memory usage for SQL Server. |
| sys.dm_os_virtual_address_dump | Offers a dump of virtual address descriptors, helpful in low-level memory diagnostics. |
Buffer pool and storage cache
These DMVs report on the buffer pool, data pages in cache, and buffer pool extension (BPE) configurations.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_buffer_descriptors | Returns metadata for all data pages currently cached in the SQL Server buffer pool, including database and file associations and page type details. This view is useful for analyzing cache usage and distribution. |
| sys.dm_os_buffer_pool_extension_configuration | Provides configuration and state for the buffer pool extension (BPE), which allows the buffer pool to extend to disk storage. This view is useful for understanding how extended cache is configured. |
Scheduling, workers, tasks, and concurrency
These DMVs describe SQLOS scheduling, worker threads, tasks, queues, dispatcher pools, spinlocks, latches, and low-level concurrency mechanisms.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_schedulers | Displays scheduler state and run queue lengths for CPU scheduling by SQLOS. This view is critical for CPU and parallelism diagnostics. |
| sys.dm_os_workers | Lists workers managed by SQLOS, including state and flags that indicate exceptions or execution conditions. |
| sys.dm_os_threads | Returns information about worker threads within SQLOS, useful for thread and scheduler analysis. |
| sys.dm_os_tasks | Shows tasks currently managed by SQLOS, providing state and scheduling details. |
| sys.dm_os_dispatcher_pools | Provides statistics about internal dispatcher pools that handle work distribution across schedulers. This view helps you analyze parallelism and scheduling load. |
| sys.dm_os_spinlock_stats | Aggregates spinlock contention statistics, which helps identify low-level synchronization bottlenecks. |
| sys.dm_os_latch_stats | Aggregates latch wait statistics, showing contention and distribution of low-level synchronization primitives. |
| sys.dm_os_waiting_tasks | Shows tasks that are currently waiting along with wait types and resource details, enabling real-time wait analysis. |
Waits and performance diagnostics
These DMVs expose wait statistics, ring buffer diagnostics, counters, or server-level data that you can use to troubleshoot performance problems.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_wait_stats | Aggregates wait statistics for thread waits across the instance, a foundational view for diagnosing CPU, memory, or I/O bottlenecks. |
| sys.dm_os_ring_buffers | Returns entries from the internal ring buffer, useful for diagnosing system-level events like memory pressure or scheduler alerts. |
| sys.dm_os_performance_counters | Exposes performance counter values for SQL Server - often used to correlate SQL-level activity with Windows performance monitoring metrics. |
| sys.dm_os_server_diagnostics_log_configurations | Shows configuration for server diagnostics logs, which capture critical internal events and diagnostics. |
Host, cluster, and environment information
These DMVs provide information about host OS characteristics, cluster configuration, multihost participation, and general instance and system metadata.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_host_info | Exposes host OS details, such as OS version and configuration, relevant to the local SQL Server instance. |
| sys.dm_os_hosts | Returns a row for each host known to the SQL Server instance, useful in multihost or clustered environments. |
| sys.dm_os_cluster_nodes | Returns information about cluster node names and roles when SQL Server is running in a Windows failover cluster. |
| sys.dm_os_cluster_properties | Shows cluster-level properties and settings relevant to the SQL Server node, such as cluster state and configured behaviors. |
| sys.dm_os_sys_info | Returns high-level SQL Server instance information such as CPU count, memory configuration, and SQLOS version. |
| sys.dm_os_windows_info | Provides OS-specific information about the Windows environment hosting SQL Server, such as version and machine characteristics. |
| sys.dm_os_child_instances | Lists resource and state information about child SQLOS instances in environments like distributed or multi-instance deployments. |
Disk, volume, and I/O environment
These DMVs relate to storage devices, available volumes, and file-level or volume-level statistics.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_enumerate_fixed_drives | Returns a list of fixed disk drives accessible to the SQL Server instance, along with basic space information. |
| sys.dm_os_volume_stats | Returns I/O statistics for database files by volume, helpful in analyzing disk activity and performance. |
Loaded modules (executable code context)
This DMV exposes DLLs and modules currently loaded and active execution components.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_loaded_modules | Lists modules (DLLs/assemblies) that SQL Server workers load, providing insight into code executing within the server. |
Low-level debugging and diagnostics
This DMV is useful primarily for escalation, debugging, crash analysis, or deep SQL Server engine troubleshooting.
| Dynamic management view | Description |
|---|---|
| sys.dm_os_stacks | Displays stack information for workers, often used in deep troubleshooting or crash dump analysis. |
Internal and unsupported DMVs
The following SQL Server operating system-related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.:
sys.dm_os_function_symbolic_namesys.dm_os_memory_allocationssys.dm_os_sublatchessys.dm_os_worker_local_storage