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