1 / 6

Dynamic Management Objects

Dynamic Management Objects. Nolan Small. 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.

cheng
Download Presentation

Dynamic Management Objects

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related