解析SQLServer数据库连接超时的问题，从参数优化、查询调优、网络配置到代码实践

// C#连接示例（.NET Framework 4.8）
try 
{
    using (SqlConnection conn = new SqlConnection("Server=.;Database=OrderDB;"))
    {
        conn.Open(); // 此处抛出超时异常
        // [...]业务逻辑
    }
}
catch (SqlException ex)
{
    // 典型错误号示例
    if (ex.Number == -2) // 连接超时
    {
        Log.Error($"连接超时：{ex.Message}");
    }
    else if (ex.Number == 121) // 查询执行超时
    {
        Log.Error($"命令超时：{ex.Message}");
    }
}

EXEC sys.sp_configure N'remote login timeout', 10; -- 远程登录超时（秒）
EXEC sys.sp_configure N'remote query timeout', 30; -- 远程查询超时（秒）
EXEC sys.sp_configure N'network packet size', 4096; -- 网络包大小（字节）
RECONFIGURE;

"Server=.;Pooling=true;Min Pool Size=10;Max Pool Size=200;
 Connection Timeout=15;ConnectRetryCount=3;ConnectRetryInterval=10;"

推荐最大连接数 = (CPU核心数 × 2) + (磁盘阵列数 × 0.5)
例如：8核CPU + 4磁盘阵列 → (8×2)+(4×0.5)=18 → 设置Max Pool Size=200（考虑突发流量）

SELECT TOP 10 
    [对象类型] = CASE 
        WHEN mid.[object_id] IS NOT NULL THEN '表' 
        ELSE '视图' END,
    [对象名称] = OBJECT_NAME(mid.[object_id]),
    [缺失索引] = 'CREATE INDEX IX_' + REPLACE(OBJECT_NAME(mid.[object_id]),'.','_') 
        + '_' + CONVERT(VARCHAR(10),mid.index_handle)
        + ' ON ' + mid.statement 
        + ' (' + ISNULL(mid.equality_columns,'') 
        + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE ',' + mid.inequality_columns END 
        + ')' 
        + ISNULL(' INCLUDE (' + mid.included_columns + ')','')
FROM sys.dm_db_missing_index_details mid
WHERE mid.database_id = DB_ID()
ORDER BY (user_seeks + user_scans) DESC;

CREATE PROCEDURE GetOrderDetails
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    SELECT * 
    FROM Orders 
    WHERE CreateDate BETWEEN @StartDate AND @EndDate
    OPTION (OPTIMIZE FOR UNKNOWN);
END

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TcpTimedWaitDelay"=dword:0000001e
"MaxUserPort"=dword:0000fffe
"StrictTimeWaitSeqCheck"=dword:00000001

# 查看当前设置
Get-NetAdapterAdvancedProperty -Name "Ethernet1" | Where DisplayName -like "*Receive Buffers*"

# 调整接收缓冲区（需根据服务器内存调整）
Set-NetAdapterAdvancedProperty -Name "Ethernet1" -DisplayName "Receive Buffers" -DisplayValue 2048

# 验证设置
Get-NetAdapterAdvancedProperty -Name "Ethernet1" -DisplayName "Receive Buffers"

// ASP.NET Core Startup.cs配置
services.AddDbContextPool<OrderContext>(options => 
    options.UseSqlServer(Configuration.GetConnectionString("OrderDB"),
        sqlOptions => 
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
            sqlOptions.CommandTimeout(60);
        }));

ALTER DATABASE ReportDB 
MODIFY FILEGROUP [PRIMARY] 
DEFAULT WITH (ONLINE = ON, ALLOW_SNAPSHOT_ISOLATION = ON);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_ReportData
ON dbo.SalesData
WITH (COMPRESSION_DELAY = 0);

SELECT 
    session_id,
    connect_time,
    last_read,
    last_write,
    most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE DATEDIFF(MINUTE, last_read, GETDATE()) > 5
  AND session_id <> @@SPID;