An Introduction to Dynamic Management Views and Functions in SQL Server 2005

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.

 1/3    1 2 3 下一页 尾页