1. Dynamic Management Objects Nolan Small

2. Introduction • Introduced in SQL Server 2005 • Have accessed more and more statistics during each release up to and including SQL 2012 • Includes both Dynamic Management Views and Dynamic Management Functions • Automatically collected by SQL Server

3. Querying Server Performance Statistics • Sys.dm_as_wait_stats • Sys.dm_as_waiting_tasks • Combining for over 600 wait stats A couple of Examples of Wait stats LCK_M_* Increasing values indicate DATA CONTENTION SOS_SCHEDULER_YIELD increasing values indicates CPU PRESSURE

4. Querying Server Performance Statistics • Sys.dm_os_wait_stats displays an aggregate values of waits • Sys.dm_os_wait_tasks displays ‘real-time’ value of waits. • Example Query • select top 10 wait_type, • wait_time_ms, • Percentage = 100 * wait_time_ms/sum(wait_time_ms) OVER() • From sys.dm_os_wait_statswt • where wt.wait_type NOT LIKE '%SLEEP%' • order by Percentage desc

5. Querying Server Information • Another example of a DMV server query • Select * From sys.dm_server_services; Sys.dm_exec_sql_text is considered to be a DMF server query and can pull the actual statement text for the request generating the wait

6. In Summary • There are hundreds of DMO objects and some database administrators make a career out of running these various statistics and analyzing the returning data. At times a forthcomming problem can be thwarted by careful analysis and detection of information being brought forward by all of these reporting tools