Dynamic Management Views

In order to provide you with a window into what is happening inside SQL Server, the dynamic management views (DMVs) were created. They can offer details on both the items being stored on the server and what is happening inside of it right now. They are made to be used in place of the system tables and other features offered by earlier SQL Server iterations. This article introduces DMVs and goes over some fundamental views and operations.

Adaptive Management In reality, views are made up of both views and table-valued functions. Some are stored in the master database and apply to the entire server. Other ones depend on the database in question. The "sys" schema houses them all. They all have names that begin with dm_ prefix. They are divided into the following twelve groups:

• Common Language Runtime Related Dynamic Management Views
• I/O Related Dynamic Management Views and Functions
• SQL Operating System Related Dynamic Management Views
• Index Related Dynamic Management Views and Functions
• Transaction Related Dynamic Management Views and Functions
• Query Notifications Related Dynamic Management Views
• Database Related Dynamic Management Views
• Replication Related Dynamic Management Views
• Database Mirroring Related Dynamic Management Views
• Execution Related Dynamic Management Views and Functions
• Service Broker Related Dynamic Management Views
• Full-Text Search Related Dynamic Management Views

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

Sessions

We'll begin by taking a look at a view that will inform us about each session. Running sp_who2 or choosing from sysprocesses are analogous to selecting from sys.dm_exec_sessions. Each session only contains one row. Keep in mind that you must qualify all references to dynamic management views and functions 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     2023-02-16 12:11:26.231 120
52         bg                   2023-02-16 12:03:33.452 160
53         L30\billgraziano     2023-02-16 11:43:26.124 40
55         bg                   2023-02-16 12:03:34.312 80

The view also returns an additional ten or so columns, but this is a good starting point. The SPID that we are accustomed to seeing is essentially the session_id. The "server process identifier," or SPID, was returned by selecting @@SPID in SQL Server 2000. The "session ID of the current user process" is returned when selecting @@SPID in SQL Server 2005. The view also returns session-specific data, including reads, writes, and other settable session objects, as well as ANSI NULL settings.

Connections

We can also check the connection details for sessions that originate from outside of SQL Server (session_id >= 51). To obtain this data, we'll ask sys.dm_exec_connections. One row is returned by this view 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
------------------------------------ ----------- -------------------- -----------
2950E0F4-073D-4E2E-B615-B25DBBE949A9 51          local machine        NTLM
9F9A1793-0519-4FAE-B714-7AA515922C7F 52          10.10.5.20           SQL
E953FC9F-2D8B-4B66-A112-8EC4CD184B3F 53          local machine        NTLM
32266C4D-E265-4480-8310-4248D7DE8294 55          10.10.5.20           SQL

There are about fifteen additional columns, but for the time being, we'll concentrate on these. Take note that both the authentication method and the client's IP address are listed. An easy way to view the IP address for each connection in SQL Server is something I've always wished for.

Requests

We'll use the sys.dm_exec_requests view to see what each connection is actually doing. This lists each request that SQL Server is currently processing.

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    0x02000000DF20982DEBF2034CF395FAC39FD3AB154E 1
56         suspended  WAITFOR   0x02000000C7143B7219F2CA427FA76915D752110631 1

There are currently two open queries. My selection from sys.dm_exec_requests is the first (#54). Another query is being run from a different connection in the second. We can see that a WAITFOR command is currently being executed. The start time, plan_handle (a hash map of the cached query plan), wait type information, transaction information, reads, writes, and connection-specific settings are among the other interesting columns in this view. Let's see what we can learn about the other active query.

SQL Text

Dynamic management function sys.dm_exec_sql_text returns the text of a SQL statement given a SQL handle. Fortunately, our query on sys.dm_exec_requests resulted in a SQL handle. We can use the following query to see the SQL code that is currently running in session #54:

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'

For each row the view returned, the function was called using a CROSS APPLY.

Security

A user must have been granted specific permissions in order to query these views. The user must have the VIEW SERVER STATE server permission in order to view the server-wide DMVs. after executing the next query as an administrator.

GRANT VIEW SERVER STATE to bg

As a regular user logged in, I was able to query the DMVs. A user must have the VIEW DATABASE STATE permission in each individual database in order to query database-specific DMVs. You can use the DENY command and specify those particular views to prevent a user from accessing particular DMVs. And keep in mind that giving permission to roles rather than to specific users is always preferable.

Summary

Some fundamental dynamic management views include those. I'll go over a few more management views that offer more details about the server and its contents in a subsequent article.