印刻数据库

外观

SQLServer 连接管理 ​

连接管理概述 ​

SQL Server 连接管理是数据库运维的重要组成部分，合理的连接管理可以提高数据库性能、减少资源消耗并避免连接相关的故障。连接管理包括连接池配置、连接限制设置、连接监控和故障排查等方面。SQL Server 2012 及以上版本在连接管理方面不断增强，提供了更多的监控和优化功能。

版本差异 ​

SQL Server 版本连接管理特性差异生产影响
2012- 基础连接池支持
- 有限的连接监控功能
- 传统连接字符串选项		- 适合中小规模应用
- 连接管理相对简单
2014- 增强的连接池性能
- 改进的连接故障处理
- 支持更多连接字符串参数		- 支持更高并发
- 连接恢复能力增强
2016- 增强的连接池监控
- 支持 MultipleActiveResultSets (MARS) 优化
- 改进的连接加密		- 更好的连接池可视性
- 增强的安全性
2017- Linux 上的连接管理
- 自适应连接池
- 增强的连接性能		- 跨平台支持
- 更智能的连接管理
2019- 智能查询处理对连接的影响
- 增强的连接故障处理
- 改进的连接池统计		- 减少连接相关的性能问题
- 更好的故障恢复
2022- 增强的连接安全性
- Azure 集成优化
- 改进的连接池监控		- 更强的安全保障
- 更好的云集成

连接池管理 ​

连接池概述 ​

连接池是应用程序与数据库之间的连接缓存机制，用于减少建立和关闭数据库连接的开销。SQL Server 支持多种连接池实现，包括 ADO.NET 连接池、ODBC 连接池和 OLE DB 连接池等。

连接池工作原理 ​

  1. 连接建立：应用程序请求数据库连接时，首先检查连接池是否有可用连接
  2. 连接复用：如果连接池中有可用连接，直接返回该连接；否则创建新连接
  3. 连接使用：应用程序使用连接执行数据库操作
  4. 连接归还：应用程序关闭连接时，将连接归还到连接池，而不是真正关闭连接
  5. 连接回收：连接池定期回收长时间空闲的连接

连接池配置 ​

ADO.NET 连接池配置 ​

ADO.NET 连接池可以通过连接字符串参数进行配置，以下是不同版本的配置示例：

csharp
// SQL Server 2012-2014 基础配置
string connectionString2014 = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True;"
                           + "Max Pool Size=100;Min Pool Size=5;Connection Timeout=30;"
                           + "Pooling=True;Enlist=True;";

// SQL Server 2016+ 增强配置
string connectionString2016 = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True;"
                           + "Max Pool Size=200;Min Pool Size=10;Connection Timeout=60;"
                           + "Connection Lifetime=3600;Idle Timeout=600;Pooling=True;"
                           + "Enlist=True;MultipleActiveResultSets=True;"
                           + "Async=True;Encrypt=True;TrustServerCertificate=True;";

// SQL Server 2022+ 云优化配置
string connectionString2022 = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True;"
                           + "Max Pool Size=200;Min Pool Size=10;Connection Timeout=60;"
                           + "Connection Lifetime=3600;Idle Timeout=600;Pooling=True;"
                           + "Enlist=True;MultipleActiveResultSets=True;"
                           + "Async=True;Encrypt=True;TrustServerCertificate=False;"
                           + "ApplicationIntent=ReadOnly;MultiSubnetFailover=True;";

SQL Server 连接池设置 ​

SQL Server 级别的连接设置：

sql
-- 查看当前连接数
SELECT COUNT(*) AS current_connections
FROM sys.dm_exec_sessions;

-- 查看最大连接数配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'user connections';

-- 修改最大连接数（需要重启 SQL Server 生效）
-- 注意：SQL Server 2012+ 建议使用默认的自动配置（0）
EXEC sp_configure 'user connections', 1000;
RECONFIGURE;

连接池监控 ​

监控连接池状态 ​

sql
-- 查看连接池统计信息
SELECT 
    db_name(dbid) AS database_name,
    COUNT(*) AS connection_count,
    login_name,
    host_name,
    program_name
FROM sys.dm_exec_sessions
WHERE status = 'running' -- 或 'sleeping' 查看所有连接
GROUP BY dbid, login_name, host_name, program_name
ORDER BY connection_count DESC;

-- 查看连接池详细信息
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    last_request_start_time,
    last_request_end_time,
    wait_type,
    wait_time
FROM sys.dm_exec_sessions
WHERE program_name LIKE '%ApplicationName%';

-- SQL Server 2016+ 查看连接池更详细信息
SELECT 
    c.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    c.connect_time,
    c.net_transport,
    c.protocol_type,
    c.encrypt_option,
    c.auth_scheme
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE s.status = 'running';

