Sql-server – Frequently Querying the DMV – sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests

dmvsql server

Could frequently (e.g. 5 seconds) querying the below DMVs from a custom tool to get the connection info impact performance on SQL server? I understand this would give a snapshot at that point.
SQL audit logs probably the best way to get this info but It would help if I get any guidance based on your experience with the DMV.

select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName, 
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,sys.dm_exec_sessions.status,r.status, text, *
from sys.dm_exec_sessions inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)

Best Answer

It depends on the amount of connections and your hardware + your ability to change the query.

The first thing you should do is look at the query and decide which columns you need and which columns you don't need.

If we run the query with

set statistics io, time on;

On an idle server with < 100 connections

select count(*) from sys.dm_exec_connections

Result

The result is clear:

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 18 ms.

The logicalreads are also very small (thanks statisticsparser):

This should mean that the query is harmless on this server,even if we run it every 5 seconds.

On a prod server with over 10k connections

select count(*) from sys.dm_exec_connections

Result

Which means that every 5 seconds, around 300 - 400 ms cpu time will be used:

 SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 485 ms.

And that amounts to the query running 1/10th of every 5 seconds.

However, if you can change the query, either by omitting some columns, or using a temp table, the overhead could be lowered by a lot.

E.G. Splitting up the query in two parts and removing some columns.

set statistics io, time on;
select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, 
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.connection_id 
INTO #temp
from sys.dm_exec_sessions 
inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id


select #temp.session_id, #temp.[Connecting machine name], #temp.[Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName, 
#temp.[Session status],#temp.command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.status, text,  *
FROM #temp 
inner join sys.dm_exec_requests r on #temp.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)

DROP TABLE #temp

Resulting in 10X lower amount of CPU usage.

Extra testing

On a server with 600 connections

select count(*) from sys.dm_exec_connections

600

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 40 ms.

About twice as much cpu time as with 20 connections.

Testing every 5 seconds on the idle server

If we add two jobs that run every 10 seconds.

(with 5 seconds between them because we cannot go lower than each 10 seconds scheduling.)

And watch the result, The jobs started executing at ~9 AM on a server with 2 cores @3,5GHZ:

The cpu usage difference is not visible, before and after the 9AM mark.

Server activity remains closely the same

DB IO.

Wait stats went up with a negligible 20ms on ASYNC_NETWORK_IO.

Related Solutions

Sql-server – How to identify a client application whose host_process_id points to sqlserver.exe

Since the sessions are using user-created logins and are accessing application tables in user databases, and since the host_process_id aligns with the sqlserver.exe process, it sounds like there is a SQLCLR assembly running code.

Use the following query to look for SQLCLR Assemblies in all databases:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 'SELECT DatabaseName = ''' + QUOTENAME(d.name) + ''', a.*
FROM sys.assemblies a
WHERE a.name <> ''Microsoft.SqlServer.Types''
'
FROM sys.databases d
ORDER BY d.name;
SET @cmd = @cmd + ';'
EXEC sys.sp_executesql @cmd;

sql-server – Zero Rows Returned on Querying DMV sys.dm_os_performance_counters

If you're sure the user in question has View Server State (and it looks like in your screenshot he does).

Then there are a number of reasons previously put into an msdn blog. Ranging from:

Performance Objects and counters set-up during the SQL Server installation failed.
A mixture of 64 and 32 bit platforms.
Registry permissions have been skewed

To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:

Using an elevated administrator command prompt perform the following steps.

Change the path to the BINN directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
Execute unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
Cycle the remote registry service:
net stop "Remote Registry" then net start "Remote Registry"
Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

The following may also be required:

Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key:
1. Grant Full Control permissions to the Creator Owner account.
2. Grant Full Control permissions to the Administrators account.
3. Grant Read permissions to the SQL Administrators on that box/Node.
4. Grant Full Control permissions to the System account

Best Answer

Extra testing

Related Solutions

Sql-server – How to identify a client application whose host_process_id points to sqlserver.exe

sql-server – Zero Rows Returned on Querying DMV sys.dm_os_performance_counters

Related Question