Dynamic Management Views
By Bill Graziano
on 30 October 2005
| 0 Comments
| 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.