使用 Performance Monitor 监控连接池 ​

关键性能计数器：

  • SQLServer:General StatisticsUser Connections
  • SQLServer:General StatisticsLogins/sec
  • SQLServer:General StatisticsLogouts/sec
  • SQLServer:LatchesLatch Waits/sec
  • SQLServer:Wait StatisticsWait Time (ms)Total

连接限制与优化 ​

连接限制策略 ​

设置合理的最大连接数 ​

  • 根据服务器硬件资源（CPU、内存）设置最大连接数
  • 对于 OLTP 系统，建议每 CPU 核心支持 100-200 个连接
  • 对于数据仓库系统，建议每 CPU 核心支持 50-100 个连接
  • 避免设置过大的最大连接数，导致资源耗尽
  • SQL Server 2012+ 建议使用默认的自动配置（0），让 SQL Server 根据负载自动调整

连接超时设置 ​

  • 根据网络环境和查询复杂度设置连接超时时间
  • 一般设置为 30-60 秒
  • 避免设置过短的超时时间，导致频繁的连接失败
  • 避免设置过长的超时时间，导致资源长时间占用

连接优化策略 ​

使用连接池 ​

  • 始终启用连接池（默认启用）
  • 合理配置连接池参数
  • 避免频繁创建和关闭连接
  • 2016+ 考虑使用 Async=True 支持异步连接

优化连接字符串 ​

  • 只包含必要的连接参数
  • 启用连接加密（2016+ 建议默认启用）
  • 启用 MultipleActiveResultSets (MARS) 以支持多个活动结果集
  • 考虑使用 ApplicationIntent=ReadOnly 进行读写分离
  • 2022+ 考虑使用 MultiSubnetFailover=True 支持 Always On 可用性组

减少连接占用时间 ​

  • 及时释放不再使用的连接
  • 避免在连接中执行长时间运行的操作
  • 考虑使用异步编程模型
  • 实现连接池预热，避免冷启动问题

实现连接重试机制 ​

  • 对于临时连接失败，实现自动重试机制
  • 设置合理的重试次数和间隔
  • 避免立即重试，导致服务器压力过大
  • 考虑使用指数退避算法

生产场景示例 ​

场景1：高并发 OLTP 系统连接配置 ​

项目内容
服务器配置16核CPU, 64GB RAM, SSD存储
SQL Server 版本2022 Enterprise
工作负载高并发 OLTP 系统，峰值连接数 1500+
连接池配置- Max Pool Size: 200
- Min Pool Size: 20
- Connection Timeout: 60秒
- Connection Lifetime: 3600秒
- Idle Timeout: 600秒
- Async: True
- Encrypt: True
服务器配置- 最大连接数: 自动配置（0）
- 启用 MARS
- 启用 Always On 可用性组
监控重点- 用户连接数 < 1500
- 连接池等待时间 < 100ms
- 登录/注销速率稳定
优化策略- 实现连接池预热
- 使用读写分离
- 优化查询性能，减少连接占用时间

场景2：Linux 上的 SQL Server 连接管理 ​

项目内容
服务器配置8核CPU, 32GB RAM, Linux Ubuntu 22.04
SQL Server 版本2019 Linux
工作负载混合工作负载，Web 应用连接
连接池配置- Max Pool Size: 100
- Min Pool Size: 10
- Connection Timeout: 30秒
- Async: True
- Encrypt: True
服务器配置- 最大连接数: 自动配置
- 启用 TCP/IP 协议
- 配置防火墙允许 1433 端口
监控重点- 连接数稳定
- 无连接超时
- Linux OOM 杀手不触发
优化策略- 使用 Linux cgroups 限制 SQL Server 内存
- 配置合理的 Linux 内核参数
- 使用 Azure Data Studio 监控连接

场景3：Azure VM 上的 SQL Server 连接配置 ​

项目内容
服务器配置Azure VM (Standard_D16s_v3), 64GB RAM
SQL Server 版本2022 Azure Edition
工作负载企业级应用，多区域部署
连接池配置- Max Pool Size: 150
- Min Pool Size: 15
- Connection Timeout: 45秒
- MultiSubnetFailover: True
- ApplicationIntent: ReadWrite
- Encrypt: True
服务器配置- 启用 Always On 可用性组
- 配置 Azure 负载均衡器
- 启用 Azure SQL Analytics
监控重点- 跨区域连接延迟 < 100ms
- 连接故障自动切换
- 连接加密状态
优化策略- 使用 Azure 私有链接
- 实现连接重试机制
- 配置 Azure Monitor 告警

连接故障排查 ​

连接失败常见原因 ​

