SQL Server仅用10GB内存却卡顿超时，求优化方案

我有一台部署了3个Web应用的服务器：

• Web应用A：使用频率低
• Web应用B：供13-14名用户日常使用，需对SQL Server执行增删改查操作；该应用会向Web应用C添加文件
• Web应用C：文件夹浏览器

服务器配置、SQL Server内存使用情况、默认内存配置、等待统计结果均有对应截图记录。

SQL Server内存占用持续累积，一周内可超过10GB，随后出现卡顿及SQLException超时错误。我未修改Max memory值，原以为80GB内存足够，SQL Server可按需使用资源。

Web应用B为自定义开发，其SQL查询代码如下：

internal List<object> GetDbDataSqlConnection(string commandText, Dictionary<string, object> kvp, string columns, string company = null)
{
    List<object> dataList = new List<object>();

    try
    {
        using (SqlConnection sqlConnection = new SqlConnection($"data source={WebConfigurationManager.AppSettings["sqldatasource"]};initial catalog={company ?? WebConfigurationManager.AppSettings["defaultsqldb"]};user id={WebConfigurationManager.AppSettings["sqlusername"]};password={WebConfigurationManager.AppSettings["sqlpassword"]};MultipleActiveResultSets=True;App=EntityFramework"))
        {
            using (SqlCommand cmd = new SqlCommand { CommandText = commandText, CommandType = CommandType.Text, Connection = sqlConnection })
            {
                if (kvp != null) foreach (KeyValuePair<string, object> p in kvp) cmd.Parameters.AddWithValue($"@{p.Key.Replace(".", string.Empty)}", p.Value);

                sqlConnection.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Dictionary<string, object> data = new Dictionary<string, object>();
                        foreach (string c in columns.Split(','))
                        {
                            string x = c.Contains(" AS ") ? c.Substring(c.IndexOf(" AS ") + 4) : c;
                            x = x.Replace("[", string.Empty).Replace("]", string.Empty);
                            x = x.Substring(x.IndexOf(".") + 1);
                            data.Add(x, Convert.ToString(reader[x]).Trim());
                        }
                        dataList.Add(data);
                    }

                    reader.Close();
                }

                sqlConnection.Close();
            }
        }
    }
    catch (Exception ex)
    {
        Logger.WriteLog($"[ERROR] on GetDbDataSqlConnection");
        Logger.WriteLog($"Exception message : {ex.Message}");
        Logger.WriteLog($"Exception : {ex}");
        throw ex;
    }

    return dataList;
}

重启SQL服务后Web应用B恢复流畅，现附等待统计结果、错误信息及部分查询语句，请问需优化代码或SQL Server配置中的哪些内容以避免该问题再次发生？

一、SQL Server配置优化 #

1. 设置合理的Max Server Memory
   虽然服务器有80GB内存，但SQL Server默认会尽可能占用内存，导致系统其他资源不足。建议预留10-15GB给操作系统和Web应用，将SQL Server最大内存设为65-70GB。执行以下命令：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'max server memory (MB)', 65536; -- 示例为64GB，可根据实际调整
RECONFIGURE;

2. 排查内存累积来源
   使用动态管理视图排查内存占用较高的组件，确认是否存在未释放的查询计划、游标或大型结果集：

SELECT type, SUM(pages_kb)/1024 AS memory_usage_gb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_usage_gb DESC;

3. 优化查询缓存策略
   开启optimize for ad hoc workloads配置，减少仅执行一次的查询对计划缓存的占用：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

二、代码优化 #

1. 替换AddWithValue为强类型参数
   AddWithValue可能导致SQL Server无法正确推断参数类型，引发隐式转换和低效执行计划。改用强类型参数：

if (kvp != null)
{
    foreach (var p in kvp)
    {
        var param = new SqlParameter($"@{p.Key.Replace(".", string.Empty)}", GetSqlDbType(p.Value))
        {
            Value = p.Value ?? DBNull.Value
        };
        cmd.Parameters.Add(param);
    }
}

// 辅助方法：根据值匹配对应SqlDbType
private SqlDbType GetSqlDbType(object value)
{
    if (value == null) return SqlDbType.Variant;
    switch (Type.GetTypeCode(value.GetType()))
    {
        case TypeCode.String:
            return SqlDbType.NVarChar;
        case TypeCode.Int32:
            return SqlDbType.Int;
        case TypeCode.DateTime:
            return SqlDbType.DateTime;
        // 按需补充其他类型映射
        default:
            return SqlDbType.Variant;
    }
}

2. 修复列名处理逻辑的风险
   当前代码中如果列名不含.，x.Substring(x.IndexOf(".") + 1)会抛出异常；同时强制转字符串会丢失类型信息、浪费内存。优化如下：

foreach (string c in columns.Split(','))
{
    string x = c.Contains(" AS ") ? c.Substring(c.IndexOf(" AS ") + 4) : c;
    x = x.Replace("[", string.Empty).Replace("]", string.Empty);
    // 处理无点号的列名
    int dotIndex = x.IndexOf(".");
    if (dotIndex > -1)
    {
        x = x.Substring(dotIndex + 1);
    }
    // 保留原始值类型，避免不必要的转换
    object value = reader.IsDBNull(reader.GetOrdinal(x)) ? null : reader[x];
    data.Add(x, value);
}

3. 移除不必要的显式关闭操作
   using语句会自动释放SqlDataReader和SqlConnection资源，无需手动调用reader.Close()和sqlConnection.Close()，可直接删除这两行代码。
4. 优化业务查询语句
   检查Web应用B的SQL语句：

• 避免无限制的SELECT *，只返回需要的列
• 为大表查询添加合适的索引
• 确认没有未提交的事务（会导致锁和内存占用）

三、其他排查方向 #

• 查看等待统计结果，确认是否存在RESOURCE_SEMAPHORE等内存相关等待事件，这类事件通常表示内存不足或查询需要过多内存
• 监控SQL Server的Page Life Expectancy（PLE）指标，若PLE持续下降，说明存在内存压力
• 确认Web应用B没有连接泄漏，虽然使用了using，但需排查是否有异常分支导致资源未正确释放

内容的提问来源于stack exchange，提问作者Murni Yanti