Dynamic Management Views

Another area to retrieve monitoring information is the Master database; this 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 any changes in a service pack messing up the solution, Microsoft instead 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 Servers are doing and how they are performing by providing a snapshot of the exact state of the SQL Servers 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 useful DMVs.

Whenever an instance is started, SQL Server starts saving state and diagnostic data into DMVs. When an instance is restarted, the information is flushed from the views, and new data begins to be loaded.

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, later in this chapter, the sys.dm_db_index_physical_stats dynamic management function will be used to determine the fragmentation percentage of the indexes for efficient database maintenance.