SQL Server/DMVs

SQL Server Dynamic Management Views

Master 307 essential DMVs with practical query examples, performance tips, and real-world use cases for SQL Server monitoring and troubleshooting.

Always On(17)

sys.dm_hadr_availability_replica_statesmedium sys.dm_hadr_ag_threadsmedium sys.dm_hadr_auto_page_repairmedium sys.dm_hadr_automatic_seedingmedium sys.dm_hadr_availability_group_statesmedium sys.dm_hadr_availability_replica_cluster_nodesmedium sys.dm_hadr_availability_replica_cluster_statesmedium sys.dm_hadr_clustermedium sys.dm_hadr_cluster_membersmedium sys.dm_hadr_cluster_networksmedium sys.dm_hadr_database_replica_cluster_statesmedium sys.dm_hadr_database_replica_statesmedium sys.dm_hadr_db_threadsmedium sys.dm_hadr_instance_node_mapmedium sys.dm_hadr_name_id_mapmedium sys.dm_hadr_physical_seeding_statsmedium sys.dm_tcp_listener_statesmedium

Change Data Capture(5)

sys.dm_cdc_errorsmedium sys.dm_cdc_log_scan_sessionsmedium sys.dm_cdc_log_scan_sessions (transact-sql)medium sys.dm_repl_traninfomedium sys.dm_repl_traninfo (transact-sql)medium

CLR(4)

sys.dm_clr_appdomainsmedium sys.dm_clr_loaded_assembliesmedium sys.dm_clr_propertiesmedium sys.dm_clr_tasksmedium

Database(22)

Extended Events(18)

sys.dm_xe_map_valuesmedium sys.dm_xe_map_values (transact-sql)medium sys.dm_xe_object_columnsmedium sys.dm_xe_object_columns (transact-sql)medium sys.dm_xe_objectsmedium sys.dm_xe_objects (transact-sql)medium sys.dm_xe_packagesmedium sys.dm_xe_packages (transact-sql)medium sys.dm_xe_session_event_actionsmedium sys.dm_xe_session_event_actions (transact-sql)medium sys.dm_xe_session_eventsmedium sys.dm_xe_session_events (transact-sql)medium sys.dm_xe_session_object_columnsmedium sys.dm_xe_session_object_columns (transact-sql)medium sys.dm_xe_session_targetsmedium sys.dm_xe_session_targets (transact-sql)medium sys.dm_xe_sessionsmedium sys.dm_xe_sessions (transact-sql)medium

FileStream(6)

sys.dm_filestream_file_io_handlesmedium sys.dm_filestream_file_io_handles (transact-sql)medium sys.dm_filestream_file_io_requestsmedium sys.dm_filestream_file_io_requests (transact-sql)medium sys.dm_filestream_non_transacted_handlesmedium sys.dm_filestream_non_transacted_handles (transact-sql)medium

Full-Text(14)

sys.dm_fts_active_catalogsmedium sys.dm_fts_active_catalogs (transact-sql)medium sys.dm_fts_fdhostsmedium sys.dm_fts_index_keywordsmedium sys.dm_fts_index_keywords_by_documentmedium sys.dm_fts_index_keywords_by_propertymedium sys.dm_fts_index_populationmedium sys.dm_fts_memory_buffersmedium sys.dm_fts_memory_poolsmedium sys.dm_fts_outstanding_batchesmedium sys.dm_fts_parsermedium sys.dm_fts_population_rangesmedium sys.dm_fts_semantic_similarity_populationmedium sys.dm_fts_semantic_similarity_population (transact-sql)medium

I/O Performance(10)

sys.dm_io_virtual_file_statsmedium sys.dm_io_backup_tapesmedium sys.dm_io_backup_tapes (transact-sql)medium sys.dm_io_cluster_shared_drivesmedium sys.dm_io_cluster_shared_drives (transact-sql)medium sys.dm_io_cluster_valid_path_namesmedium sys.dm_io_cluster_valid_path_names (transact-sql)medium sys.dm_io_pending_io_requestsmedium sys.dm_io_pending_io_requests (transact-sql)medium sys.dm_io_virtual_file_stats (transact-sql)medium

In-Memory OLTP(18)

sys.dm_db_xtp_checkpoint_filesmedium sys.dm_db_xtp_checkpoint_statsmedium sys.dm_db_xtp_gc_cycle_statsmedium sys.dm_db_xtp_hash_index_statsmedium sys.dm_db_xtp_index_statsmedium sys.dm_db_xtp_memory_consumersmedium sys.dm_db_xtp_merge_requestsmedium sys.dm_db_xtp_nonclustered_index_statsmedium sys.dm_db_xtp_object_statsmedium sys.dm_db_xtp_table_memory_statsmedium sys.dm_db_xtp_transactionsmedium sys.dm_db_xtp_undeploy_statusmedium sys.dm_xtp_gc_queue_statsmedium sys.dm_xtp_gc_statsmedium sys.dm_xtp_system_memory_consumersmedium sys.dm_xtp_threadsmedium sys.dm_xtp_transaction_recent_rowsmedium sys.dm_xtp_transaction_statsmedium

