Dynamic Management Views

By Bill Graziano on 30 October 2005 | Tags: Administration

The dynamic management views (DMVs) in SQL Server 2005 are designed to give you a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000. This article provides an introduction to DMVs and covers a few of the basic views and functions.

The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

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 Operating System Related Dynamic Management Views
Index Related Dynamic Management Views and Functions
Transaction Related Dynamic Management Views and Functions

This article will focus on a few of the more common views.

Sessions

We'll start by looking at a view that will give us information on each session. Selecting from sys.dm_exec_sessions is similar to running sp_who2 or selecting from sysprocesses. It lists one row per session. Remember that when you reference any of the dynamic management views or functions you'll need to qualify with the sys schema.

SELECT
	session_id,
	login_name,
	last_request_end_time,
	cpu_time
FROM
	sys.dm_exec_sessions
WHERE
	session_id >= 51
GO


session_id login_name           last_request_end_time   cpu_time
---------- -------------------- ----------------------- -----------
51         L30\billgraziano     2005-10-30 17:11:26.487 170
52         bg                   2005-10-30 17:03:33.667 190
53         L30\billgraziano     2005-10-30 16:43:26.160 30
55         bg                   2005-10-30 17:03:34.740 90

The view also returns an extra 25 columns or so but this is a good place to start. The session_id is basically the SPID that we're used to seeing. In SQL Server 2000 selecting @@SPID returned the "server process identifier" or SPID. In SQL Server 2005 selecting @@SPID returns the "session ID of the current user process". The view also returns session-specific information such as the ANSI NULL settings, reads, writes and other set-able session objects.

Connections

For those sessions that come from outside SQL Server (session_id >= 51) we can also look at the connection information. We'll query sys.dm_exec_connections for this information. This view returns one row for each connection.

SELECT
	connection_id,
	session_id,
	client_net_address,
	auth_scheme
FROM
	sys.dm_exec_connections
GO

connection_id                        session_id  client_net_address   auth_scheme
------------------------------------ ----------- -------------------- -----------
71AE7560-9366-486C-ACBF-D5405E89B6F5 51          local machine        NTLM
CAB9E608-0312-42D8-A19C-AD1D954AA427 52          192.168.8.20         SQL
7369B4B6-7199-4527-A882-215023D352EC 53          local machine        NTLM
192FFCE9-588C-49DA-9BE1-07134291BC86 55          192.168.8.20         SQL

There are about fifteen other columns but we're going to focus on these for now. Notice that the IP address of the client is listed as well as the authentication scheme. One of the things I've always wanted from SQL Server is an easy way to see the IP address for each connection.

Requests

In order to what each connection is actually doing we're going to use the sys.dm_exec_requests view. This lists each request that is executing within SQL Server.

SELECT
	session_id,
	status,
	command,
	sql_handle,
	database_id
FROM
	sys.dm_exec_requests
WHERE
	session_id >= 51 
GO

session_id status     command   sql_handle                                         database_id
---------- ---------- --------- -------------------------------------------------- -----------
54         running    SELECT    0x02000000DF1170132662EE95912DA70270B3EE0F74BCD15C 1
56         suspended  WAITFOR   0x02000000C72622210D647D6515783CD4D7140FEB7EE478B5 1

In this case there are two running queries. The first (#54) is my select from sys.dm_exec_requests. The second is another query running from a different connection. We can see that it's current running a WAITFOR command. This view has a number of other interesting columns including the start time, plan_handle (hash map of the cached query plan), wait type information, transaction information, reads, writes and connection specific settings. Let's see what we can find out about the other query that's running.

SQL Text

sys.dm_exec_sql_text is a dynamic management function that returns the text of a SQL statement given a SQL handle. Fortunately we just happen to have a SQL handle from our query of sys.dm_exec_requests. To see the SQL text that's currently executing in session #54 we can use this query:

SELECT	
	st.text
FROM
	sys.dm_exec_requests r
CROSS APPLY 
	sys.dm_exec_sql_text(sql_handle) AS st
WHERE
	r.session_id = 56
GO


text
----------------------------------------------
SELECT
	*
FROM
	TAMSDev.dbo.AuditLog

WAITFOR DELAY '00:00:10'

Notice that we used a CROSS APPLY to execute the function for each row returned by the view.

Security

In order to query these views a user needs specific permissions granted. To view the server-wide DMVs the user must be granted the VIEW SERVER STATE on the server. After running the following query as administrator

GRANT VIEW SERVER STATE to bg

I was able to query the DMVs when logged in as a regular user. To query database specific DMVs a user must be granted the VIEW DATABASE STATE permission in each specific database. If you want to deny a user permission to query certain DMVs you can use the DENY command and reference those specific views. And remember it's always better to grant permission to roles instead of individual users.

Summary

Those are some of the basic dynamic management views. In a future article I'll cover a few more of the management views that provide additional information about the server and what's in it.