错误信息可能原因解决方案版本差异
Login failed for user 'username'用户名或密码错误检查用户名和密码无
Could not open a connection to SQL Server网络问题、SQL Server 未运行、端口配置错误检查网络连接、SQL Server 服务状态、防火墙设置无
The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment网络延迟、SQL Server 负载过高检查网络连接、优化 SQL Server 性能2019+ 增强了超时处理
Maximum number of concurrent connections exceeded连接数达到上限增加最大连接数、优化连接池配置2012+ 建议使用自动配置
A transport-level error has occurred when receiving results from the server网络中断、SQL Server 重启检查网络连接、SQL Server 日志2017+ 增强了连接恢复
SSL Provider: The certificate chain was issued by an authority that is not trusted证书信任问题配置 TrustServerCertificate=True 或安装可信证书2016+ 加强了证书验证

连接故障排查步骤 ​

步骤 1：检查 SQL Server 服务状态 ​

sql
-- 查看 SQL Server 服务状态
EXEC xp_readerrorlog 0, 1, N'Service started';

-- 查看最近的错误日志
EXEC xp_readerrorlog 0, 1, N'error', NULL, NULL, NULL, N'desc';

-- 2016+ 查看更详细的错误信息
SELECT 
    event_time,
    error_number,
    error_severity,
    error_state,
    error_message,
    database_id,
    user_id
FROM sys.fn_get_audit_file('<audit_file_path>', DEFAULT, DEFAULT);

步骤 2：检查网络连接 ​

powershell
# 使用 PowerShell 测试网络连接（Windows）
test-netconnection -ComputerName ServerName -Port 1433

# 使用 telnet 测试网络连接
telnet ServerName 1433

# 使用 nc 测试网络连接（Linux）
nc -zv ServerName 1433

步骤 3：检查 SQL Server 配置 ​

sql
-- 查看 SQL Server 监听状态
SELECT 
    local_net_address,
    local_tcp_port,
    type_desc
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

-- 查看 SQL Server 网络配置
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', 
                       N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',
                       N'Enabled';

-- 2016+ 查看连接加密状态
SELECT 
    session_id,
    encrypt_option
FROM sys.dm_exec_connections;

步骤 4：检查登录权限 ​

sql
-- 查看登录名状态
SELECT 
    name,
    type_desc,
    is_disabled,
    create_date,
    modify_date
FROM sys.server_principals
WHERE name = 'LoginName';

-- 查看登录审核日志
SELECT 
    login_name,
    error_code,
    error_message,
    start_time
FROM sys.event_log
WHERE event_type = 'error' AND login_name = 'LoginName'
ORDER BY start_time DESC;

步骤 5：检查连接池状态 ​

sql
-- 查看连接池相关的等待类型
SELECT 
    wait_type,
    wait_time_ms,
    waiting_tasks_count,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%POOL%' OR wait_type LIKE '%CONNECTION%';

-- 查找长时间运行的连接
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    last_request_start_time,
    DATEDIFF(second, last_request_start_time, GETDATE()) AS idle_seconds
FROM sys.dm_exec_sessions
WHERE status = 'sleeping'
ORDER BY idle_seconds DESC;

连接管理最佳实践 ​

合理配置连接池 ​

  • 根据应用程序需求设置 Max Pool Size 和 Min Pool Size
  • 启用 Connection Lifetime 以自动回收旧连接
  • 设置合理的 Connection Timeout
  • 2016+ 考虑使用 Async=True 支持异步连接
  • 2022+ 考虑使用 MultiSubnetFailover=True 支持 Always On 可用性组

监控连接状态 ​

  • 定期监控连接数和连接池状态
  • 设置连接数告警阈值
  • 监控连接相关的等待类型
  • 分析连接使用模式
  • 2016+ 使用 SQL Server Management Studio 的活动监视器

优化连接使用 ​

  • 避免长时间占用连接
  • 及时释放不再使用的连接
  • 实现连接重试机制
  • 考虑使用只读连接执行查询操作
  • 实现连接池预热，避免冷启动问题

安全配置 ​

  • 使用 Windows 身份验证代替 SQL Server 身份验证
  • 2016+ 启用连接加密
  • 限制登录名的连接权限
  • 定期更换密码
  • 2022+ 考虑使用 Azure AD 身份验证

高可用性考虑 ​

  • 使用 Always On 可用性组监听器
  • 配置连接字符串以支持故障转移
  • 实现应用程序级别的故障转移逻辑
  • 2022+ 考虑使用 Azure 自动故障转移组

常见问题 (FAQ) ​

  1. 如何查看当前 SQL Server 的连接数？

    • 使用以下查询查看当前连接数：
      sql
      -- 查看所有连接数
SELECT COUNT(*) AS total_connections
FROM sys.dm_exec_sessions;

