经常查询DMV - sys.dm_exec_sessions、sys.dm_exec_connections和sys.dm_exec_requests

Question

是否经常(例如5秒)从自定义工具中查询下面的DMV以获得连接信息对SQL服务器性能的影响？据我所知，这会给出一个快照。SQL审计日志可能是获取此信息的最佳方法，但如果我根据您在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)

Answer 1

取决于连接的数量和硬件+更改查询的能力。

您应该做的第一件事是查看查询，并决定您需要哪些列，哪些列不需要。

如果我们使用

set statistics io, time on;

具有<100个连接的空闲服务器上的

select count(*) from sys.dm_exec_connections

结果

20

结果是明确的：

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

逻辑读物也很小(谢谢统计稀疏)：

这意味着该查询在此服务器上是无害的，即使我们每5秒运行一次。

具有超过10k连接的prod服务器上的

select count(*) from sys.dm_exec_connections

结果

13713

这意味着每5秒，大约300到400毫秒的cpu时间将被使用：

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

，这相当于每5秒运行1/10的查询。

但是，如果您可以通过省略某些列或使用临时表来更改查询，则开销可以降低很多。

例如，将查询分成两部分，并删除一些列。

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

导致CPU使用量减少10倍。

额外测试

具有600个连接的服务器上的

(

)

select count(*) from sys.dm_exec_connections

600

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

大约是20个连接cpu时间的两倍。

每5秒在空闲服务器

上测试一次

如果我们添加两个每10秒运行一次的作业。

(它们之间有5秒的间隔，因为我们不能低于每10秒的调度时间。)

观察结果，作业于上午9点开始在一个具有两个核心@3，5GHZ内核的服务器上执行：

在9AM标记之前和之后，cpu使用率差异是不可见的。

服务器活动保持不变。

DB IO。

在ASYNC_NETWORK_IO上，等待数据上升了20毫秒。