SQL Server DMVs
Performance and Troubleshooting

By Richard Fryar

One of the most common questions we are asked here is "SQL CoPilot is amazing, but where does it get its information?"

The short answer is "SQL Server DMVs". The long answer is "dynamic management views, dynamic management functions, system views and a handful of DBCC commands."

A DMV looks very much like a table - you can SELECT from it and JOIN it to other DMVs and system views. The difference is, as the name suggests, they are dynamic, meaning the results can change to reflect the current state of the SQL Server instance. Some reference underlying data structures that are constantly updated (eg. sys.dm_db_index_usage_stats), while others go direct to the source (eg. sys.dm_tran_locks).

SQL Server DMVs were introduced in 2005 and every new version adds a few - sometimes a lot.

Which SQL Server DMVs to Use?

Out of the numerous DMVs, which are the most useful and which are used very rarely? This article concentrates on some of the most useful for performance tuning and troubleshooting showing how to use them in some example scripts, and where they are used in SQL CoPilot.

I have put together a diagram showing the DMVs that will be covered. Click on the image to download a pdf version.

SQL Server DMVs

The arrows show a reference from one DMV to another. For example, sys.dm_exec_requests has a session_id column, which can be used to join to sys.dm_exec_sessions, so there is a line joining them together - note the direction of the arrow.

I have left some arrows out - only the most useful are included to prevent the diagram from looking like a spider's web.

SQL Server Execution DMVs - Connections, Sessions and Requests

The execution DMVs relate to the interaction with logins (both system and user) via connections and sessions, the activity (requests) being performed, and the operations performed by the database engine as it executes the requests.

This section deals with connections, sessions and requests. The means by which exact SQL text and execution plans are obtained is covered in the next section.

Connections

A connection is a physical connection with a user. A user may be an actual user connected from a different server or a local connection such as a SQL Agent job or database mirroring endpoint.

The SQL Server DMV that returns information about all current connections is sys.dm_exec_connections.

sys.dm_exec_connections Examples

TCP/IP addresses connected, with number of connections and most recent read/write activity. There is another version of this further down the page that shows host, login and application names:

SELECT client_net_address, COUNT(1) num_connections, MAX(last_read) last_read, MAX(last_write) last_write
FROM sys.dm_exec_connections
GROUP BY client_net_address
ORDER BY 2 DESC;

A connection is always made to a particular NUMA node and remains on that node for its lifetime. This query shows how connections are distributed across the nodes (assuming the instance has more than one):

SELECT node_affinity, COUNT(1) num_connections
FROM sys.dm_exec_connections
GROUP BY node_affinity
ORDER BY node_affinity;

Connections with no activity for 24 hours (change as required):

SELECT * -- replace this with the specific columns you are interested in
FROM sys.dm_exec_connections
WHERE last_read < DATEADD(hour, -24, GETDATE())
AND
last_write < DATEADD(hour, -24, GETDATE());

Where SQL CoPilot uses sys.dm_exec_connections

sys.dm_exec_connections is joined within numerous queries. Here are two of them:

So the obvious one is the list of current user connections:

User Connections

And also the connection section of the session detail page:

Session Connections

Sessions

Most connections map onto a session. A session is also created for connectionless system processes, such as the lazy writer, checkpoint process, etc.

The DMV that returns information about all current sessions is sys.dm_exec_sessions. This returns one row for each session, with columns for login, host, program, status, resource usage (cpu, I/O, etc) and more.

All activity can be traced back to a session via its session_id (also known as the SPID) - note the session_id column in sys.dm_exec_connections, allowing the two DMVs to be joined.

This is one of the DMVs that replaces the legacy sysprocesses view. You should try to avoid using sysprocesses as it will be removed in future versions of SQL Server.

There are a couple of misconceptions about this DMV:

  1. All system sessions have a session_id below 51
    WRONG - no user sessions will have a session_id below 51 but there is nothing to stop system sessions using a higher number. The correct way to filter is on the is_user_process column.
  2. The logical_reads column shows reads from the buffer pool only and does not include physical reads (the reads column)
    WRONG - logical reads include physical reads. The logical_reads column will always be greater than or equal to the reads column.

sys.dm_exec_sessions Examples

Summary of sessions showing login and host, number of sessions for each and when they were last active.

SELECT login_name, HOST_NAME, COUNT(1) num_sessions, MAX(last_request_start_time) last_request
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name, HOST_NAME
ORDER BY login_name, HOST_NAME;

Get the database associated with user sessions.

sys.sysprocesses returns dbid but in SQL Server 2005 thru 2008 R2, sys.dm_exec_sessions does not include it. If you are only interested in active requests you can join to sys.dm_exec_requests and use the database_id column, but that doesn't work for idle sessions.

From SQL Server 2012 onwards the database_id column was added, so to get the database for a session in 2012 onwards it's easy:

SELECT DB_NAME(database_id) database_name, * -- Add other columns of interest
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

In 2005, 2008 and 2008 R2 we need to look at sys.dm_tran_locks. This will have one or more shared database locks for each session:

SELECT DB_NAME(COALESCE(tl.database_id, 1)) database_name, s.* -- Add other columns of interest
FROM sys.dm_exec_sessions s
LEFT JOIN (
  
SELECT request_session_id, MAX(resource_database_id) database_id
  
FROM sys.dm_tran_locks
  
GROUP BY request_session_id
) tl ON s.session_id = tl.request_session_id
WHERE s.is_user_process = 1;

Get idle sessions with open transactions:

SELECT s.* -- replace this with the specific columns you are interested in
FROM sys.dm_exec_sessions s
JOIN sys.dm_tran_session_transactions t ON s.session_id = t.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.session_id IS NULL;

Where SQL CoPilot uses sys.dm_exec_sessions

Here are a few of the pages where sys.dm_exec_sessions is used in SQL CoPilot:

The list of current requests and sessions:

Requests and Sessions

The session section of the session detail page:

Session Detail

The idle session count in the Big Picture:

Idle Sessions

This has barely touched the surface of the SQL Server DMVs available. More will be added soon - watch this space.