Monday, April 5, 2010

DMVs Related to Disk I/O

Objective: Designing a Monitoring Strategy

Sub-objective: Design a monitoring solution at the operating system level

This is a list of DMVs I have encountered in my studies of I/O as it pertains to SQL Server. I have included a brief description from the MSDN article as well as link to the article itself. These will be highly important, not only from a certification level, but also from a practical level. This list is in DBA Survivor by Thomas LaRock and many of them are also found in sources like SQL Server 2008 Administration in Action by Rod Colledge and the chapter entitled My Favorite DMVs and Why by Aaron Bertrand in MVP Deep Dives.

DMV NameFunction
sys.dm_db_file_space_usageReturns space usage information for each file in the database.
sys.dm_db_index_operational_statsReturns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
sys.dm_db_index_usage_statsReturns counts of different types of index operations and the time each type of operation was last performed.
sys.dm_exec_query_statsReturns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
sys.dm_exec_query_planReturns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_io_virtual_file_statsReturns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
sys.dm_io_pending_io_requestsReturns a row for each pending I/O request in SQL Server.
sys.dm_os_wait_statsReturns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
sys.dm_os_waiting_tasksReturns information about the wait queue of tasks that are waiting on some resource.


Chrissy LeMaire said...

Very nice, thanks for the list!

Post a Comment