SQL Server空闲连接查询对比与分析


    目录
  • 前言
  • 1. 基本知识
  • 2. sys.dm_exec_sessions
  • 3. sys.dm_exec_sessions 和 sys.dm_exec_requests（新版）
  • 4. master.dbo.sysprocesses（旧版）
  • 5. 总结

    前言
    在 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 结合，可能更适合传统的系统表
相关文章！