One my most favorite features of SQL Server 2005 is the introduction of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). They allow the DBA to take a peek into the internal workings of SQL Server at both the server and database levels. Using DMVs and DMFs, the DBA can better monitor the health of SQL Server, better tune SQL Server, and troubleshoot many kinds of problems. In many cases, they replace the need to use system tables and other obscure methods to find out what is happening inside SQL Server. And in many other cases, they provide new insights into SQL Server internals that have never been available before to DBAs.
While DMVs and DMFs provide a wealth of information, the information they provide is often esoteric or difficult to understand. Because of this, there is a fairly steep learning curve when using them. In addition, you must use Transact-SQL to SELECT the results you want, so you must have a basic understanding of how to write SELECT statements in order to take full advantage of them.
The purpose of this article is to introduce you to them at a high level; in later articles, I will drill down into how specific DMVs and DMFs can be used to help you performance tune your servers and databases.
The Basics
There are two different kinds of DMVs and DMFs:
- Server-scoped: These look at the state of an entire SQL Server instance.
- Database-scoped: These look at the state of a specific database.
All DMVs and DMFs exist in the master database and belong to the sys schema. They also follow the naming convention of dm_*, such as:
sys.dm_db_index_usage_stats
DMVs can be referred to in Transact-SQL using the DMVs two-part, three-part, or four-part name. DMFs have to be referred to using either their two-part or three-part names in code. Neither DMVs nor DMFs can be referred to with their one-part name. The two-part name example above is the most common way to refer to DMVs and DMFs.
To access a DMV or DMF, the user must have SELECT permission on the specific DMV or DMF they want to access and must have either VIEW SERVER STATE or VIEW DATABASE STATE permission, depending on if the DMV or DMF is server- or database-based. By default, members of the sysadmin group have these permissions.
To view the data provided by a DMV or DMF, you use the SELECT statement. Here’s a simple example:
SELECT * FROM sys.dm_db_index_usage_stats
This produces results like the following:
Figure: This output from the sys.dm_db_index_usage_stats DMV has been greatly abbreviated to fit the available space.
As you can see just from this one example, results from a DMV or DMF can be very detailed, but also very difficult to interpret without a little, or a lot of help. To be able to fully understand the capabilities of DMVs and DMFs, you will need to read up on each one and experiment with them to see if the information they provide is beneficial to you. In some cases, you may have to write some very complex Transact-SQL to produce the results you need. Books Online has some examples of Transact-SQL code you can use for various DMVs and DMFs.
Classes of DMVs and DMFs
Because there are so many DMVs and DMFs available, Microsoft has grouped them into categories. They include:
- Common Language Runtime Related Dynamic Management Views
- I/O Related Dynamic Management Views and Functions
- Database Mirroring Related Dynamic Management Views
- Query Notifications Related Dynamic Management Views
- Database Related Dynamic Management Views
- Replication Related Dynamic Management Views
- Execution Related Dynamic Management Views and Functions
- Service Broker Related Dynamic Management Views
- Full-Text Search Related Dynamic Management Views
- SQL Server Operating System Related Dynamic Management Views
- Index Related Dynamic Management Views and Functions
- Transaction Related Dynamic Management Views and Functions
Each of the above categories has many different DMVs and DMFs. While we don’t have time in this article to look at them all, let’s take a quick look at those that fall in the Index Related Dynamic Management Views and Functions category, one of the most useful categories of DMVs and DMFs used in performance tuning. They include:
- sys.dm_db_index_operational_stats
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
- sys.dm_db_missing_index_columns
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_group_stats
- sys.dm_db_missing_index_groups
While the name of the DMV or DMF is somewhat useful when figuring out what information they provide, you will find yourself looking these up in Books Online, or just experimenting by trail and error, to find out what information they can provide you.
Summary
DMVs and DMFs, along with data provided by Management Studio, System Monitor, and Profiler, provide DBAs with a wealth of information they can use to troubleshoot and correct SQL Server performance problems, in addition to many other SQL Server-related problems. If you have not yet done so, take some time and look up the more interesting DMVs and DMFs in Books Online and give them a try.
]]>