SQL Server 2016中T-SQL慢查询调优方案咨询

针对你给出的UPDATE查询，我来拆解一下性能瓶颈的根源，并给出具体的调优步骤——核心问题在于JOIN条件里的自定义标量函数和非SARGable表达式，咱们一步步来优化：

1. 干掉性能杀手：自定义标量函数dbo.fReplace #

标量值函数在JOIN条件里调用会触发逐行计算（RBAR模式），这是SQL Server里性能极差的操作之一。而且你的函数只是去除非数字字符，完全可以用更高效的方式替代，或者提前计算结果：

替代方案A：用内置函数替换标量函数 #

SQL Server 2016支持TRANSLATE函数，配合REPLACE可以快速去除所有非数字字符，写法如下：

-- 替代dbo.fReplace的逻辑，直接嵌入查询
REPLACE(TRANSLATE(your_column, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!@#$%^&*()_+-=', REPLICATE(' ', 62)), ' ', '')

这个写法是集合式操作，比标量函数快得多，避免了逐行调用的开销。

替代方案B：新增持久化计算列+索引（推荐） #

如果这个非数字处理的逻辑是高频使用的，直接给两个表新增持久化计算列，并创建覆盖索引，从根源上解决函数调用的问题：

-- 给Table1添加持久化计算列并创建索引
ALTER TABLE [Table1] 
ADD [Project Definition_Numeric] AS 
REPLACE(TRANSLATE([Project Definition], 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!@#$%^&*()_+-=', REPLICATE(' ', 62)), ' ', '') 
PERSISTED;

CREATE NONCLUSTERED INDEX IX_Table1_ProjectDefNumeric 
ON [Table1]([Project Definition_Numeric]) 
INCLUDE ([Project Definition], [Project Description]);

-- 给Table2添加持久化计算列并创建索引
ALTER TABLE [Table2] 
ADD [PROJECT #_Numeric] AS 
REPLACE(TRANSLATE([PROJECT #], 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!@#$%^&*()_+-=', REPLICATE(' ', 62)), ' ', '') 
PERSISTED;

CREATE NONCLUSTERED INDEX IX_Table2_ProjectNumNumeric 
ON [Table2]([PROJECT #_Numeric]) 
INCLUDE ([Project], [Project Name]);

之后你的JOIN条件就可以直接用计算列，完全避免函数调用，而且索引会被SQL Server高效利用，大幅提升JOIN的速度。

2. 优化DIFFERENCE函数的开销 #

DIFFERENCE函数是计算两个字符串的SOUNDEX差异，本身也是非SARGable的，无法利用索引。可以通过提前计算SOUNDEX值来减少重复计算：

新增SOUNDEX计算列 #

-- 给Table1添加SOUNDEX计算列
ALTER TABLE [Table1] 
ADD [Project Description_Soundex] AS SOUNDEX([Project Description]) 
PERSISTED;

-- 给Table2添加SOUNDEX计算列
ALTER TABLE [Table2] 
ADD [Project Name_Soundex] AS SOUNDEX([Project Name]) 
PERSISTED;

之后查询里的DIFFERENCE条件可以改成：

DIFFERENCE(a.[Project Description_Soundex], b.[Project Name_Soundex]) >= 3

虽然还是要计算DIFFERENCE，但提前预存SOUNDEX值可以减少重复计算的开销，尤其是在大表场景下效果明显。

如果业务场景允许，甚至可以直接匹配SOUNDEX值（a.[Project Description_Soundex] = b.[Project Name_Soundex]），这样就能利用索引进一步提速，但这需要确认是否符合你的业务需求。

3. 优化UPDATE的执行逻辑 #

如果Table1的数据量很大，一次性UPDATE会导致锁表、事务日志暴涨，甚至阻塞其他业务操作。可以用分批更新的方式来缓解：

DECLARE @BatchSize INT = 1000; -- 根据你的服务器性能调整批次大小

WHILE EXISTS (
    SELECT 1 
    FROM [Table1] a 
    INNER JOIN [Table2] b 
        ON a.[Project Definition_Numeric] = b.[PROJECT #_Numeric] 
        AND a.[Project Definition] <> '' 
        AND DIFFERENCE(a.[Project Description_Soundex], b.[Project Name_Soundex]) >= 3
)
BEGIN
    UPDATE TOP(@BatchSize) a
    SET a.[Project Definition] = b.[Project]
    FROM [Table1] a
    INNER JOIN [Table2] b 
        ON a.[Project Definition_Numeric] = b.[PROJECT #_Numeric] 
        AND a.[Project Definition] <> '' 
        AND DIFFERENCE(a.[Project Description_Soundex], b.[Project Name_Soundex]) >= 3;
    
    WAITFOR DELAY '00:00:01'; -- 可选，给服务器留一点喘息时间，避免资源耗尽
END

分批更新可以减少锁的持有时间，降低对系统的影响。

4. 基础优化：更新统计信息+分析执行计划 #

• 确保两个表的统计信息是最新的，执行下面的语句：

  UPDATE STATISTICS [Table1] WITH FULLSCAN;
  UPDATE STATISTICS [Table2] WITH FULLSCAN;
  

  最新的统计信息能让SQL Server生成更准确的执行计划。
• 查看查询的执行计划，重点关注是否有表扫描、键查找、哈希警告等问题：
  • 如果有表扫描，说明缺少合适的索引；
  • 如果有键查找，需要调整索引的INCLUDE子句，把需要的字段包含进去，避免回表查找。

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