Dynamic Management Views(DMV's) in SQL Server

Dynamic Management Views(DMV's)

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.

1)To See Wait type using DMVs:

1)SELECT DiSTINCT wait_type

FROM sys.dm_os_wait_stats;

2)To check Currently Running Query/Statements in SQL Server:

1)sys.dm_exec_sessions

2)sys.dm_exec_requests

3)To check index details using DMV’s:

sys.dm_db_missing_index_details ----to check missing indexes.

sys.dm_db_missing_index_columns ----to check missing index columns

sys.dm_db_index_usage_stats ----to check index usage

4)To check I/O related info using DMV’s

sys.dm_io_pending_io_requests

5) To check Object Related info using DMV’s

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

6)To see Replication related info using DMV’s:

sys.dm_repl_traninfo

sys.dm_repl_schemas

sys.dm_repl_articles

7) To check audit related info using DMV’s:

sys.dm_audit_actions (Transact-SQL)

8)To check Transaction related info using DMV’s:

sys.dm_tran_locks

sys.dm_tran_database_transactions

sys.dm_tran_active_transactions

9)To check Operationg Systems info using DMV’s:

sys.dm_os_performance_counters

sys.dm_os_memory_objects

sys.dm_os_process_memory 

sys.dm_os_waiting_tasks