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 Name	Function
sys.dm_db_file_space_usage	Returns space usage information for each file in the database.
sys.dm_db_index_operational_stats	Returns 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_stats	Returns counts of different types of index operations and the time each type of operation was last performed.
sys.dm_exec_query_stats	Returns 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_plan	Returns 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_stats	Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
sys.dm_io_pending_io_requests	Returns a row for each pending I/O request in SQL Server.
sys.dm_os_wait_stats	Returns 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_tasks	Returns information about the wait queue of tasks that are waiting on some resource.