The DMVs aren’t based on real tables
stored in database files but are based on internal server structures. The objects are separated into several categories based on the
functional area of the information they expose.
They are all in the sys schema and have a name that starts with
dm_,
followed by a code indicating the area of the server with which the object
deals. The main categories we’ll address are the following:
dm_exec_*
Contains
information directly or indirectly related to the execution of user code
and associated connections. For example,
sys.dm_exec_sessions returns one row per authenticated session on SQL Server. This
object contains much of the same information that sysprocesses
contains but has even more information about the operating environment of each session.
dm_os_*
Contains
low-level system information such as memory, locking, and scheduling. For
example, sys.dm_os_schedulers is a DMV that returns one row per scheduler. It is primarily
used to monitor the condition of a scheduler or to identify runaway tasks.
dm_tran_*
Contains
details about current transactions. For example, sys.dm_tran_locks returns
information about currently active lock resources. Each row represents a
currently active request to the lock management component for a lock that has
been granted or is waiting to be granted.
dm_io_*
Keeps
track of I/O activity on networks and disks. For example, the function sys.dm_io_virtual_fi
le_stats returns I/O statistics for data and log
fi les.
dm_db_*
Contains details about databases and
database objects such as indexes. For example, sys.dm_db_index_physical_stats is a function that returns size and
fragmentation information for the data and indexes of the specified table or
view.
