目录
前言
在 SQL Server 中,释放空连接(也称为空闲连接)是一个重要的操作,特别是在高并发环境中,以确保数据库资源得到有效利用并避免连接泄漏
SQL Server 空闲连接查询对比与分析:使用 sys.dm_exec_sessions、sys.dm_exec_requests 和 master.dbo.sysprocesses
1. 基本知识
-
空连接:当前没有执行任何操作但仍保持打开状态的数据库连接
长时间存在的空连接可能会占用数据库资源,导致性能问题 -
连接池:连接池会重用现有连接,而不是每次都创建新的连接。过多的空连接可能会影响连接池的效率
-
事务:事务可能会导致连接长时间保持活动状态,特别是当事务未正确提交或回滚时
详情分析如下:
-
监控连接:
使用sys.dm_exec_sessions
和sys.dm_exec_connections
视图可以监控当前的数据库连接
通过这些视图,可以获取每个连接的状态信息、连接时间等 -
识别空连接:
可以通过检查连接的 status 列来识别空连接
例如,状态为 sleeping 的连接可能是空连接 -
释放空连接:
如果需要手动释放空连接,可以使用 KILL 命令来终止特定的会话
(避免对正常业务产生影响) -
配置连接超时:
连接超时设置,自动释放长时间未活动的连接
例如,可以在连接字符串中设置 Connection Timeout
依赖于不同的数据视图和条件,因此返回的结果可能不同,选择合适的查询即可
2. sys.dm_exec_sessions
基本的参数如下:
sys.dm_exec_sessions
视图提供了当前用户会话的信息last_request_start_time
字段(理论上)表示最后请求的开始时间,但实际上这个字段在 sys.dm_exec_sessions 中并不存在,可能导致此查询无效或错误
SELECT
session_id,
login_name,
status,
host_name,
program_name,
last_request_start_time
FROM sys.dm_exec_sessions
WHERE status = 'sleeping'
AND last_request_start_time < DATEADD(MINUTE, -120, GETDATE());
这个查询仅返回那些状态为 ‘sleeping’ 且最后请求时间早于 120 分钟的会话
截图如下:
3. sys.dm_exec_sessions 和 sys.dm_exec_requests(新版)
sys.dm_exec_requests
提供当前正在执行的请求的信息,包括 start_time 字段,表示请求的开始时间
SELECT
s.session_id,
s.login_name,
s.status,
s.host_name,
s.program_name,
r.start_time AS last_request_start_time
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.status = 'sleeping'
AND r.start_time < DATEADD(MINUTE, -120, GETDATE());
这个查询通过联接 sys.dm_exec_sessions
和 sys.dm_exec_requests
来筛选空闲的会话,检查最后请求的开始时间
截图如下:
4. master.dbo.sysprocesses(旧版)
- master.dbo.sysprocesses 是一个较老的系统表,用于提供进程信息
- spid:进程 ID
- waittype 和 waittime:检测进程是否在等待特定资源
- last_batch:上一个批处理的时间
- login_time:登录时间
SELECT RTRIM(spid), *
FROM master.dbo.sysprocesses
WHERE spid > 50
AND waittype = 0x0000
AND waittime = 0
AND status = 'sleeping '
AND last_batch < DATEADD(MINUTE, -120, GETDATE())
AND login_time < DATEADD(MINUTE, -120, GETDATE());
查询筛选了处于空闲状态的进程,并检查 last_batch 和 login_time 是否早于 120 分钟
截图如下:
5. 总结
一、数据视图差异:
- sys.dm_exec_sessions 和 sys.dm_exec_requests 提供更现代、更详细的信息,相比之下,master.dbo.sysprocesses 是旧版系统表
- sys.dm_exec_sessions 只包含会话信息,不包含请求的详细信息(如最后请求的开始时间),而 sys.dm_exec_requests 提供当前请求的信息
二、时间条件差异:
查询 1 的时间条件依赖于 last_request_start_time 字段,但在实际的 SQL Server 中,这个字段可能不存在或无效
查询 2 使用 sys.dm_exec_requests 提供了实际的请求开始时间,适用于检测空闲会话
查询 3 的时间条件通过 last_batch 和 login_time 结合,可能更适合传统的系统表