Dynamic Management Views in Sql Server
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:
1. Common Language Runtime Related Dynamic Management Views
2. I/O Related Dynamic Management Views and Functions
3. Database Mirroring Related Dynamic Management Views
4. Query Notifications Related Dynamic Management Views
5. Database Related Dynamic Management Views
6. Replication Related Dynamic Management Views
7. Execution Related Dynamic Management Views and Functions
8. Service Broker Related Dynamic Management Views
9. Full-Text Search Related Dynamic Management Views
10. SQL Operating System Related Dynamic Management Views
11. Index Related Dynamic Management Views and Functions
12. 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.
Best way to find the Latest Executed Query from a Connection:
First we should find the session_id using sys.dm_exec_connections dmv. Then use the below query:
select
st.text
from
sys.dm_exec_connections C
cross apply
sys.dm_exec_sql_text(most_recent_sql_handle) st
where
C.session_id=61
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.
