Skip to content

System Dynamic Management Views

    Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

    Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

    There are two types of dynamic management views and functions:

    • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
    • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

    Querying Dynamic Management Views

    Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions on the other hand can be referenced in Transact-SQL statements by using either two-part or three-part names. Dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.

    All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:

    SQL

    SELECT wait_type, wait_time_ms 

    FROM sys.dm_os_wait_stats; 

    Required Permissions

    To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively restrict access of a user or login to dynamic management views and functions. To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.

    Dynamic management views and functions have been organized into the following categories.

    • Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)

    Always On Availability Groups Dynamic Management Views – Functions

    sys.dm_hadr_auto_page_repair

    sys.dm_hadr_availability_group_states

    sys.dm_hadr_availability_replica_cluster_nodes

    sys.dm_hadr_availability_replica_cluster_states

    sys.dm_hadr_availability_replica_states

    sys.dm_hadr_cluster

    sys.dm_hadr_cluster_members

    sys.dm_hadr_cluster_networks

    sys.dm_hadr_database_replica_cluster_states

    sys.dm_hadr_database_replica_states

    sys.dm_hadr_instance_node_map

    sys.dm_hadr_name_id_map

    sys.dm_tcp_listener_states

    • Change Data Capture Related Dynamic Management Views (Transact-SQL)

    Change Data Capture – sys.dm_cdc_errors

    • Change Tracking Related Dynamic Management Views

    Change Tracking – sys.dm_tran_commit_table

    • Common Language Runtime Related Dynamic Management Views (Transact-SQL)

    Common Language Runtime Related Dynamic Management Views (Transact-SQL)

    sys.dm_clr_appdomains

    sys.dm_clr_properties

    sys.dm_clr_loaded_assemblies

    sys.dm_clr_tasks

    • Database Mirroring Related Dynamic Management Views (Transact-SQL)

    Database Mirroring – sys.dm_db_mirroring_auto_page_repair

    • Database Related Dynamic Management Views (Transact-SQL)

    Database Related Dynamic Management Views (Transact-SQL)

    sys.dm_db_file_space_usage

    sys.dm_db_log_info

    sys.dm_db_log_stats

    sys.dm_db_partition_stats

    sys.dm_db_session_space_usage

    sys.dm_db_uncontained_entities

    sys.dm_db_fts_index_physical_stats

    sys.dm_db_log_space_usage

    sys.dm_db_page_info

    sys.dm_db_persisted_sku_features

    sys.dm_db_task_space_usage

    • DMV’s unique to SQL Database or Azure Synapse Analytics.

    sys.dm_db_wait_stats (Azure SQL Database)

    sys.dm_db_resource_stats (Azure SQL Database)

    sys.dm_operation_status (Azure SQL Database)

    sys.dm_database_copies (Azure SQL Database)

    sys.dm_db_objects_impacted_on_version_change (Azure SQL Database)

    • Execution Related Dynamic Management Views and Functions (Transact-SQL)

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

    sys.dm_exec_background_job_queue

    sys.dm_exec_cached_plan_dependent_objects

    sys.dm_exec_compute_node_errors

    sys.dm_exec_compute_nodes

    sys.dm_exec_cursors

    sys.dm_exec_describe_first_result_set_for_object

    sys.dm_exec_distributed_requests

    sys.dm_exec_dms_services

    sys.dm_exec_external_operations

    sys.dm_exec_function_stats

    sys.dm_exec_plan_attributes

    sys.dm_exec_query_memory_grants

    sys.dm_exec_query_optimizer_memory_gateways

    sys.dm_exec_query_parallel_workers

    sys.dm_exec_query_resource_semaphores

    sys.dm_exec_query_stats

    sys.dm_exec_requests

    sys.dm_exec_sessions

    sys.dm_exec_text_query_plan

    sys.dm_exec_valid_use_hints

    sys.dm_external_script_execution_stats

    sys.dm_exec_background_job_queue_stats

    sys.dm_exec_cached_plans

    sys.dm_exec_compute_node_status

    sys.dm_exec_connections

    sys.dm_exec_describe_first_result_set

    sys.dm_exec_distributed_request_steps

    sys.dm_exec_distributed_sql_requests

    sys.dm_exec_dms_workers

    sys.dm_exec_external_work

    sys.dm_exec_input_buffer

    sys.dm_exec_procedure_stats

    sys.dm_exec_query_optimizer_info

    sys.dm_exec_query_plan

    sys.dm_exec_query_profiles

    sys.dm_exec_query_statistics_xml

    sys.dm_exec_query_plan_stats

    sys.dm_exec_session_wait_stats

    sys.dm_exec_sql_text

    sys.dm_exec_trigger_stats

    sys.dm_exec_xml_handles

    sys.dm_external_script_requests

    • Extended Events Dynamic Management Views

    Extended Events Dynamic Management Views

    sys.dm_xe_map_values (Transact-SQL)

    sys.dm_xe_object_columns (Transact-SQL)

    sys.dm_xe_objects (Transact-SQL)

    sys.dm_xe_packages (Transact-SQL)

    sys.dm_xe_session_event_actions (Transact-SQL)

    sys.dm_xe_session_events (Transact-SQL)

    sys.dm_xe_session_object_columns (Transact-SQL)

    sys.dm_xe_session_targets (Transact-SQL)

    sys.dm_xe_sessions (Transact-SQL)

    • Filestream and FileTable Dynamic Management Views (Transact-SQL)

    Filestream and FileTable Dynamic Management Views (Transact-SQL)

    Filestream Dynamic Management Views and Functions

    sys.dm_filestream_file_io_handles (Transact-SQL)
    Displays the currently open transactional file handles.

    sys.dm_filestream_file_io_requests (Transact-SQL)
    Displays current file input and file output requests.

    FileTable Dynamic Management Views and Functions

    sys.dm_filestream_non_transacted_handles (Transact-SQL)
    Displays the currently open non-transactional file handles to FileTable data.

    Full-Text Search and Semantic Search Dynamic Management Views and Functions (Transact-SQL)

    Full-Text and Semantic Search Dynamic Management Views – Functions

    This section contains the following dynamic management views and functions that are related to full-text search and semantic search.

    Full-Text Search Dynamic Management Views and Functions

    sys.dm_fts_active_catalogs (Transact-SQL)
    Returns information on the full-text catalogs that have some population activity in progress on the server.

    sys.dm_fts_fdhosts
    Returns information on the current activity of the filter daemon host or hosts on the server instance.

    sys.dm_fts_index_keywords
    Returns information about the content of a full-text index for the specified table.

    sys.dm_fts_index_keywords_by_document
    Returns information about the document-level content of a full-text index for the specified table. A given keyword can appear in several documents.

    sys.dm_fts_index_keywords_by_property
    Returns all property-related content in the full-text index of a given table. This includes all data that belongs to any property registered by the search property list associated with that full-text index.

    sys.dm_fts_index_keywords_position_by_document
    Returns the position of keywords in a document.

    sys.dm_fts_index_population
    Returns information about the full-text index populations currently in progress.

    sys.dm_fts_memory_buffers
    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.

    sys.dm_fts_memory_pools
    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.

    sys.dm_fts_outstanding_batches
    Returns information about each full-text indexing batch.

    sys.dm_fts_parser
    Returns the final tokenization result after applying a given word breaker, thesaurus, and stoplist combination to a query string input. The output is equivalent to the output if the specified given query string were issued to the Full-Text Engine.

    sys.dm_fts_population_ranges
    Returns information about the specific ranges related to a full-text index population currently in progress.

     

    Semantic Search Dynamic Management Views and Functions

    sys.dm_fts_semantic_similarity_population (Transact-SQL)
    Returns one row of status information about the population of the document similarity index for each similarity index in each table that has an associated semantic index.

    • Geo-Replication Dynamic Management Views and Functions (Azure SQL Database)

    Geo-Replication Dynamic Management Views and Functions (Azure SQL Database)

    sys.geo_replication_links (Azure SQL Database)

    sys.dm_operation_status (Azure SQL Database)

    sys.dm_geo_replication_link_status (Azure SQL Database)

    sys.dm_continuous_copy_status (Azure SQL Database)

    • Index Related Dynamic Management Views and Functions (Transact-SQL)

    Index Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

    sys.dm_db_index_operational_stats (Transact-SQL)

    sys.dm_db_index_usage_stats (Transact-SQL)

    sys.dm_db_missing_index_details (Transact-SQL)

    sys.dm_db_missing_index_groups (Transact-SQL)

    sys.dm_db_index_physical_stats (Transact-SQL)

    sys.dm_db_missing_index_columns (Transact-SQL)

    sys.dm_db_missing_index_group_stats (Transact-SQL)

    • I O Related Dynamic Management Views and Functions (Transact-SQL)

    I/O Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_io_backup_tapes (Transact-SQL)

    sys.dm_io_pending_io_requests (Transact-SQL)

    sys.dm_io_cluster_valid_path_names (Transact-SQL)

    sys.dm_io_cluster_shared_drives (Transact-SQL)

    sys.dm_io_virtual_file_stats (Transact-SQL)

    • Memory-Optimized Table Dynamic Management Views (Transact-SQL)

    Memory-Optimized Table Dynamic Management Views (Transact-SQL)

    sys.dm_db_xtp_checkpoint_stats (Transact-SQL)

    sys.dm_db_xtp_gc_cycle_stats (Transact-SQL)

    sys.dm_db_xtp_index_stats (Transact-SQL)

    sys.dm_db_xtp_merge_requests (Transact-SQL)

    sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

    sys.dm_db_xtp_transactions (Transact-SQL)

    sys.dm_xtp_gc_stats (Transact-SQL)

    sys.dm_xtp_transaction_stats (Transact-SQL)

    sys.dm_db_xtp_checkpoint_files (Transact-SQL)

    sys.dm_db_xtp_hash_index_stats (Transact-SQL)

    sys.dm_db_xtp_memory_consumers (Transact-SQL)

    sys.dm_db_xtp_object_stats (Transact-SQL)

    sys.dm_db_xtp_table_memory_stats (Transact-SQL)

    sys.dm_xtp_gc_queue_stats (Transact-SQL)

    sys.dm_xtp_system_memory_consumers (Transact-SQL)

    Object Catalog Views

    The following object catalog views are used specifically with In-Memory OLTP.

    sys.hash_indexes (Transact-SQL)

    sys.memory_optimized_tables_internal_attributes (Transact-SQL)

    Internal DMVs

    There are additional DMVs that are intended for internal use only, and for which we provide no direct documentation. In the area of memory-optimized tables, undocumented DMVs include the following:

    • sys.dm_xtp_threads
    • sys.dm_xtp_transaction_recent_rows
    • Object Related Dynamic Management Views and Functions (Transact-SQL)

    Object Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_db_incremental_stats_properties

    sys.dm_db_stats_properties

    sys.dm_sql_referencing_entities

    sys.dm_db_stats_histogram

    sys.dm_sql_referenced_entities

    • Query Notifications Related Dynamic Management Views (Transact-SQL)

    Query Notifications – sys.dm_qn_subscriptions

    • Replication Related Dynamic Management Views (Transact-SQL)

    Replication Related Dynamic Management Views (Transact-SQL)

    sys.dm_repl_articles

    sys.dm_repl_tranhash

    sys.dm_repl_schemas

    sys.dm_repl_traninfo

    • Resource Governor Related Dynamic Management Views (Transact-SQL)

    Resource Governor Related Dynamic Management Views (Transact-SQL)

    sys.dm_resource_governor_configuration (Transact-SQL)

    sys.dm_resource_governor_external_resource_pool_affinity (Transact-SQL)

    sys.dm_resource_governor_resource_pool_affinity (Transact-SQL)

    sys.dm_resource_governor_external_resource_pools (Transact-SQL)

    sys.dm_resource_governor_resource_pools (Transact-SQL)

    sys.dm_resource_governor_resource_pool_volumes (Transact-SQL)

    sys.dm_resource_governor_workload_groups (Transact-SQL)

    • Security-Related Dynamic Management Views and Functions (Transact-SQL)

    Security-Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_audit_actions (Transact-SQL)

    sys.dm_audit_class_type_map (Transact-SQL)

    sys.dm_cryptographic_provider_algorithms (Transact-SQL)

    sys.dm_cryptographic_provider_keys (Transact-SQL)

    sys.dm_cryptographic_provider_properties (Transact-SQL)

    sys.dm_cryptographic_provider_sessions (Transact-SQL)

    sys.dm_database_encryption_keys (Transact-SQL)

    sys.dm_server_audit_status (Transact-SQL)

    • Server-Related Dynamic Management Views and Functions (Transact-SQL)

    Server-Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_server_memory_dumps

    sys.dm_server_registry

    sys.dm_server_services

    • Service Broker Related Dynamic Management Views (Transact-SQL)

    Service Broker Related Dynamic Management Views (Transact-SQL)

    sys.dm_broker_activated_tasks

    sys.dm_broker_forwarded_messages

    sys.dm_broker_connections

    sys.dm_broker_queue_monitors

    • Spatial Data Related Dynamic Management Views and Functions (Transact-SQL)

    Spatial Data – sys.dm_db_objects_disabled_on_compatibility_level_change

    Example

    The following example shows a query on sys.dm_db_objects_disabled_on_compatibility_level_change to find the objects impacted by changing the compatibility level to 120.

    SQL

    SELECT * FROM sys.dm_db_objects_disabled_on_compatibility_level_change(120); 

    GO 

    • Azure Synapse Analytics and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)

    Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data Warehouse Dynamic Management Views

    The following dynamic management views apply to both Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data Warehouse:

    sys.dm_pdw_dms_cores (Transact-SQL)

    sys.dm_pdw_dms_external_work (Transact-SQL)

    sys.dm_pdw_dms_workers (Transact-SQL)

    sys.dm_pdw_errors (Transact-SQL)

    sys.dm_pdw_exec_connections (Transact-SQL)

    sys.dm_pdw_exec_requests (Transact-SQL)

    sys.dm_pdw_exec_sessions (Transact-SQL)

    sys.dm_pdw_hadoop_operations (Transact-SQL)

    sys.dm_pdw_lock_waits (Transact-SQL)

    sys.dm_pdw_nodes (Transact-SQL)

    sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)

    sys.dm_pdw_os_threads (Transact-SQL)

    sys.dm_pdw_request_steps (Transact-SQL)

    sys.dm_pdw_resource_waits (Transact-SQL)

    sys.dm_pdw_sql_requests (Transact-SQL)

    sys.dm_pdw_sys_info (Transact-SQL)

    sys.dm_pdw_wait_stats (Transact-SQL)

    sys.dm_pdw_waits (Transact-SQL)

    Azure Synapse Analytics (SQL Data Warehouse) Dynamic Management Views

    The following dynamic management views apply to Azure Synapse Analytics (SQL Data Warehouse) only:

    sys.dm_pdw_nodes_exec_query_plan (Transact-SQL)

    sys.dm_pdw_nodes_exec_query_profiles (Transact-SQL)

    sys.dm_pdw_nodes_exec_query_statistics_xml (Transact-SQL)

    sys.dm_pdw_nodes_exec_sql-text (Transact-SQL)

    sys.dm_pdw_nodes_exec_text_query_plan (Transact-SQL)

    sys.dm_workload_management_workload_groups_stats (Transact-SQL) (Preview)

    Parallel Data Warehouse Dynamic Management Views

    The following dynamic management views apply to Parallel Data Warehouse only:

    sys.dm_pdw_component_health_active_alerts (Transact-SQL)

    sys.dm_pdw_component_health_alerts (Transact-SQL)

    sys.dm_pdw_component_health_status (Transact-SQL)

    sys.dm_pdw_diag_processing_stats (Transact-SQL)

    sys.dm_pdw_network_credentials (Transact-SQL)

    sys.dm_pdw_node_status (Transact-SQL)

    sys.dm_pdw_os_event_logs (Transact-SQL)

    sys.dm_pdw_os_performance_counters (Transact-SQL)

    sys.dm_pdw_query_stats_xe (Transact-SQL)

    sys.dm_pdw_query_stats_xe_file (Transact-SQL)

    • SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

    SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

    This section documents dynamic management views (DMV) that are associated with SQL Server Operating System (SQLOS). SQLOS is responsible for managing operating system resources that are specific to SQL Server.

    SQL SERVER OPERATING SYSTEM RELATED DYNAMIC MANAGEMENT VIEWS (TRANSACT-SQL)
    sys.dm_os_buffer_descriptorssys.dm_os_buffer_pool_extension_configuration
    sys.dm_os_child_instancessys.dm_os_cluster_nodes
    sys.dm_os_cluster_propertiessys.dm_os_dispatcher_pools
    sys.dm_os_enumerate_fixed_drivessys.dm_os_host_info
    sys.dm_os_hostssys.dm_os_latch_stats
    sys.dm_os_loaded_modulessys.dm_os_memory_brokers
    sys.dm_os_memory_cache_clock_handssys.dm_os_memory_cache_counters
    sys.dm_os_memory_cache_entriessys.dm_os_memory_cache_hash_tables
    sys.dm_os_memory_clerkssys.dm_os_memory_nodes
    sys.dm_os_nodessys.dm_os_performance_counters
    sys.dm_os_process_memorysys.dm_os_schedulers
    sys.dm_os_server_diagnostics_log_configurationssys.dm_os_spinlock_stats
    sys.dm_os_stackssys.dm_os_sys_info
    sys.dm_os_sys_memorysys.dm_os_tasks
    sys.dm_os_threadssys.dm_os_virtual_address_dump
    sys.dm_os_volume_statssys.dm_os_waiting_tasks
    sys.dm_os_wait_statssys.dm_os_windows_info
    sys.dm_os_workers

    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_ring_buffers
    sys.dm_os_memory_allocationssys.dm_os_sublatches
    sys.dm_os_worker_local_storage
    • Stretch Database Dynamic Management Views (Transact-SQL)

    There are two types of dynamic management views and functions:

    • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
    • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

    Querying Dynamic Management Views

    Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions on the other hand can be referenced in Transact-SQL statements by using either two-part or three-part names. Dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.

    All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:

    SQL

    SELECT wait_type, wait_time_ms 

    FROM sys.dm_os_wait_stats; 

    • Transaction Related Dynamic Management Views and Functions (Transact-SQL)

    Transaction Related Dynamic Management Views and Functions (Transact-SQL)

    sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)

    sys.dm_tran_current_snapshot (Transact-SQL)

    sys.dm_tran_database_transactions (Transact-SQL)

    sys.dm_tran_session_transactions (Transact-SQL)

    sys.dm_tran_transactions_snapshot (Transact-SQL)

    sys.dm_tran_version_store_space_usage

    sys.dm_tran_active_transactions (Transact-SQL)

    sys.dm_tran_current_transaction (Transact-SQL)

    sys.dm_tran_locks (Transact-SQL)

    sys.dm_tran_top_version_generators (Transact-SQL)

    sys.dm_tran_version_store (Transact-SQL)