Introducing Dynamic Management Objects

Performance tuning and troubleshooting in SQL Server has always been something of a black art. To be effective at it you need to know how to use a large set of tools, including Profiler, Perfmon, DBCC commands and stored procedures. Sometimes it can seem almost random which tool you should use for a specific issue. They will often affect performance themselves, so you might not always be able to use them. Another problem is undocumented DBCC commands or system tables. More or less everyone who knows something about tuning, know and use a couple of these. But the advice has always been that you should not use these unless you really know what you are doing or you are told to use them by a Microsoft support professional. For all of these reasons SQL Server 2000 can be seen as a black box that can be quite difficult to penetrate. SQL Server 2005 changes all this by introducing the new Dynamic Management Objects (DMO)*.

Note: Dynamic Management Objects have absolutely nothing to do with SQL-DMO, the old technique for programmatically accessing the server, which has now been replaced by SMO.

The dynamic management objects introduce a very transparent view into what is going on inside SQL Server at any time. There are two types of DMO; Dynamic Management Views (DMV) and Dynamic Management Functions (DMF). These are similar in the same way as any view and function, with the difference being that the DMFs take arguments that refine the output. They both generate tabular resultsets with dynamically generated information regarding the state and health of the server. There are 75 dynamic management views and dynamic management functions fully documented in SQL Server Books Online.

The information returned by the DMOs is collected by the engine itself from lots of different sources and the DMOs make it easy for you to use it by simply querying them using SELECT statements. Since the information shows the current state of the server this means that when the server is restarted the information collected is lost and the engine starts collecting a fresh set.

* I do not know if DMO is actually an officially used abbreviation. The term dynamic management objects is used however in Books Online for referring to dynamic management views and dynamic management functions as a whole.

Categories of DMOs

The DMOs are divided into different categories and named accordingly to be easy to find and distinguish. All DMO names start with the prefix dm_ (no difference between DMVs and DMFs). Next follows a prefix identifying the category a DMO belongs to. For instance, the prefix dm_io_ is used for dynamic management objects that return information regarding disk and network I/O. Another category is dm_clr_ which is used for DMOs that return information regarding the hosting of the CLR (.NET Common Language Runtime) in SQL Server. Below is a listing of the complete set of categories of dynamic management objects.

• dm_broker_* - Service Broker
• dm_clr_* - Common Language Runtime
• dm_db_* - Database related
• dm_db_index_* - Indexes
• dm_db_mirroring_* - Database mirroring
• dm_exec_* - Execution
• dm_fts_* - Full-Text Search
• dm_io_* - I/O
• dm_os_* - SQL Operating System (SQLOS)
• dm_qn_* - Query Notification
• dm_repl_* - Replication
• dm_tran_* - Transactions

The following query lists all the dynamic management objects that are available:

SELECT [name], [type], type_desc
FROM sys.system_objects
WHERE NAME LIKE ‘dm%’
ORDER BY [name]

Viewing a currently executing query

Now, lets have a look at a typical usage of the dynamic management objects. One problem that a DBA sometimes faces is when some connection is executing something that seems to be blocking resources and making the system run slowly. Prior to SQL Server 2005, you needed to have a trace running already in Profiler (or a server-side trace of course) when the blocking statement was executed. A trace started after the connection had already started the execution of the blocking statement would not be able to show you what that connection was actually doing. You could always try DBCC INPUTBUFFER, but depending on the situation you might not get all the information you wanted from it. For instance, it only shows the first 255 characters of the statement that was executed, or if it was a procedure that was executed only the procedure name is shown. Lets see what options SQL Server 2005 and the dynamic management objects gives us in this case.

There are three scripts at the end of this article that you can use to follow the action. Script 1 creates a simple stored procedure that starts a transaction, updates all the rows in a table and then sits back and waits for an hour. Script 2 executes this procedure. Note the session ID for the query window where script 2 is executed. Enter this session ID in Script 3 for the variable @sessionid. The rest of the code in Script 3 is the interesting part. First we query sys.dm_exec_requests using the session id. Some of the output in this dynamic management view should look familiar since it is similar to what sp_who returns in SQL Server 2000. However, there is a lot more information in this dynamic management view. We are particularly interested in two columns here, sql_handle and plan_handle. These are hash values of the SQL text and execution plan respectively used for the request we are looking at. With these handles we can move on and look closer at what this request is doing.

The dynamic management view sys.dm_exec_query_stats can tell us a lot about the amount of resources used by a query plan given a sql_handle or plan_handle. However, we are simply interested in reviewing what the request is doing right now, so we instead use the dynamic management function sys.dm_exec_sql_text. This table-valued function takes a sql_handle as input and returns a table where the column text contains the actual SQL statement(s) that corresponds to that hash. If we pass it the sql_handle we got from sys.dm_exec_requests we get the text of the stored procedure executed in Script 2. But we are not finished yet. There is one further step we can take to really understand what is going on. By passing the plan_handle from sys.dm_exec_requests into the dynamic management function sys.dm_exec_query_plan we can get the actual query plan that was used to execute the request. The output column query_plan contains the query plan in the new showplan XML format. Like all columns that return XML data in SQL Server Management Studio we can click the contents of the column and a new window is opened with the XML data indented and fully readable. Even better, if we save the xml with a .sqlplan extension we can then double-click the saved file and SQL Server Management Studio will show the graphical query plan that the showplan XML describes. At this stage we should be able to tell exactly what is causing the problem.

Summary

The dynamic management objects gives us a very transparent view of what is going on inside various areas of SQL Server 2005. By using them we will be able to query the system for information about it’s current state in a much more effective and easy way than ever before. For performance tuning and troubleshooting they will be an essential tool to use. This article has shown a small glimpse of what they are and how you can use them, now it is time to start taking a look at all the other available dynamic views and functions.

Scripts for demo

Script 1

USE AdventureWorks
GO
CREATE PROCEDURE dbo.updater
AS
BEGIN TRAN
UPDATE Person.Contact WITH (HOLDLOCK)
SET FirstName = 'Chris'
WAITFOR DELAY '01:00:00'
ROLLBACK TRAN
GO

Script 2

-- Execute the line below and note the session ID for the query window executing this part
-- SELECT @@SPID
EXEC dbo.updater

Script 3

DECLARE @sessionid INT
SET @sessionid = 52 -- Change to the session ID script 2 was executed in
DECLARE @sqlhandle VARBINARY(64)
DECLARE @planhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle, @planhandle = plan_handle
FROM sys.dm_exec_requests
WHERE session_id = @sessionid
SELECT *
FROM sys.dm_exec_sql_text(@sqlhandle)
SELECT *
FROM sys.dm_exec_query_plan(@planhandle)