Introducing Dynamic Management Objects (DMO) in Sqlserver

Dynamic Management Objects (DMO)

Dynamic Management Views(DMV) ,  Dynamic Management Functions(DMF)

Execution :  Starts with sys.dm_exec

1 . sys.dm_exec_connections ( Get a count of SQL connections by IP address)

2. Session Ownership (sys.dm_exec_sessions)

3. Current expensive, or blocked, requests (sys.dm_exec_requests ,sys.dm_exec_sql_text)

4 .Query Stats – Find the “top X” most expensive cached queries  (sys.dm_exec_query_stats)

5 How many single-use ad-hoc Plans? (sys.dm_exec_cached_plans)

6 DMV#6: Ad-hoc queries and the plan cache (sys.dm_exec_cached_plans and sys.dm_exec_sql_text)

7 Investigate expensive cached stored procedures ( sys.dm_exec_procedure_stats )

8  Find Queries that are waiting, or have waited, for a Memory Grant (sys.dm_exec_query_memory_grants)

Transactions : begin with “sys.dm_tran_”.

1. Monitor long-running transactions (    sys.dm_tran_database_transactions ,  sys.dm_tran_session_transactions)

2. Identify locking and blocking issues (    sys.dm_tran_locks )

Database and Indexes:  Starts with sys.dm_db_

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details
4. Interrogate Index usage            sys.dm_db_index_usage_stats,
5. Table Storage Stats (Pages and Row Counts)        sys.dm_db_partition_stats
6. Monitor TempDB         sys.dm_db_file_space_usage

Disk I/O

1.  sys.dm_exec_query_stats – IO that a given query has cost over the times it had been executed

2.  sys.dm_exec_connections – IO that has taken place on that connection

3.  sys.dm_exec_sessions – IO that has taken place during that session

4.  sys.dm_os_workers – IO that is pending for a given worker thread

5.  Investigate Disk Bottlenecks via I/O Stalls ( sys.dm_io_virtual_file_stats)

6.  Investigate Disk Bottlenecks via Pending I/O  (sys.dm_io_pending_io_requests)

Operating System :  “sys.dm_os_”,

1. sys.dm_os_wait_stats(Returns information about all the waits encountered by threads that executed)
2. sys.dm_os_performance_counters(exposes the PerfMon counters)
3. sys.dm_os_sys_info  (Basic CPU Configuration)
4. sys.dm_os_ring_buffers(CPU Utilization History)
5. sys.dm_os_schedulers (Monitor Schedule activity)
6. sys.dm_os_sys_memory  (System-wide Memory Usage)
7. sys.dm_os_sys_memory  (System-wide Memory Usage)
8. sys.dm_os_memory_cache_counters (Investigate Memory Usage Across all Caches)
9. sys.dm_os_memory_cache_counters (Investigate Memory Usage Across all Caches)

Miscellaneous

1. sys.dm_clr_tasks Rooting out Unruly CLR Tasks
2. sys.dm_fts_active_catalogs Full Text Search
3. dm_db_mirroring_auto_page_repair Page Repair attempts in Database Mirroring

 

Download the pdf : http://ahref=

Download the word document : Important Notes

http://ahref=