Skip to content

SQL Server Dynamic Management Views (DMVs)

    What are Dynamic Management Views (DMVs)

    Another tool at your disposal to measure performance and view details about the SQL Server is the DMVs.  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

    There are two different kinds of DMVs and DMFs:

    • Server-scoped: These look at the state of an entire SQL Server instance.
    • Database-scoped: These look at the state of a specific database
    • While the DMV can be used like the select statement, the DMF requires a parameter
    • Some apply to entire server and are stored in the master database while others to each database
    • Over 200 DMVs at this time

    The DMVs are broken down into the following categories:

    • Change Data Capture Related Dynamic Management Views
    • Change Tracking Related Dynamic Management Views
    • Common Language Runtime Related Dynamic Management Views
    • Database Mirroring Related Dynamic Management Views
    • Database Related Dynamic Management Views
    • Execution Related Dynamic Management Views and Functions
    • Extended Events Dynamic Management Views
    • Full-Text Search Related Dynamic Management Views
    • Filestream-Related Dynamic Management Views (Transact-SQL)
    • I/O Related Dynamic Management Views and Functions
    • Index Related Dynamic Management Views and Functions
    • Object Related Dynamic Management Views and Functions
    • Query Notifications Related Dynamic Management Views
    • Replication Related Dynamic Management Views
    • Resource Governor Dynamic Management Views
    • Service Broker Related Dynamic Management Views
    • SQL Server Operating System Related Dynamic Management Views
    • Transaction Related Dynamic Management Views and Functions
    • Security Related Dynamic Management Views

    A few common examples of DMVs:

    --view all DMVs
    
    SELECT name, type, type_desc
    FROM sys.system_objects
    WHERE name LIKE 'dm_%'
    ORDER BY 2 desc
    
    
    SELECT * FROM SYS.dm_os_memory_allocations               --<< Notice that all DMVs starts with SYS.DM
    SELECT * FROM SYS.dm_db_xtp_nonclustered_index_stats   
    SELECT * FROM SYS.dm_db_mirroring_past_actions
    SELECT * FROM SYS.dm_xe_session_object_columns
    SELECT * FROM SYS.dm_os_loaded_modules
    SELECT * FROM SYS.dm_db_task_space_usage
    SELECT * FROM SYS.dm_os_memory_objects
    SELECT * FROM SYS.dm_audit_class_type_map
    SELECT * FROM SYS.dm_os_schedulers
    SELECT * FROM SYS.dm_os_server_diagnostics_log_configurations
    SELECT * FROM SYS.dm_hadr_instance_node_map
    SELECT * FROM SYS.dm_io_cluster_valid_path_names
    SELECT * FROM SYS.dm_os_dispatcher_pools
    SELECT * FROM SYS.dm_xtp_transaction_stats
    SELECT * FROM SYS.dm_exec_query_profiles
    SELECT * FROM SYS.dm_os_threads
    SELECT * FROM SYS.dm_repl_tranhash
    SELECT * FROM SYS.dm_hadr_cluster
    SELECT * FROM SYS.dm_qn_subscriptions
    SELECT * FROM SYS.dm_db_session_space_usage
    SELECT * FROM SYS.dm_xtp_gc_stats
    SELECT * FROM SYS.dm_exec_query_optimizer_info
    SELECT * FROM SYS.dm_xe_map_values
    SELECT * FROM SYS.dm_db_xtp_index_stats
    SELECT * FROM SYS.dm_tran_top_version_generators
    SELECT * FROM SYS.dm_fts_fdhosts
    SELECT * FROM SYS.dm_xe_sessions
    SELECT * FROM SYS.dm_db_log_space_usage
    SELECT * FROM SYS.dm_hadr_name_id_map
    SELECT * FROM SYS.dm_os_waiting_tasks
    SELECT * FROM SYS.dm_exec_background_job_queue
    SELECT * FROM SYS.dm_resource_governor_resource_pool_volumes
    SELECT * FROM SYS.dm_os_hosts
    SELECT * FROM SYS.dm_os_memory_brokers
    SELECT * FROM SYS.dm_exec_requests
    SELECT * FROM SYS.dm_tran_commit_table
    SELECT * FROM SYS.dm_db_missing_index_details
    SELECT * FROM SYS.dm_clr_properties
    SELECT * FROM SYS.dm_os_sublatches
    SELECT * FROM SYS.dm_os_buffer_pool_extension_configuration
    SELECT * FROM SYS.dm_exec_query_memory_grants
    SELECT * FROM SYS.dm_fts_outstanding_batches
    SELECT * FROM SYS.dm_logpool_hashentries
    SELECT * FROM SYS.dm_os_wait_stats
    SELECT * FROM SYS.dm_os_memory_node_access_stats
    SELECT * FROM SYS.dm_os_spinlock_stats
    SELECT * FROM SYS.dm_database_encryption_keys
    SELECT * FROM SYS.dm_db_xtp_checkpoint_stats
    SELECT * FROM SYS.dm_hadr_availability_replica_states
    SELECT * FROM SYS.dm_broker_connections
    SELECT * FROM SYS.dm_db_mirroring_auto_page_repair
    SELECT * FROM SYS.dm_server_registry
    SELECT * FROM SYS.dm_tran_current_snapshot
    SELECT * FROM SYS.dm_os_dispatchers
    SELECT * FROM SYS.dm_os_stacks
    SELECT * FROM SYS.dm_db_xtp_object_stats
    SELECT * FROM SYS.dm_filestream_non_transacted_handles
    SELECT * FROM SYS.dm_xe_session_targets
    SELECT * FROM SYS.dm_fts_memory_buffers
    SELECT * FROM SYS.dm_fts_index_population
    SELECT * FROM SYS.dm_tran_current_transaction
    SELECT * FROM SYS.dm_os_cluster_properties
    SELECT * FROM SYS.dm_os_child_instances
    SELECT * FROM SYS.dm_exec_connections
    SELECT * FROM SYS.dm_server_memory_dumps
    SELECT * FROM SYS.dm_xtp_threads
    SELECT * FROM SYS.dm_exec_background_job_queue_stats
    SELECT * FROM SYS.dm_os_memory_broker_clerks
    SELECT * FROM SYS.dm_filestream_file_io_handles
    SELECT * FROM SYS.dm_xtp_transaction_recent_rows
    SELECT * FROM SYS.dm_hadr_availability_replica_cluster_nodes
    SELECT * FROM SYS.dm_fts_active_catalogs
    SELECT * FROM SYS.dm_tran_database_transactions
    SELECT * FROM SYS.dm_filestream_file_io_requests
    SELECT * FROM SYS.dm_cdc_log_scan_sessions
    SELECT * FROM SYS.dm_os_memory_cache_clock_hands
    SELECT * FROM SYS.dm_repl_schemas
    SELECT * FROM SYS.dm_db_mirroring_connections
    SELECT * FROM SYS.dm_audit_actions
    SELECT * FROM SYS.dm_hadr_availability_group_states
    SELECT * FROM SYS.dm_os_ring_buffers
    SELECT * FROM SYS.dm_db_xtp_table_memory_stats
    SELECT * FROM SYS.dm_db_missing_index_groups
    SELECT * FROM SYS.dm_hadr_cluster_members
    SELECT * FROM SYS.dm_db_uncontained_entities
    SELECT * FROM SYS.dm_exec_cached_plans
    SELECT * FROM SYS.dm_hadr_availability_replica_cluster_states
    SELECT * FROM SYS.dm_exec_sessions
    SELECT * FROM SYS.dm_os_memory_clerks
    SELECT * FROM SYS.dm_hadr_auto_page_repair
    SELECT * FROM SYS.dm_db_xtp_memory_consumers
    SELECT * FROM SYS.dm_repl_articles
    SELECT * FROM SYS.dm_xe_session_events
    SELECT * FROM SYS.dm_broker_forwarded_messages
    SELECT * FROM SYS.dm_resource_governor_resource_pools
    SELECT * FROM SYS.dm_db_xtp_checkpoint_files
    SELECT * FROM SYS.dm_db_partition_stats
    SELECT * FROM SYS.dm_io_pending_io_requests
    SELECT * FROM SYS.dm_xtp_system_memory_consumers
    SELECT * FROM SYS.dm_hadr_cluster_networks
    SELECT * FROM SYS.dm_os_nodes
    SELECT * FROM SYS.dm_tcp_listener_states
    SELECT * FROM SYS.dm_os_memory_cache_entries
    SELECT * FROM SYS.dm_os_virtual_address_dump
    SELECT * FROM SYS.dm_os_memory_cache_hash_tables
    SELECT * FROM SYS.dm_cdc_errors
    SELECT * FROM SYS.dm_resource_governor_configuration
    SELECT * FROM SYS.dm_exec_query_stats
    SELECT * FROM SYS.dm_fts_semantic_similarity_population
    SELECT * FROM SYS.dm_clr_tasks
    SELECT * FROM SYS.dm_db_xtp_hash_index_stats
    SELECT * FROM SYS.dm_os_worker_local_storage
    SELECT * FROM SYS.dm_db_persisted_sku_features
    SELECT * FROM SYS.dm_os_sys_memory
    SELECT * FROM SYS.dm_cryptographic_provider_properties
    SELECT * FROM SYS.dm_tran_transactions_snapshot
    SELECT * FROM SYS.dm_os_buffer_descriptors
    SELECT * FROM SYS.dm_tran_active_snapshot_database_transactions
    SELECT * FROM SYS.dm_server_services
    SELECT * FROM SYS.dm_tran_active_transactions
    SELECT * FROM SYS.dm_db_file_space_usage
    SELECT * FROM SYS.dm_broker_activated_tasks
    SELECT * FROM SYS.dm_broker_queue_monitors
    SELECT * FROM SYS.dm_os_memory_cache_counters
    SELECT * FROM SYS.dm_tran_session_transactions
    SELECT * FROM SYS.dm_clr_appdomains
    SELECT * FROM SYS.dm_db_xtp_gc_cycle_stats
    SELECT * FROM SYS.dm_exec_trigger_stats
    SELECT * FROM SYS.dm_os_memory_pools
    SELECT * FROM SYS.dm_os_latch_stats
    SELECT * FROM SYS.dm_io_backup_tapes
    SELECT * FROM SYS.dm_db_xtp_merge_requests
    SELECT * FROM SYS.dm_resource_governor_workload_groups
    SELECT * FROM SYS.dm_hadr_database_replica_states
    SELECT * FROM SYS.dm_fts_memory_pools
    SELECT * FROM SYS.dm_resource_governor_resource_pool_affinity
    SELECT * FROM SYS.dm_os_sys_info
    SELECT * FROM SYS.dm_tran_locks
    SELECT * FROM SYS.dm_exec_procedure_stats
    SELECT * FROM SYS.dm_hadr_database_replica_cluster_states
    SELECT * FROM SYS.dm_exec_query_transformation_stats
    SELECT * FROM SYS.dm_exec_query_resource_semaphores
    SELECT * FROM SYS.dm_repl_traninfo
    SELECT * FROM SYS.dm_db_missing_index_group_stats
    SELECT * FROM SYS.dm_fts_population_ranges
    SELECT * FROM SYS.dm_os_performance_counters
    SELECT * FROM SYS.dm_os_workers
    SELECT * FROM SYS.dm_xe_session_event_actions
    SELECT * FROM SYS.dm_db_script_level
    SELECT * FROM SYS.dm_server_audit_status
    SELECT * FROM SYS.dm_io_cluster_shared_drives
    SELECT * FROM SYS.dm_os_tasks
    SELECT * FROM SYS.dm_db_fts_index_physical_stats
    SELECT * FROM SYS.dm_xe_packages
    SELECT * FROM SYS.dm_logpool_stats
    SELECT * FROM SYS.dm_os_memory_nodes
    SELECT * FROM SYS.dm_tran_version_store
    SELECT * FROM SYS.dm_os_windows_info
    SELECT * FROM SYS.dm_os_cluster_nodes
    SELECT * FROM SYS.dm_xtp_gc_queue_stats
    SELECT * FROM SYS.dm_os_process_memory
    SELECT * FROM SYS.dm_xe_objects
    SELECT * FROM SYS.dm_xe_object_columns
    SELECT * FROM SYS.dm_db_xtp_transactions
    SELECT * FROM SYS.dm_clr_loaded_assemblies
    SELECT * FROM SYS.dm_db_index_usage_stats
    
    
       /* DMV to list all empty tables in your database. */
       Use AdventureWorks2012
       go
    
    
       ;WITH Empty AS     
       (
        SELECT 
     OBJECT_NAME(OBJECT_ID) [Table],
     SUM(row_count) [Records]
        FROM 
     sys.dm_db_partition_stats      
        WHERE 
     index_id = 0 OR index_id = 1      
        GROUP BY 
     OBJECT_ID      
       )      
    
       SELECT [Table],Records 
       FROM [Empty]      
       WHERE [Records] = 0
    
    /*
    dmvs for indexes, slow running quieries, missing indexes, statistice, 
    counters, cpu, memoru, I/), physical disk sessions, users, security, 
    database info 
    */
    Select * from sys.dm_exec_connections
    Select * from sys.dm_exec_sessions
    Select * from sys.dm_exec_requests
    Select * from sys.dm_db_index_usage_stats
    Select * from sys.dm_db_missing_index_group_stats
    Select * from sys.dm_os_performance_counters
    select * from sys.dm_os_sys_memory
    
    --allow the DBA to identify where the bulk of the connections originate
    SELECT dec.client_net_address ,
    des.program_name ,
    des.host_name ,
    --des.login_name  
    COUNT(dec.session_id) AS connection_count
    FROM sys.dm_exec_sessions AS des
    INNER JOIN sys.dm_exec_connections AS dec
    ON des.session_id = dec.session_id
    -- WHERE LEFT(des.host_name, 2) = 'WK'
    GROUP BY dec.client_net_address ,
    des.program_name ,
    des.host_name
    -- des.login_name
    -- HAVING COUNT(dec.session_id) > 1
    ORDER BY des.program_name,
    dec.client_net_address ;
    
    --who are directly connected to the SQL Server instance
    
    SELECT dec.client_net_address ,
    des.host_name ,
    dest.text
    FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec
    ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE des.program_name LIKE 'Microsoft SQL Server Management Studio%'
    ORDER BY des.program_name ,
    dec.client_net_address
    
    --Find indexes for database
    use AdventureWorks2012
    go
    
    SELECT DB_NAME(ddius.[database_id]) AS database_name ,
    OBJECT_NAME(ddius.[object_id], DB_ID('AdventureWorks2012')) --<< replace db name
    AS [object_name] ,
    asi.[name] AS index_name ,
    ddius.user_seeks + ddius.user_scans + ddius.user_lookups AS user_reads
    FROM sys.dm_db_index_usage_stats ddius
    INNER JOIN AdventureWorks2012.sys.indexes asi
    ON ddius.[object_id] = asi.[object_id]
    AND ddius.index_id = asi.index_id ;
    
    --userful DMV for determining usage of index
    
    SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,
    ddius.index_id ,
    ddius.user_seeks ,
    ddius.user_scans ,
    ddius.user_lookups ,
    ddius.user_seeks + ddius.user_scans + ddius.user_lookups
    AS user_reads ,
    ddius.user_updates AS user_writes ,
    ddius.last_user_scan ,
    ddius.last_user_update
    FROM sys.dm_db_index_usage_stats ddius
    WHERE ddius.database_id > 4 -- filter out system tables
    AND OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
    AND ddius.index_id > 0 -- filter out heaps
    ORDER BY ddius.user_scans DESC
    
    -- List unused indexes
    
    SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
    i.name
    FROM sys.indexes AS i
    INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE i.index_id NOT IN ( SELECT ddius.index_id
    FROM sys.dm_db_index_usage_stats AS ddius
    WHERE ddius.[object_id] = i.[object_id]
    AND i.index_id = ddius.index_id
    AND database_id = DB_ID() )
    AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
    
    --STOP
    --Current Running Transaction
    
    use master 
    SELECT 
    SPID,ER.percent_complete,
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
    + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
    + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
    + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
    ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
    DB_NAME(SP.DBID) AS DBNAME,
    SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
    ((CASE ER.statement_end_offset 
    WHEN -1 THEN DATALENGTH(est.text)
    ELSE ER.statement_end_offset
    END - ER.statement_start_offset)/2) + 1) AS QueryText,
    TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
    SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
    FROM SYSPROCESSES SP
    INNER JOIN 
    sys.dm_exec_requests ER
    ON sp.spid = ER.session_id
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
    
    --Run following query to find longest running query using T-SQL
    SELECT DISTINCT TOP 3
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    --ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn--,
    --ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
    ORDER BY
    s.max_elapsed_time DESC
    GO
    
    -- Top 5 worst performing Queries 
    SELECT 
    TOP 5 obj.name, max_logical_reads, max_elapsed_time 
    FROM 
    sys.dm_exec_query_stats a CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle) hnd INNER JOIN 
    sys.sysobjects obj on hnd.objectid = obj.id 
    ORDER BY 
    max_logical_reads DESC
    
    --TOP 5 CPU-CONSUMING STATEMENTS 
    SELECT TOP 5
    qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins],
    qs.execution_count,
    qs.min_worker_time/60000000 as [Min CPU Time in mins],
    --qs.total_worker_time/qs.execution_count,
    SUBSTRING(qt.text,qs.statement_start_offset/2,
    (case when qs.statement_end_offset = -1
    then len(convert(nvarchar(max), qt.text)) * 2
    else qs.statement_end_offset end -qs.statement_start_offset)/2)
    as query_text,
    dbname=db_name(qt.dbid),
    object_name(qt.objectid) as [Object name]
    FROM 
    sys.dm_exec_query_stats qs cross apply 
    sys.dm_exec_sql_text(qs.sql_handle) as qt
    ORDER BY
    [Avg CPU Time in mins] DESC