SQL Server分布式表查询性能优化求助：高并发下响应迟缓

架构老林

2026-8-7

分布式大表高并发查询性能优化求助

问题背景

涉及的JobOrder_PerfTable是一张分布式表，包含42列、170万条记录，仅少量为int列，其余均为varchar文本字段。该表日常有1万条插入、约25万条更新操作，日均需响应55万次查询。由于关联大量应用层业务规则，查询包含12种筛选条件，需返回42列中的34列，且所有列支持排序和搜索，因此采用动态SQL实现，存储过程如下：

ALTER PROCEDURE [dbo].[jo_JobOrder_PerfIndex_Test] 
            @companyID int,--255
            @state int,--null
            @status int,
            @skip int,
            @take int,
            @filterDateType int,
            @technicianID int,
            @filterModelID int,
            @filterCustomerID int,
            @pastTenDate datetime,
            @CategoryID int,
            @TeamID int,
            @CustomSearchParam nvarchar(100),
            @FilterStartDate datetime,
            @FilterEndDate datetime,
            @AssociatedCustomerFilter int,
            @OrderByColumn varchar(50),
            @OrderByDirection nvarchar(50),
            @ForceStrictlyCompany int,
            @deviceType nvarchar(50)
AS
DECLARE @sql nvarchar(max)

SET @Sql=';WITH TempResult AS(Select JobOrder_PerfTable.JobOrderID,ReferenceID,Customer,SerialNo,DeviceName,DeviceTypeName,DeviceBrand,StateID,Staff,JobOrder_PerfTable.GsmNo,StartDate,EndDate,ActualStartDate,ActualEndDate,RelatedFirm,AppointmentDate,ServiceType,CompanyName,Cost,1 as [PassedTime],JobOrder_PerfTable.Address,Description,
Province,District,CurrentFullFilled,DealerTrackCode,CargoDetails,NotifiedFault,State,TagName,JobOrder_PerfTable.SortID,JobOrder_PerfTable.CompanyID,JobOrder_PerfTable.PhoneNumber,JobOrder_PerfTable.Importance,
JobOrder_PerfTable.Status,null as CategoryID,JobOrder_PerfTable.RepeatCount,JobOrder_PerfTable.Attributes,JobOrder_PerfTable.DeliveryShipmentNo,JobOrder_PerfTable.CreatedBy,
0 as [DeviceChange],
0 as [DeviceReturn],
0 as [SNORepeat]
 from JobOrder_PerfTable
 WITH(NOLOCK)
inner join Companies On Companies.CompanyID=JobOrder_PerfTable.CompanyID
WHERE  ((@state is null or JobOrder_PerfTable.StateID=@state) OR @state=-2 AND JobOrder_PerfTable.JobOrderID IN ( select jobOrderID from attendedStaffStatistics where StaffID=@technicianID AND CONVERT(date,AttendedStaffStatistics.InsertDate, 105)=CONVERT(date, getdate(), 105) ))
            AND ((@status = -1 AND JobOrder_PerfTable.Status IN(0,1,10,11,4)) OR (@status=1 AND JobOrder_PerfTable.Status IN(1,4,10,11)) OR
             (@status=2 AND JobOrder_PerfTable.Status IN (0))) AND (@CategoryID is null or JobOrder_PerfTable.CategoryID=@CategoryID)
             AND (@AssociatedCustomerFilter is null or Joborder_PerfTable.CustomerID IN (Select CustomerID from Customers where RelatedFirmID IN (select CustomerID from StaffAssignedToCustomer where StaffID=@AssociatedCustomerFilter) UNION select CustomerID from StaffAssignedToCustomer where StaffID=@AssociatedCustomerFilter))
             AND ((@ForceStrictlyCompany>0 AND JobOrder_PerfTable.CompanyID=@ForceStrictlyCompany) OR (@ForceStrictlyCompany is null AND (JobOrder_PerfTable.CompanyID=@companyID OR Companies.SubCompanyOf=@companyID)))
                 '+ ( CASE WHEN @CustomSearchParam='' THEN '' ELSE (Select dbo.[perf_likeBuilder](@CustomSearchParam)) END)+'
             AND (@technicianID is null or JobOrder_PerfTable.JobOrderID IN (Select AttendedStaff.JobOrderID from AttendedStaff Where AttendedStaff.StaffID=@technicianID))
             AND (@pastTenDate is null or  JobOrder_PerfTable.StartDate<@pastTenDate)
             AND (@filterModelID is null or JobOrder_PerfTable.DeviceModelID=@filterModelID)
             AND (@filterCustomerID is null or JobOrder_PerfTable.CustomerID=@filterCustomerID)
             AND (@deviceType is null or JobOrder_PerfTable.DeviceTypeName=@deviceType)
             AND ((@filterDateType=1 AND (@FilterStartDate is null or convert(datetime, JobOrder_PerfTable.StartDate, 20) between @FilterStartDate and @FilterEndDate)) OR (@filterDateType=2 AND (@FilterStartDate is null or convert(datetime, JobOrder_PerfTable.EndDate, 20) between @FilterStartDate and @FilterEndDate)))
             ), TotalCount AS (Select COUNT(*) as TotalCount from TempResult) 
             
             Select * from TempResult, TotalCount 
          order by '+@OrderByColumn+' '+@OrderByDirection+'

            OFFSET     @skip ROWS      
