SQL Server 2019 Express查询快，Standard版极慢的问题求助

开发者阿泽

2026-7-15

SQL Server 2019 Standard与Express版查询性能差异异常排查

问题描述

一段涉及临时表的复杂查询，在SQL Server 2019 Express版上执行耗时不足1秒，但在硬件配置相同（2 vCPU、8 GB内存）、数据集一致的SQL Server 2019 Standard版上，耗时超13分钟。

稳定复现环境

Cloud SQL For SQL Server（谷歌云托管）：约25分钟
Ubuntu Linux上的SQL Server 2019 Standard版：13分钟
Windows Server（GCP预制镜像）上的SQL Server 2019 Standard版：13分钟

关键现象

查询执行期间，单个CPU核心持续100%占用，其他核心闲置。
若注释掉查询WHERE子句中的任意两个AND条件，所有环境下执行耗时均为1-2秒。

查询语句

Declare
 @SubscriberId           INT                = 100,    
 @UserName               NVARCHAR(10)       = 'testUser',    
 @SiteId                 INT                = 10240,    
 @Category               NVARCHAR(MAX)      = '',    
 @AssetStatus            NVARCHAR(50)       = '',    
 @Upcoming               NVARCHAR(5)        = '',    
 @OverDue                NVARCHAR(5)        = '',    
 @ActionStatus           NVARCHAR(5)        = '',    
 @NextInspectionFromDate NVARCHAR(10)       = '',    
 @NextInspectionToDate   NVARCHAR(10)       = '',    
 @LastInspectionFromDate NVARCHAR(10)       = '',    
 @LastInspectionToDate   NVARCHAR(10)       = '',    
 @QuickSearch            NVARCHAR(MAX)      = '',    
 @Latitude               NVARCHAR(50)       = '',    
 @Longitude              NVARCHAR(50)       = '',    
 @AssignToFromDate       NVARCHAR(10)       = '',    
 @AssignToToDate         NVARCHAR(10)       = '',    
 @IsExpired              NVARCHAR(1)        = '',     
 @ExpiredFromDate        NVARCHAR(10)       = '',    
 @ExpiredToDate          NVARCHAR(10)       = '',    
 @AssetCategory          NVARCHAR(10)       = '2,3',    
 @APKID                  VARCHAR(MAX)       = '',    
 @CopyID                 NVARCHAR(500)      = '' 
    
    --Dynamic Label for Asset Status based on UserPersona --   
    Create Table #tmpAssetStatus (AssetStatusID INT, AssetStatusName NVARCHAR(100), ShortcutCode NVARCHAR(10), BaseAssetStatus INT, ColorCode NVARCHAR(20))   
    
    Insert into #tmpAssetStatus   
    Select * From ufn_RefAssetStatus(@SubscriberId, @Username)   
    
    DECLARE @Latitute DECIMAL(12, 9), @Longitute DECIMAL(12, 9), @SitePoint GEOGRAPHY, @GeoRadius INT   
    DECLARE @NoDate VARCHAR(10)= '1901/01/01'       
        
    IF ISNULL(@AssetStatus,'') = ''    
    BEGIN   
       SET @AssetStatus = '1,2,6,7'   
    END   
    
    SET @SitePoint = geography ::Point(@Latitude, @Longitude, 4326)   
    SET @GeoRadius =   
    (   
        SELECT GeoRadius   
        FROM SysPerson   
        WHERE Username = @UserName    
    )   
    DECLARE @InspectionInterval INT, @AlertInterval INT   
    SELECT
       @InspectionInterval = InspectionInterval,    
       @AlertInterval = AlertInterval   
    FROM SysSubscriber   
    WHERE ID = @SubscriberID   
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL   
    DROP TABLE #Temp   
    
    SELECT A.Id AS AssetId,    
       AssetName,    
       CAST(A.Description AS NVARCHAR(4000)) AS Description,   
       NULLIF(CAST(LastInspectionDate AS DATE),'1900-01-01') AS LastInspectionDate,    
       CompanyID,    
       SerialNumber,    
       CustomID,        
       NULLIF(CAST(NextInspectionDate AS DATE),'1900-01-01') AS NextInspectionDate,    
       ManufacturerName,    
       ModelNumber,    
       CAST(' ' AS VARCHAR(10)) AS Result,    
       ISNULL(A.TemplateName, '') AS TemplateName,    
       ISNULL(a.IsKit, 0) AS IsKit,    
       ISNULL(A.MasterAssetID, 0) AS MasterAssetID,    
       A.IsInspectionRequired,   
       CASE   
       WHEN JobCardStatus = 2   
       THEN NULL   
       ELSE A.JobCardID   
       END AS JobCardID,    
       A.AssetStatus,    
    ISNULL(A.ItemTypeID, 0) ItemTypeID, 
       C.name AS DeviceTypeName,    
       D.name AS SubType,    
       (CASE   
        WHEN A.AssetStatus IN(1, 2)   
         AND DATEDIFF(d, A.NextInspectionDate, GETDATE() + @AlertInterval) BETWEEN 0 AND @AlertInterval   
         AND A.NextInspectionDate IS NOT NULL   
         AND CONVERT(VARCHAR(10), A.NextInspectionDate, 111) > @NoDate   
        THEN 1   
        ELSE 0   
    END) AS NextInspectionDateFlag,    
       (CASE   
        WHEN A.AssetStatus IN(1, 2)   
         AND DATEDIFF(d, A.NextInspectionDate, GETDATE()) > 0   
         AND A.NextInspectionDate IS NOT NULL   
         AND CONVERT(VARCHAR(10), A.nextinspectiondate, 111) > @NoDate   
        THEN 1   
        ELSE 0   
    END) AS DueFlag,    
       NULLIF(CAST(AssignedDueDate as Date),'1900-01-01') AS AssignedDueDate,    
       (CASE   
        WHEN((A.UsageCount >= A.Servicelife   
      AND A.AssetStatus IN(1, 2)   
      AND A.UsageCount IS NOT NULL   
      AND A.Servicelife IS NOT NULL)   
         OR (DATEDIFF(DAY, GETDATE(), A.ExpiryDate) < 0   
         AND A.ExpiryDate IS NOT NULL))   
        THEN 1   
        ELSE 0   
    END) AS IsExpiry,    
       IsKitSchemaCompleted,    
       A.TemplateKey, 
       NULLIF(CAST(DateofFirstuse AS DATE),'1900-01-01') AS DateofFirstuse,   
       A.SiteName,   
       A.APKCategory as Category,   
       A.GenericID,   
       A.JobName as ProjectName,   
       A.[Standard],   
       A.ProductRating as LoadRating,   
       A.PurchaseOrderNumber,   
       A.Batch as Batch,   
       A.AssignedTo,   
       A.SubscriberName,   
       A.AssignedMobileNumber,   
       A.KitAssetName as KitName,   
       A.InspectionCalendar,   
       A.InspectionPeriod,   
       A.InspectionPeriodCount,   
       A.InspectionPeriodUnit,   
       A.UsageCount,   
       A.ServiceLife,   
       CAST(A.AnniversaryDate AS DATE) AS AnniversaryDate,   
       CAST(A.ExpiryDate AS DATE) AS ExpiryDate,   
       CAST(A.ManufactureDate AS DATE) AS ManufacturedDate,   
       A.Dimensions,   
       T.AssetStatusName,   
       T.ShortcutCode,   
       T.BaseAssetStatus,    
       T.ColorCode,   
       case
           when A.InspectionPeriod=1 then 'Daily'           
           when A.InspectionPeriod=2 then 'Weekly'           
           when A.InspectionPeriod=3 then 'Monthly'           
           when A.InspectionPeriod=4 then 'Yearly'            
           when A.InspectionPeriod=5 then 'Number'           
           when A.InspectionPeriod=6 then 'Quarterly'           
           when A.InspectionPeriod=7 then 'Half Yearly'           
       End as InspectionCycle,   
       InspectorDetails.FirstName+' '+InspectorDetails.LastName as InspectorName,   
       ReviewerDetails.FirstName+' '+ReviewerDetails.LastName as ReviewerName,   
       ISNULL(A.PurchasePrice,0.00) as PurchasePrice, 
       A.AssetLatitute as Latitude, 
       A.AssetLongitute as Longitude 
    INTO #Temp   
    FROM ScxAssetView A   
     LEFT OUTER JOIN insTemplateLatestView B ON A.TemplateKey = B.TemplateKey   
     LEFT OUTER JOIN RefItemType C ON A.ItemTypeID = C.ID   
     LEFT OUTER JOIN RefItemSubType D ON A.ItemSubTypeID = D.ID   
     Left Join sysPerson InspectorDetails on InspectorDetails.Username=A.Inspector    
     Left Join sysPerson ReviewerDetails on ReviewerDetails.Username=A.Reviewer    
     Left Join #tmpAssetStatus T ON T.BaseAssetStatus = A.AssetStatus   
    WHERE A.SubscriberID = @SubscriberId   
      AND A.SiteID = ISNULL(@SiteId, A.SiteID)   
     AND A.APKActive = 1   
     AND ((@AssetStatus = '')   
      OR AssetStatus IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT(@AssetStatus, ',')))   
     AND ((@Category = '')   
      OR A.ItemTypeID IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT(@Category, ',')))   
     AND ((@Upcoming = '')   
      OR (AssetStatus IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT('1,2,6,7', ','))   
      AND DATEDIFF(d, NextInspectionDate, GETDATE() + @AlertInterval) BETWEEN 0 AND @AlertInterval   
      AND NextInspectionDate IS NOT NULL   
      AND CONVERT(VARCHAR(10), NextInspectionDate, 111) > @NoDate))   
     AND ((@Overdue = '')   
      OR (AssetStatus IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT('1,2,6,7', ','))   
      AND DATEDIFF(d, NextInspectionDate, GETDATE()) > 0   
      AND NextInspectionDate IS NOT NULL   
      AND CONVERT(VARCHAR(10), NextInspectionDate, 111) > @NoDate))   
     AND ((@NextInspectionFromDate = ''   
       AND @NextInspectionToDate = '')   
      OR (CAST(NextInspectionDate AS DATE) BETWEEN CAST(@NextInspectionFromDate AS DATE) AND CAST(@NextInspectionToDate AS DATE)))   
     AND ((@LastInspectionFromDate = ''   
       AND @LastInspectionToDate = '')   
      OR (CAST(LastInspectionDate AS DATE) BETWEEN CAST(@LastInspectionFromDate AS DATE) AND CAST(@LastInspectionToDate AS DATE)))   
     AND ((@AssignToFromDate = ''   
       AND @AssignToToDate = '')   
      OR (CAST(AssignedDueDate AS DATE) BETWEEN CAST(@AssignToFromDate AS DATE) AND CAST(@AssignToToDate AS DATE)))   
     AND (@QuickSearch = ''   
      OR (A.AssetName LIKE '%' + @QuickSearch + '%'))   
     AND (@QuickSearch = ''   
      OR (AssignedTo LIKE '%' + @QuickSearch + '%'))   
     AND 1 = CASE   
         WHEN @Latitude = ''   
          OR @Longitude = ''   
          OR @Latitude IS NULL   
          OR @Longitude IS NULL   
         THEN 1   
         WHEN AssetLatitute IS NOT NULL   
          AND AssetLongitute IS NOT NULL   
          AND AssetLatitute != 0   
          AND AssetLongitute != 0   
          AND @SitePoint.STDistance(geography::Point(AssetLatitute, AssetLongitute, 4326)) <= @GeoRadius   
         THEN 1   
     END   
    AND ((@IsExpired = '')   
     OR (IsExpiry = @IsExpired))   
    AND ((@ExpiredFromDate = ''   
      AND @ExpiredToDate = '')   
     OR (CAST(ExpiryDate AS DATE) BETWEEN CAST(@ExpiredFromDate AS DATE) AND CAST(@ExpiredToDate AS DATE)))   
    AND APKCategory IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT(@AssetCategory, ','))   
    AND (@APKID = ''   
     OR A.ID IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT(@APKID, ',')))   
    AND (@CopyID = '' OR A.CopyID IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT(@CopyID, ',')))   
    ORDER BY A.ID DESC                                         
   
    Select * From #Temp
 
    Drop Table #tmpAssetStatus

