Dynamic Management Views

Another area to retrieve monitoring information is the Master database, which is where SQL Server stores most of its configuration information. It is not a good idea to directly query the master database because Microsoft could change the structure of the master database from version to version or even in service pack releases. Rather than developers building solutions that rely on the Master database schema and risking changes in a service pack ruining the solution, Microsoft has created a set of dynamic management views and functions.

Dynamic management views and functions return valuable information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. They give administrators an easy way to monitor what SQL Server is doing and how it is performing by providing a snapshot of the exact state of SQL Server at the point they are queried. They replace the need to query the system tables or to use other inconvenient methods of retrieving system information in use prior to SQL Server 2005. SQL Server 2005 introduced DMVs, and the latest release, SQL Server 2008 (and SQL Server 2008 R2), includes additional DMVs.

DMVs and functions are part of the sys schema in the Master database. Administrators can find a list of dynamic views in SQL Server Management Studio under Master, Views, System Views, and the dynamic functions are located under Master, Programmability, Functions, System Functions, Table-valued Functions. Each dynamic object’s name has a dm_ prefix.

For example, earlier in this chapter, in “Managing and Maintaining SQL 2008 R2,” the sys.dm_db_index_physical_stats dynamic management function was used to determine the fragmentation percentage of the indexes for efficient database maintenance.