Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 20 May 2015

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