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.
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.
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.
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.
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.
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
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
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
WHERE last_read < DATEADD(hour, -24, GETDATE())
AND last_write < DATEADD(hour, -24, GETDATE());
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:
And also the connection section of the session detail page:
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:
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
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
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
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;
Here are a few of the pages where sys.dm_exec_sessions is used in SQL CoPilot:
The list of current requests and sessions:
The session section of the session detail page:
The idle session count in the Big Picture:
This has barely touched the surface of the SQL Server DMVs available. More will be added soon - watch this space.