SQL Server简单表读写性能低下问题排查求助

背景 #

应用内有“计算对象”模块，核心计算、分组等操作通过夜间批处理完成。为此创建CalculatedModel表，存储用户标识（ID）和序列化后的JSON对象（前端直接展示，无需转换格式）。

表结构 #

CREATE TABLE [dbo].[CalculatedModel](
    [ID] [uniqueidentifier] NOT NULL,
    [Model] [varchar](max) NULL,
 CONSTRAINT [PK_CalculatedModel] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
) 
GO

核心性能问题 #

1. 全量查询慢：虽可使用分布式内存缓存，但部分操作需获取全部结果，执行SELECT * FROM CalculatedModel时，测试环境（生产镜像）约85条数据耗时达9秒，生产环境数据量更大，问题更突出。
2. 插入超时：午夜批处理先截断表再插入数据，几乎每晚都会出现单条计算模型插入超时的情况。

排查情况 #

• 执行计划显示仅为聚集索引扫描，无大量常规I/O，但LOB相关读写极高：
  • 首次执行统计信息：

    Table 'CalculatedModel'. 
    Scan count 1, 
    logical reads 3, 
    physical reads 1, 
    page server reads 0, 
    read-ahead reads 0, 
    page server read-ahead reads 0, 
    lob logical reads 331062, 
    lob physical reads 1561, 
    lob page server reads 0, 
    lob read-ahead reads 468754, 
    lob page server read-ahead reads 0.
    

  • 客户端统计：

    Client Execution Time   16:25:34        
    Query Profile Statistics                            
      Number of INSERT, DELETE and UPDATE statements    0       
      Rows affected by INSERT, DELETE, or UPDATE statements 0       
      Number of SELECT statements       1       
      Rows returned by SELECT statements        82      
      Number of transactions        0       
    Network Statistics                           
      Number of server roundtrips           1       
      TDS packets sent from client      1       
      TDS packets received from server      109763      
      Bytes sent from client            112     
      Bytes received from server        4.495862E+08        
    Time Statistics                          
      Client processing time            3319        
      Total execution time          3331        
      Wait time on server replies           12      
    

• 平均行大小4015B（后续可能增长），已将nvarchar改为varchar（仅存ASCII字符），优化效果甚微。
• ID字段已有聚集索引，但全量查询不会使用该索引，目前无法定位性能瓶颈。

执行计划XML #

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.539" Build="15.0.2101.7" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="8" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0032908" StatementText="SELECT *&#xD;&#xA;  FROM redacted.[dbo].[CalculatedModel]" StatementType="SELECT" QueryHash="0x3E4C27C2654A6D8E" QueryPlanHash="0x41958009A2AFC17B" RetrievedFromCache="true" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="1" CompileCPU="0" CompileMemory="88">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417267" EstimatedPagesCached="208633" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="14876512" />
            <TraceFlags IsCompileTime="true">
              <TraceFlag Value="8017" Scope="Global" />
            </TraceFlags>
            <TraceFlags IsCompileTime="false">
              <TraceFlag Value="8017" Scope="Global" />
            </TraceFlags>
            <WaitStats>
              <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="303" WaitCount="10541" />
            </WaitStats>
            <QueryTimeStats CpuTime="92" ElapsedTime="386" />
            <RelOp AvgRowSize="4051" EstimateCPU="0.0001658" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8" EstimatedRowsRead="8" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032908" TableCardinality="8">
              <OutputList>
                <ColumnReference Database="[redacted]" Schema="[dbo]" Table="[CalculatedModel]" Column="ID" />
                <ColumnReference Database="[redacted]" Schema="[dbo]" Table="[CalculatedModel]" Column="Model" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="8" ActualRowsRead="8" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[redacted]" Schema="[dbo]" Table="[CalculatedModel]" Column="ID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[redacted]" Schema="[dbo]" Table="[CalculatedModel]" Column="Model" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[redacted]" Schema="[dbo]" Table="[CalculatedModel]" Index="[PK_CalculatedModel]" IndexKind="Clustered" Storage="RowStore" />
              </IndexScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

代码实现 #

服务层代码 #

private string ListAllCalculatedUserModelsCacheKey => $"{_configuration.ClientID}-Service.ListAllCalculatedUserModels";

public List<ServiceModels.CalculatedUserModel> ListAllCalculatedUserModels()
{
    if (_memoryCache != null)
    {
        if (_memoryCache.TryGetValue(ListAllCalculatedUserModelsCacheKey, out var _cache)) return (List<ServiceModels.CalculatedUserModel>)_cache;
    }

    var value = new Data.CalculatedModel(_configuration.ConnectionString).List();
    if (_memoryCache != null)
    {
        _memoryCache.Set(ListAllCalculatedUserModelsCacheKey, value, new TimeSpan(16, 0, 0));
    }
    return value;
}

数据层代码 #

internal List<ServiceModels.CalculatedUserModel> List()
{
    var value = new List<ServiceModels.CalculatedUserModel>();
    using (var sql = new SqlServer(_connectionstring))
    {
        sql.NewCommand("CalculatedModel_List");
        // 以下行耗时约9秒
        var table = sql.Select().Tables[0];
        // 后续处理耗时1秒以内
        foreach (DataRow row in table.Rows)
        {
            value.Add(Newtonsoft.Json.JsonConvert.DeserializeObject<ServiceModels.CalculatedUserModel>(Conversion.ToString(row["Model"])));
        }
    }
    return value;
}

存储过程 #

CREATE PROCEDURE [dbo].[CalculatedModel_List]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM CalculatedModel
END
GO

说明：SELECT *语句本身耗时约9秒，后续JSON反序列化处理仅需1秒以内；即使在服务器本地用SSMS首次执行该查询，也耗时约9秒。

求问如何提升该表的读写速度？

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