# 分析步骤：

# 1. 查看当前连接状态
SELECT
DB_NAME(dbid) AS DatabaseName,
COUNT(*) AS ConnectionCount,
status,
hostname
FROM sys.sysprocesses
WHERE dbid = DB_ID(‘fgedudb’)
GROUP BY DB_NAME(dbid), status, hostname
ORDER BY ConnectionCount DESC;
GO

DatabaseName ConnectionCount status hostname
———— ————— ——– ————
fgedudb 150 sleeping APP-SERVER-01
fgedudb 45 sleeping APP-SERVER-02
fgedudb 5 running APP-SERVER-01

# 2. 查看长时间空闲连接
SELECT
session_id,
DB_NAME(database_id) AS DatabaseName,
host_name,
program_name,
status,
DATEDIFF(MINUTE, last_request_end_time, GETDATE()) AS IdleMinutes,
last_request_end_time
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(‘fgedudb’)
AND status = ‘sleeping’
AND DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 30
ORDER BY IdleMinutes DESC;
GO

session_id DatabaseName host_name program_name status IdleMinutes last_request_end_time
———- ———— ————- ————– ——- ———– ——————–
78 fgedudb APP-SERVER-01 .Net SqlClient sleeping 125 2026-04-07 08:30:00
82 fgedudb APP-SERVER-01 .Net SqlClient sleeping 98 2026-04-07 08:57:00
95 fgedudb APP-SERVER-02 .Net SqlClient sleeping 67 2026-04-07 09:28:00

# 3. 解决方案：清理长时间空闲连接
— 创建清理脚本
DECLARE @sql NVARCHAR(MAX) = ”;
SELECT @sql = @sql + ‘KILL ‘ + CAST(session_id AS VARCHAR) + ‘; ‘
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(‘fgedudb’)
AND status = ‘sleeping’
AND DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 60;
EXEC(@sql);
GO

# 4. 优化连接池配置
— 应用端连接字符串调整
— Max Pool Size=200 (从150增加)
— Connection Lifetime=1800 (30分钟自动回收)
— Connection Reset=true (连接重置)

# 分析步骤：

# 1. 监控连接数变化
— 创建监控脚本
SELECT
GETDATE() AS CheckTime,
DB_NAME(database_id) AS DatabaseName,
COUNT(*) AS ConnectionCount,
SUM(CASE WHEN status = ‘running’ THEN 1 ELSE 0 END) AS ActiveCount,
SUM(CASE WHEN status = ‘sleeping’ THEN 1 ELSE 0 END) AS SleepingCount
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(‘fgedudb’)
GROUP BY DB_NAME(database_id);
GO

CheckTime DatabaseName ConnectionCount ActiveCount SleepingCount
———————– ———— ————— ———– ————-
2026-04-08 10:00:00 fgedudb 150 5 145
2026-04-08 11:00:00 fgedudb 180 8 172
2026-04-08 12:00:00 fgedudb 210 6 204
2026-04-08 13:00:00 fgedudb 245 7 238

# 2. 分析连接来源
SELECT
host_name,
program_name,
COUNT(*) AS ConnectionCount,
MIN(login_time) AS FirstLogin,
MAX(login_time) AS LastLogin
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(‘fgedudb’)
GROUP BY host_name, program_name
ORDER BY ConnectionCount DESC;
GO

host_name program_name ConnectionCount FirstLogin LastLogin
————- ————– ————— ——————— ———————
APP-SERVER-01 .Net SqlClient 180 2026-04-08 08:00:00 2026-04-08 13:00:00
APP-SERVER-02 .Net SqlClient 65 2026-04-08 08:05:00 2026-04-08 13:00:00

# 3. 排查应用代码问题
— 检查是否有未关闭的连接
— 确保使用using语句或try-finally关闭连接
— 检查连接池配置是否合理

# 4. 解决方案
— 应用代码修改示例(C#)
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
// 执行数据库操作
// 连接会自动关闭和释放
}

— 配置连接池参数
Min Pool Size=20
Max Pool Size=200
Connection Lifetime=1800
Connection Reset=true
Pooling=true

— 配置工作线程数
EXEC sp_configure ‘max worker threads’, 1024;
RECONFIGURE;

— 配置内存
EXEC sp_configure ‘min server memory (MB)’, 4096;
EXEC sp_configure ‘max server memory (MB)’, 16384;
RECONFIGURE;

# 2. 应用端优化
— 连接字符串配置
Server=192.168.1.100;Database=fgedudb;
User ID=fgedu;Password=fgedu123;
Min Pool Size=20;
Max Pool Size=200;
Connect Timeout=30;
Connection Lifetime=1800;
Connection Reset=true;
Pooling=true;

# 3. 监控脚本
— 创建连接池监控存储过程
CREATE PROCEDURE sp_fgedu_monitor_connections
AS
BEGIN
SELECT
GETDATE() AS CheckTime,
DB_NAME(database_id) AS DatabaseName,
COUNT(*) AS TotalConnections,
SUM(CASE WHEN status = ‘running’ THEN 1 ELSE 0 END) AS ActiveConnections,
SUM(CASE WHEN status = ‘sleeping’ THEN 1 ELSE 0 END) AS SleepingConnections,
SUM(CASE WHEN DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 30
THEN 1 ELSE 0 END) AS LongIdleConnections
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY DB_NAME(database_id);
END;
GO

Command(s) completed successfully.

— 执行监控
EXEC sp_fgedu_monitor_connections;
GO

CheckTime DatabaseName TotalConnections ActiveConnections SleepingConnections LongIdleConnections
———————– ———— —————- —————– ——————- ——————-
2026-04-08 14:00:00 fgedudb 185 12 173 45
2026-04-08 14:00:00 master 8 2 6 0