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_descriptors | sys.dm_os_buffer_pool_extension_configuration |
| sys.dm_os_child_instances | sys.dm_os_cluster_nodes |
| sys.dm_os_cluster_properties | sys.dm_os_dispatcher_pools |
| sys.dm_os_enumerate_fixed_drives | sys.dm_os_host_info |
| sys.dm_os_hosts | sys.dm_os_latch_stats |
| sys.dm_os_loaded_modules | sys.dm_os_memory_brokers |
| sys.dm_os_memory_cache_clock_hands | sys.dm_os_memory_cache_counters |
| sys.dm_os_memory_cache_entries | sys.dm_os_memory_cache_hash_tables |
| sys.dm_os_memory_clerks | sys.dm_os_memory_nodes |
| sys.dm_os_nodes | sys.dm_os_performance_counters |
| sys.dm_os_process_memory | sys.dm_os_schedulers |
| sys.dm_os_server_diagnostics_log_configurations | sys.dm_os_spinlock_stats |
| sys.dm_os_stacks | sys.dm_os_sys_info |
| sys.dm_os_sys_memory | sys.dm_os_tasks |
| sys.dm_os_threads | sys.dm_os_virtual_address_dump |
| sys.dm_os_volume_stats | sys.dm_os_waiting_tasks |
| sys.dm_os_wait_stats | sys.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_name | sys.dm_os_ring_buffers |
| sys.dm_os_memory_allocations | sys.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)