-- 按状态分类查看连接数
SELECT 
    status,
    COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
GROUP BY status;

-- 按数据库分类查看连接数
SELECT 
    DB_NAME(dbid) AS database_name,
    COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
GROUP BY dbid;

  2. SQL Server 2012+ 建议使用什么最大连接数设置？

    • SQL Server 2012+ 建议使用默认的自动配置（0），让 SQL Server 根据系统资源自动调整最大连接数
    • 对于特殊场景，可以根据硬件资源和工作负载手动调整

  3. 连接池中的连接什么时候会被关闭？

    • 连接池中的连接会在以下情况下被关闭：
      • 连接池达到最大大小，且所有连接都在使用中
      • 连接空闲时间超过 Idle Timeout 配置
      • 连接生命周期超过 Connection Lifetime 配置
      • 连接出现错误
      • 应用程序显式调用 ClearPool 或 ClearAllPools

  4. 如何诊断连接泄漏问题？

    • 诊断连接泄漏问题的步骤：
      1. 监控连接数变化，确认连接数持续增长
      2. 查找长时间运行的连接：
        sql
        SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    last_request_start_time,
    DATEDIFF(second, last_request_start_time, GETDATE()) AS idle_seconds
FROM sys.dm_exec_sessions
WHERE status = 'sleeping'
ORDER BY idle_seconds DESC;
      3. 分析应用程序代码，查找未正确关闭的连接
      4. 使用连接池监控工具，如 SQL Server Profiler 或 Extended Events

  5. 如何优化高并发连接场景？

    • 优化高并发连接场景的方法：
      1. 使用 SQL Server 2012+ 的自动连接数配置
      2. 优化连接池配置，增加 Max Pool Size
      3. 优化查询性能，减少连接占用时间
      4. 考虑使用读写分离，分担主服务器压力
      5. 实现连接重试机制
      6. 考虑使用异步编程模型
      7. 实现连接池预热

  6. 连接池会导致死锁吗？

    • 连接池本身不会导致死锁，但不合理的连接使用可能导致死锁：
      • 长时间占用连接执行多个事务
      • 未正确释放连接，导致连接池耗尽
      • 连接池配置不合理，导致连接争用
    • 通过合理配置连接池和优化连接使用，可以减少死锁的发生

  7. 2016+ 为什么建议启用连接加密？

    • 2016+ 增强了连接加密功能，性能开销降低
    • 符合安全最佳实践，保护数据传输安全
    • 满足合规要求（如 GDPR、HIPAA 等）
    • 可以使用自签名证书或可信证书

  8. 如何配置 MultipleActiveResultSets (MARS)？

    • 在连接字符串中添加 MultipleActiveResultSets=True
    • 适用于需要在单个连接上执行多个查询的场景
    • 2016+ 优化了 MARS 性能

  9. 如何实现连接池预热？

    • 在应用程序启动时，预先创建并打开多个连接
    • 执行简单查询验证连接可用性
    • 保持连接在连接池中，避免冷启动问题
    • 示例代码：
      csharp
      // 连接池预热示例
for (int i = 0; i < 20; i++)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand("SELECT 1", conn))
        {
            cmd.ExecuteScalar();
        }
    }
}

  10. 2022+ 如何配置 Always On 可用性组连接？

    • 在连接字符串中添加 MultiSubnetFailover=True
    • 使用可用性组监听器作为服务器名称
    • 考虑使用 ApplicationIntent 进行读写分离
    • 示例连接字符串：
      Data Source=AGListener;Initial Catalog=DatabaseName;Integrated Security=True;
MultiSubnetFailover=True;ApplicationIntent=ReadWrite;
Encrypt=True;TrustServerCertificate=False;

总结 ​

SQL Server 连接管理是数据库运维的重要组成部分，合理的连接管理可以提高数据库性能、减少资源消耗并避免连接相关的故障。随着 SQL Server 版本的不断升级，连接管理功能也在不断增强，从基础的连接池支持到智能连接管理和云集成。

建议 DBA 在进行连接管理时，注意以下几点：

  1. 合理配置连接池参数，根据应用程序需求调整最大连接数、最小连接数和超时时间
  2. 定期监控连接状态，设置连接数告警阈值
  3. 优化连接使用，避免长时间占用连接
  4. 实现连接重试机制，提高应用程序的容错能力
  5. 及时排查连接相关的故障，确保数据库服务的可用性
  6. 遵循连接管理最佳实践，提高数据库的性能和安全性
  7. 关注版本差异，利用新版本的连接管理增强功能

通过实施这些最佳实践，可以构建高效、可靠的连接管理体系，确保 SQL Server 数据库在高并发场景下的稳定运行。