Index Analysis(16)

sys.dm_db_index_operational_statsmedium sys.dm_db_index_physical_statsmedium sys.dm_db_index_usage_statsmedium sys.dm_db_missing_index_detailsmedium sys.dm_db_missing_index_group_statsmedium sys.dm_db_column_store_row_group_physical_statsmedium sys.dm_db_column_store_row_group_physical_stats (transact-sql)medium sys.dm_db_index_operational_stats (transact-sql)medium sys.dm_db_index_physical_stats (transact-sql)medium sys.dm_db_index_usage_stats (transact-sql)medium sys.dm_db_missing_index_columnsmedium sys.dm_db_missing_index_columns (transact-sql)medium sys.dm_db_missing_index_details (transact-sql)medium sys.dm_db_missing_index_group_stats (transact-sql)medium sys.dm_db_missing_index_groupsmedium sys.dm_db_missing_index_groups (transact-sql)medium

Object(5)

sys.dm_db_incremental_stats_propertiesmedium sys.dm_db_stats_histogrammedium sys.dm_db_stats_propertiesmedium sys.dm_sql_referenced_entitiesmedium sys.dm_sql_referencing_entitiesmedium

Operating System(40)

sys.dm_os_buffer_descriptorsmedium sys.dm_os_memory_clerksmedium sys.dm_os_performance_countersmedium sys.dm_os_process_memorymedium sys.dm_os_schedulersmedium sys.dm_os_sys_infomedium sys.dm_os_sys_memorymedium sys.dm_os_threadsmedium sys.dm_os_wait_statsmedium sys.dm_os_waiting_tasksmedium sys.dm_os_buffer_pool_extension_configurationmedium sys.dm_os_child_instancesmedium sys.dm_os_cluster_nodesmedium sys.dm_os_cluster_propertiesmedium sys.dm_os_dispatcher_poolsmedium sys.dm_os_enumerate_fixed_drivesmedium sys.dm_os_function_symbolic_namemedium sys.dm_os_host_infomedium sys.dm_os_hostsmedium sys.dm_os_latch_statsmedium sys.dm_os_loaded_modulesmedium sys.dm_os_memory_allocationsmedium sys.dm_os_memory_brokersmedium sys.dm_os_memory_cache_clock_handsmedium sys.dm_os_memory_cache_countersmedium sys.dm_os_memory_cache_entriesmedium sys.dm_os_memory_cache_hash_tablesmedium sys.dm_os_memory_nodesmedium sys.dm_os_nodesmedium sys.dm_os_ring_buffersmedium sys.dm_os_server_diagnostics_log_configurationsmedium sys.dm_os_spinlock_statsmedium sys.dm_os_stacksmedium sys.dm_os_sublatchesmedium sys.dm_os_tasksmedium sys.dm_os_virtual_address_dumpmedium sys.dm_os_volume_statsmedium sys.dm_os_windows_infomedium sys.dm_os_worker_local_storagemedium sys.dm_os_workersmedium

Query Performance(59)

sys.dm_exec_cached_plansmedium sys.dm_exec_connectionsmedium sys.dm_exec_function_statsmedium sys.dm_exec_procedure_statsmedium sys.dm_exec_query_planmedium sys.dm_exec_query_statsmedium sys.dm_exec_requestsmedium sys.dm_exec_sessionsmedium sys.dm_exec_sql_textmedium sys.dm_exec_trigger_statsmedium sys.dm_exec_background_job_queuemedium sys.dm_exec_background_job_queue_statsmedium sys.dm_exec_cached_plan_dependent_objectsmedium sys.dm_exec_compute_node_errorsmedium sys.dm_exec_compute_node_statusmedium sys.dm_exec_compute_nodesmedium sys.dm_exec_cursorsmedium sys.dm_exec_describe_first_result_setmedium sys.dm_exec_describe_first_result_set_for_objectmedium sys.dm_exec_distributed_request_stepsmedium sys.dm_exec_distributed_requestsmedium sys.dm_exec_distributed_sql_requestsmedium sys.dm_exec_dms_servicesmedium sys.dm_exec_dms_workersmedium sys.dm_exec_external_operationsmedium sys.dm_exec_external_workmedium sys.dm_exec_input_buffermedium sys.dm_exec_plan_attributesmedium sys.dm_exec_query_memory_grantsmedium sys.dm_exec_query_optimizer_infomedium sys.dm_exec_query_optimizer_memory_gatewaysmedium sys.dm_exec_query_parallel_workersmedium sys.dm_exec_query_plan_statsmedium sys.dm_exec_query_profilesmedium sys.dm_exec_query_resource_semaphoresmedium sys.dm_exec_query_statistics_xmlmedium sys.dm_exec_session_wait_statsmedium sys.dm_exec_text_query_planmedium sys.dm_exec_valid_use_hintsmedium sys.dm_exec_xml_handlesmedium sys.dm_external_script_execution_statsmedium sys.dm_external_script_requestsmedium sys.dm_pdw_dms_coresmedium sys.dm_pdw_dms_external_workmedium sys.dm_pdw_dms_workersmedium sys.dm_pdw_errorsmedium sys.dm_pdw_exec_connectionsmedium sys.dm_pdw_exec_requestsmedium sys.dm_pdw_exec_sessionsmedium sys.dm_pdw_hadoop_operationsmedium sys.dm_pdw_nodes_exec_query_planmedium sys.dm_pdw_nodes_exec_query_profilesmedium sys.dm_pdw_nodes_exec_query_statistics_xmlmedium sys.dm_pdw_nodes_exec_sql_textmedium sys.dm_pdw_nodes_exec_text_query_planmedium sys.dm_pdw_query_stats_xemedium sys.dm_pdw_query_stats_xe_filemedium sys.dm_pdw_request_stepsmedium sys.dm_pdw_sql_requestsmedium

