Dynamic management views (DMVs), which were introduced in SQL Server 2005, provide a simple means for assessing the state of a server. These views provide a lightweight means for gathering diagnostic information without the heavy burden associated with the tools available in SQL Server 2000. The SQL Server 2000 diagnostic tools, such as heavy Profiler traces, PerfMon,
dbcc executions, and
pssdiag, are still available, but oftentimes, the information returned from the DMVs is enough to determine what may be ailing a SQL Server machine.
An extensive number of DMVs are available in SQL Server 2008. Some DMVs are scoped at the server level, and others are scoped at the database level. They are all found in the
sys schema ...