SQL Server 2005 DMVs: Exposing the state of your server

Ever want to find out which connections running T-SQL are blocking other connections? Sure, you could run sp_who or query the syslockinfo table, but what if you wanted more information to help debug the problem? For instance, how would you determine which T-SQL the blocking process was running or which resource the blocking process was waiting on?

Alas, the answers to these questions for older versions of SQL Server did not come easily. Historically, the database management system (DBMS) has not been known for its transparency. Some visibility was afforded by system tables, a bit more by DBCC (Database Console Commands) and still more through various undocumented channels. But to put these resources together into a single query to answer even seemingly simple questions was a gargantuan task.

Enter Database Management Views

If you read my previous tip on SQL Server 2005 Catalog Views, you know that system tables have received a major upgrade in the latest version of the DBMS. But the most interesting component of this upgrade is a piece called Dynamic Management Views (DMVs.) Whereas Catalog Views allow you to retrieve information about static database objects, such as tables or stored procedures, DMVs expose information about more dynamic information.

Now you have access to information that was previously only accessible through performance counters; by using the DMVs, you can find information in much greater detail and be able to do much more with it. For instance, there are five SQL Server performance counters that relate to the query plan cache and several more that apply to the buffer cache. But these counters are aggregate and can't tell you, for example, which plans are cached or the number of disk I/Os incurred due to each buffer cache entry. DMVs can. See the sys.dm_exec_cached_plans view for information on the query plan cache, and sys.dm_os_memory_cache_entries for extremely detailed information about buffer caches.

Like performance counters, information retrieved via DMVs should be considered stale the moment it is accessed. The data exposed by these views can change as often as thousands of times per second depending on the view being queried. So the key to reading and understanding output over time is sampling. If you need to monitor for certain conditions, don't try to capture the state of the DMV at every moment. Instead, take regular snapshots and look for averages or changing trends. Taking less data will both keep you sane and prevent data collection efforts from bringing your server to its knees.

Using Database Management Views

So where can you find these powerhouse views? Start SQL Server Management Studio and open the Object Explorer. Navigate to the "master" database and expand the Views hierarchy, then expand System Views. Scroll down until you see views prefixed with dm_ -- these are the DMVs. They also come in the form of table-valued functions. Expand the Programmability hierarchy, then look under Functions, System Functions and finally Table-valued Functions. You'll see the related Dynamic Management Functions. These functions are used for specific helper tasks in conjunction with the views.

The first thing you'll notice is that DMV names are based on the 10 categories into which they fall. For instance, dm_broker views contain information about SQL Service Broker; dm_fts views expose information to help administrate Full Text Search. But don't think that information from one category can't be joined to information from another category -- this is the real power of the DMVs.

Let's revisit a question asked earlier in this tip: How would you identify not only the T-SQL being executed by a blocking process, but also the resource that process is waiting on?

Start with the "exec" (execution) category of views, and specifically the sys.dm_exec_requests view. Identify blocked transactions by looking at the blocking_session_id column. If the column is non-NULL, the session identified by the session_id column is being blocked by the session identified by the blocking_session_id column (see Microsoft Books Online for more detailed descriptions). Now join back to the same view, querying to get the value of the sql_handle column for the blocking session. Use this column as input for the sys.dm_exec_sql_text function to retrieve the T-SQL connection that's blocking the other process.

Now, how do you get the blocked resource? Jump over to the "os" (SQLOS) category of views and query the sys.dm_os_waiting_tasks view. That view happens to have a column called session_id, which -- surprise, surprise -- you can use to correlate rows to the blocking_session_id column of sys.dm_exec_requests.

How do you put this together to find out which queries are currently blocking other queries, and what resources they're waiting on? The following query is a good place to start:

SELECT
 blocked_query.session_id AS blocked_session_id,
 blocking_query.session_id AS blocking_session_id,
 sql_text.text AS blocking_text,
 waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
 blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
 SELECT *
 FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) sql_text
JOIN sys.dm_os_waiting_tasks waits ON
 waits.session_id = blocking_query.session_id

Unfortunately, there is only enough room in this tip to scratch the surface of the power the DMVs have to offer. Watch this space for upcoming tips that will cover more in-depth scenarios to help you track down tricky performance and utilization issues. In the meantime, start exploring! This level of visibility into the inner workings of the DBMS is new terrain for SQL Server DBAs, and the uses for this information are countless.

About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.

More information from SearchSQLServer.com