SQL Server – Some commonly used DMV/DMFs by most of the DBAs

There are more than 150 DMV/DMFs spread across 20 some categories in 2012, and no one remember all these by heart.  Below are some commonly used DMV/DMFs by most the DBAs.


— Execution related DMV and DMF

— Current Connections (User Processes only)
SELECT * FROM sys.dm_exec_connections

— Active Sessions
SELECT * FROM sys.dm_exec_sessions

— Currently executing requests
SELECT * FROM sys.dm_exec_requests

— Very Important: Query Statistics
SELECT * FROM sys.dm_exec_query_stats

— Cached Plans
SELECT * FROM sys.dm_exec_cached_plans

— Very Important: Query Optimizer details
SELECT * FROM sys.dm_exec_query_optimizer_info

— Very Important: Query Plan, This is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_query_plan (T1.plan_handle )

— Very Important: SQL Statement, this is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_SQL_text (T1.SQL_handle )

— Index related Dynamic Management Objects
— Index Usability
SELECT * FROM sys.dm_db_index_usage_stats

— Missing Indexes
SELECT * FROM sys.dm_db_missing_index_details

— Physical Stats, this is DMF, requires DBID, ObjectID, IndexID, PartitionID and Mode
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(‘TESTDB‘), OBJECT_ID(‘EMPLOYEE‘), NULL, NULL , NULL) WHERE avg_fragmentation_in_percent >30

— SQL Server Operating system related Dynamic Management Objects

— Wait Stats
SELECT * FROM sys.dm_os_wait_stats

— Scheduler
SELECT * FROM sys.dm_os_schedulers

— Waiting/ Suspended Tasks
SELECT * FROM sys.dm_os_waiting_tasks WHERE Session_ID > 50

— Performance Counters
SELECT * FROM sys.dm_os_performance_counters

— Cluster Nodes Details
SELECT * FROM sys.dm_os_cluster_nodes