FETCH NEXT @take ROWS ONLY 
OPTION (RECOMPILE); ';


EXECUTE sp_executesql  @sql, N'@companyID int,
            @state int,
            @status int,
            @skip int,@take int,
            @filterDateType int,
            @technicianID int,
            @filterModelID int,
            @filterCustomerID int,
            @pastTenDate datetime,
            @CategoryID int,
            @TeamID int,
            @CustomSearchParam nvarchar(100),
            @FilterStartDate datetime,
            @FilterEndDate datetime,
            @AssociatedCustomerFilter int,
            @ForceStrictlyCompany int,
            @deviceType nvarchar(50)',
            @companyID=@companyID,
            @state=@state,
            @status=@status,
            @skip=@skip,
            @take=@take,
            @filterDateType=@filterDateType,
            @technicianID=@technicianID,
            @filterModelID=@filterModelID,
            @filterCustomerID=@filterCustomerID,
            @pastTenDate=@pastTenDate,
            @CategoryID=@CategoryID,            
            @TeamID=@TeamID,
            @CustomSearchParam=@CustomSearchParam,
            @FilterStartDate=@FilterStartDate,
            @FilterEndDate=@FilterEndDate,
            @AssociatedCustomerFilter=@AssociatedCustomerFilter,
            @ForceStrictlyCompany=@ForceStrictlyCompany,
            @deviceType=@deviceType

已尝试的优化操作

为WHERE子句中多数列创建索引
将动态SQL改为常规查询，无性能变化
添加OPTION(RECOMPILE)解决执行计划或参数嗅探问题
使用WITH(NOLOCK)应对频繁更新

已知要点

全表查询性能极差
过多索引会显著影响更新操作的效率
索引未覆盖查询列会出现键查找，但无法将35列全部加入索引
键查找并非一定会导致性能问题
并行执行可能因关联表（仅5000条数据）的索引问题变慢

当前问题

该查询最快耗时3秒，占用大量CPU资源；当有10次并发查询时，系统响应严重缓慢，恳请提供针对性的优化建议。

优化建议

1. 索引策略优化

组合索引+包含列：分析高频查询的筛选条件组合（如CompanyID+Status+StateID这类高选择性组合），创建组合索引，将排序常用字段加入索引键，同时把查询需要的核心字段作为包含列（INCLUDE），减少键查找开销。优先覆盖高频查询场景，避免单字段索引。
特殊场景索引优化：针对@state=-2的查询逻辑，给attendedStaffStatistics表创建StaffID+InsertDate的组合索引，同时将日期判断改为无转换的范围查询：InsertDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND InsertDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())+1, 0)，避免每条记录都执行日期转换。
文本搜索优化：若业务允许，用全文索引替代perf_likeBuilder生成的LIKE模糊查询，全文索引的模糊搜索性能远高于原生LIKE，尤其适合大文本字段。

2. 查询逻辑重构

拆分统计与分页查询：将总条数统计与数据分页查询分离，先获取筛选后的JobOrderID集合存入临时表，再基于该集合统计总数和查询详情，避免重复执行筛选逻辑。
简化嵌套子查询：将AssociatedCustomerFilter对应的UNION子查询改为提前计算符合条件的CustomerID集合存入临时表，或改用EXISTS关联查询，减少嵌套查询的重复执行。
消除日期转换损耗：若StartDate/EndDate存储为varchar类型，改为datetime类型；若无法修改表结构，提前将@FilterStartDate/@FilterEndDate转换为对应格式，避免每条记录执行转换操作。

3. 分页与排序优化

替换大偏移量OFFSET：当@skip值较大时，用基于键的分页替代OFFSET ... FETCH，记录上一页最后一条的JobOrderID和排序字段值，下一页查询时用WHERE [排序字段] > [上一页值] AND ...过滤，避免扫描大量无关数据。
确保排序字段在索引中：@OrderByColumn对应的字段必须存在于索引键或包含列中，避免排序时的磁盘排序（Sort操作），这是CPU占用高的核心原因之一。

4. 并发与架构优化

读写分离：将查询流量引导到只读副本，降低主库读写压力，减少锁冲突。
调整隔离级别：若业务允许，启用READ COMMITTED SNAPSHOT ISOLATION（RCSI），替代WITH(NOLOCK)，在不读取脏数据的前提下减少读写阻塞。

5. 动态SQL与存储过程优化

精简动态SQL拼接：只拼接有值的筛选条件，生成更简洁的SQL语句，减少SQL Server的解析和执行计划生成开销。
拆分高频场景存储过程：针对高频参数组合创建专门的存储过程，避免每次都通过OPTION(RECOMPILE)重新编译执行计划。

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

SQL Server分布式表查询性能优化求助：高并发下响应迟缓

问题背景 #

已尝试的优化操作 #

已知要点 #

当前问题 #

优化建议 #