Dynamic Management Views and Functions


SQL Server 2005 introduces a new set of system objects that allow us to see the internal state of SQL Server; Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). The kind of detailed information that is most useful when troubleshooting performance issues can be found through these objects.

All DMVs and DMFs are in the sys schema and are named with a dm_ prefix. Many, if not all, of the DMVs and DMFs are defined in the MSSQLServerResource database and most use a special, internal use only, version of the OpenRowset statement to produce their data. These OpenRowset statements appear as one form or another of a streaming operator in query plans. In most queries against a DMV these operators will end up streaming all of their data – this might not be a good thing on a busy server.

BOL describes the future compatibility status of DMVs and DMFs as follows;

DMVs and DMFs return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, DMVs and DMFs in future releases may not be compatible with the DMVs and DMFs in SQL Server 2005.

Interestingly, the future compatibility statement for catalog views is different;

In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list.

See the BOL entry Dynamic Management Views and Functions for a list of the 12 categories into which the DMVs and DMFs are grouped.

Copyright 2007-2015 RMJCS Ltd