Replication(3)

sys.dm_repl_articlesmedium sys.dm_repl_schemasmedium sys.dm_repl_tranhashmedium

Resource Governor(14)

sys.dm_resource_governor_configurationmedium sys.dm_resource_governor_configuration (transact-sql)medium sys.dm_resource_governor_external_resource_pool_affinitymedium sys.dm_resource_governor_external_resource_pool_affinity (transact-sql)medium sys.dm_resource_governor_external_resource_poolsmedium sys.dm_resource_governor_external_resource_pools (transact-sql)medium sys.dm_resource_governor_resource_pool_affinitymedium sys.dm_resource_governor_resource_pool_affinity (transact-sql)medium sys.dm_resource_governor_resource_pool_volumesmedium sys.dm_resource_governor_resource_pool_volumes (transact-sql)medium sys.dm_resource_governor_resource_poolsmedium sys.dm_resource_governor_resource_pools (transact-sql)medium sys.dm_resource_governor_workload_groupsmedium sys.dm_resource_governor_workload_groups (transact-sql)medium

Security(18)

sys.dm_audit_actionsmedium sys.dm_audit_actions (transact-sql)medium sys.dm_audit_class_type_mapmedium sys.dm_audit_class_type_map (transact-sql)medium sys.dm_cryptographic_provider_algorithmsmedium sys.dm_cryptographic_provider_algorithms (transact-sql)medium sys.dm_cryptographic_provider_keysmedium sys.dm_cryptographic_provider_keys (transact-sql)medium sys.dm_cryptographic_provider_propertiesmedium sys.dm_cryptographic_provider_properties (transact-sql)medium sys.dm_cryptographic_provider_sessionsmedium sys.dm_cryptographic_provider_sessions (transact-sql)medium sys.dm_database_encryption_keysmedium sys.dm_database_encryption_keys (transact-sql)medium sys.dm_external_provider_certificate_infomedium sys.dm_external_provider_certificate_info (transact-sql)medium sys.dm_server_audit_statusmedium sys.dm_server_audit_status (transact-sql)medium

Server(11)

sys.dm_server_accelerator_statusmedium sys.dm_server_external_policy_actionsmedium sys.dm_server_external_policy_principal_assigned_actionsmedium sys.dm_server_external_policy_principalsmedium sys.dm_server_external_policy_role_actionsmedium sys.dm_server_external_policy_role_membersmedium sys.dm_server_external_policy_rolesmedium sys.dm_server_memory_dumpsmedium sys.dm_server_registrymedium sys.dm_server_servicesmedium sys.dm_server_suspend_statusmedium

Service Broker(4)

sys.dm_broker_activated_tasksmedium sys.dm_broker_connectionsmedium sys.dm_broker_forwarded_messagesmedium sys.dm_broker_queue_monitorsmedium

Transactions(23)

sys.dm_tran_locksmedium sys.dm_tran_active_snapshot_database_transactionsmedium sys.dm_tran_active_snapshot_database_transactions (transact-sql)medium sys.dm_tran_active_transactionsmedium sys.dm_tran_active_transactions (transact-sql)medium sys.dm_tran_current_snapshotmedium sys.dm_tran_current_snapshot (transact-sql)medium sys.dm_tran_current_transactionmedium sys.dm_tran_current_transaction (transact-sql)medium sys.dm_tran_database_transactionsmedium sys.dm_tran_database_transactions (transact-sql)medium sys.dm_tran_distributed_transaction_statsmedium sys.dm_tran_distributed_transaction_stats (transact-sql)medium sys.dm_tran_locks (transact-sql)medium sys.dm_tran_session_transactionsmedium sys.dm_tran_session_transactions (transact-sql)medium sys.dm_tran_top_version_generatorsmedium sys.dm_tran_top_version_generators (transact-sql)medium sys.dm_tran_transactions_snapshotmedium sys.dm_tran_transactions_snapshot (transact-sql)medium sys.dm_tran_version_storemedium sys.dm_tran_version_store (transact-sql)medium sys.dm_tran_version_store_space_usagemedium