排查方向与解决建议

执行计划对比分析
- 在两个版本分别生成并对比执行计划（可使用SET SHOWPLAN_XML ON;或SSMS图形化工具），重点关注：
  - 表/视图的扫描方式差异（索引扫描vs表扫描）
  - 表连接算法的选择（Nested Loops/Hash Match/Merge Join）
  - 临时表的统计信息是否缺失
  - STRING_SPLIT函数的执行次数与效率
统计信息与索引检查
- 手动更新Standard版核心表/视图的统计信息：UPDATE STATISTICS ScxAssetView;，其他涉及表同理
- 检查ScxAssetView底层表是否存在针对过滤字段（如SubscriberID、SiteID、APKActive、AssetStatus）的有效索引
查询逻辑优化
- 移除WHERE子句中对列的函数转换（如CAST(NextInspectionDate AS DATE)），可提前将参数转换为日期类型，或修改表结构存储原生日期字段
- 将重复的STRING_SPLIT调用结果存入临时表，避免多次拆分计算
- 提取重复的条件判断（如AssetStatus IN(1,2,6,7)）到变量或临时表，简化CASE表达式逻辑
版本特性与配置检查
- 对比两个版本的CU（累积更新）版本，补丁差异可能导致执行计划选择异常
- 检查Standard版的并行执行配置：执行sp_configure 'max degree of parallelism'，确认是否限制了并行度
- 验证是否开启了Query Store，查看是否有执行计划退化情况
临时表优化
- 为临时表#tmpAssetStatus添加主键或索引：CREATE CLUSTERED INDEX IX_tmpAssetStatus_BaseAssetStatus ON #tmpAssetStatus(BaseAssetStatus);，提升连接效率

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

SQL Server 2019 Express查询快，Standard版极慢的问题求助

问题描述 #

稳定复现环境 #

关键现象 #

查询语句 #

排查方向与解决建议 #

问题描述

稳定复现环境

关键现象

查询语句

排查方向与解决建议