值班那天的告警铃声我现在还记得很清楚。生产环境一个跑了一年多的存储过程平时平均执行时间 100 到 200 毫秒那天晚上突然飙到 20 多秒并且连带拖慢了同库其他查询。第一反应都是很常规的看阻塞、找慢查询、查索引碎片。结果一圈折腾下来问题居然出在存储过程里一个不起眼的临时表上。这算是 SQL 性能问题排查里非常典型的一类案例临时表本身在代码里看着人畜无害但在特定数据和执行计划条件下它会变成一个隐藏的陷阱让优化器做出完全错误的判断。这篇就把我当时完整的排查思路、根因定位过程和最终的重写方案拆开写清楚遇到类似情况的同学可以直接照着捋一遍。1. 一个运行良好的存储过程为什么突然从 200ms 变成了 20s先把这个存储过程的逻辑简化一下它本身并不复杂是做订单汇总报表用的CREATE PROCEDURE dbo.GetCustomerOrderSummary StartDate DATE, EndDate DATE AS BEGIN SET NOCOUNT ON; -- 第一步把符合条件的订单先放入临时表 SELECT o.CustomerID, o.OrderID, o.OrderDate INTO #RecentOrders FROM dbo.Orders o WHERE o.OrderDate BETWEEN StartDate AND EndDate; -- 第二步临时表再与客户表、订单明细表关联做聚合 SELECT c.CustomerName, COUNT(DISTINCT r.OrderID) AS OrderCount, SUM(od.Quantity * od.UnitPrice) AS TotalAmount FROM #RecentOrders r JOIN dbo.Customers c ON c.CustomerID r.CustomerID JOIN dbo.OrderDetails od ON od.OrderID r.OrderID GROUP BY c.CustomerName ORDER BY TotalAmount DESC; END如果你以前没怎么接触过 SQL Server 的临时表优化机制你看到这段代码大概会觉得先筛订单存到一个临时结果集里再拿这个结果集去和其他表关联这很合理啊而且还能让第二步的查询更清爽。问题恰恰就出在这个合理上。1.1 报警当天的现象那天晚上监控最先报警的其实不是这个存储过程本身而是大量PAGEIOLATCH_SH等待异常升高。懂的人清楚PAGEIOLATCH_SH是查询在等待把数据页从磁盘读入内存等待飙升说明系统里出现了大量的物理 IO 读取。当时整个实例的 CPU 倒不算高但磁盘队列长度非常高像是有什么东西在疯狂扫表。打开活动监视器一看罪魁祸首是GetCustomerOrderSummary而且已经形成了一个很长的阻塞链后面的查询都在等它释放锁。一个本来毫秒级的过程突然变成秒级常见原因无非是缺少索引、统计信息过期、参数嗅探、数据量突变。我当时第一反应也是先看它的执行计划。1.2 初次看到执行计划的疑惑我用实际执行的存储过程抓了一份实际执行计划注意不是估计计划发现第二步的 JOIN 操作是一个嵌套循环连接Nested Loops而驱动侧是#RecentOrders临时表。单独看嵌套循环本身没问题它适合驱动表行数很少的场景。我立刻把鼠标移到临时表扫描这个运算符上看到了一个明显让人起疑的数值估计行数只有 55 行。但当时实际跑出来的行数是多少我回头查了下这个存储过程的参数StartDate和EndDate覆盖了将近一个月的数据Orders 表里符合条件的有 40 多万行。也就是说实际参与连接的行数和优化器估计的行数差了将近 8000 倍。这个巨大的误差直接导致优化器认为嵌套循环是高效的但实际上它要对 40 万行数据中的每一行去 OrderDetails 里做一次查找累计的 IO 开销完全失控。所以问题的核心不是嵌套循环这个算子有什么错而是优化器根本不知道临时表里到底有多少行。2. 临时表陷阱的根因统计信息缺失带来的连锁反应很多人知道索引对查询性能影响大但统计信息经常被忽略。所谓统计信息说白了就是优化器用来估算这个表有多少行、这个列上值的分布大概是什么样的元数据。优化器为什么选择哈希连接而不是嵌套循环为什么预估返回 100 行时选索引查找预估返回 100 万行时直接全表扫描这些都是靠统计信息来推算的。普通用户表在执行CREATE INDEX或者自动更新统计信息后优化器能比较准确地估算行数。可临时表有它的特殊性。2.1 临时表不会像普通表那样维护统计信息#RecentOrders是用SELECT ... INTO创建的。SELECT INTO有一个特点它在建表的同时插入数据但它的统计信息维护策略和普通表不一样。SQL Server 在临时表创建后的首次编译时优化器可能还没来得及收集统计信息只能使用默认猜测值。在不同版本和不同场景下这个初始猜测值可能很小比如几十行。即使后来临时表上的统计信息被自动创建了它也不会像普通表那样在数据变更达到阈值时自动更新。临时表里的数据在执行过程中是动态产生的优化器没法预知它的行数。尤其是当存储过程的执行计划被缓存后第二次、第三次执行时如果参数范围变大临时表里实际行数暴涨但计划还是基于第一次编译时的判断来走的。用一句话概括普通表的统计信息是相对稳定且会被自动维护的临时表的统计信息要么缺失要么容易过期这是它成为性能陷阱的根本原因。2.2 统计信息失真如何一步步带偏执行计划我们来推演一下那个存储过程当时经历了什么。第一次执行时传入的参数可能只是当天的数据Orders 里符合条件的行数并不多可能就几千行。临时表数据量小嵌套循环连接完全够用执行计划也很好看很快完成。但这个执行计划被缓存了下来。第二天或者第三天业务方传了一个大范围的参数比如一个月的数据。临时表在每次执行时都会重新创建重新插入数据可是优化器在编译时看临时表还是一张新表它不知道这次插入了 40 万行继续沿用旧计划里对临时表行数的小估算。于是执行计划就变成了这样Orders 表正常做范围筛选插入了大量数据到临时表优化器预估临时表只有几十行选择用临时表作为嵌套循环的驱动表对临时表的每一行去 OrderDetails 上做一次索引查找实际行数是 40 万嵌套循环就执行了 40 万次索引查找大量物理读等待飙升。这种问题最隐蔽的地方在于代码没有变索引也都存在统计信息你去看 Orders 表和 OrderDetails 表也都是正常的。只有临时表那一块的估计行数和实际行数差了一大截。不看实际执行计划很难一眼锁定问题。2.3 参数嗅探为什么会在这时候叠加这类场景还特别容易叠加参数嗅探问题。所谓参数嗅探就是存储过程第一次执行时用的参数值决定了计划的样子后续不管传什么参数SQL Server 都倾向于复用这个计划。正常表有一定的统计信息兜底参数嗅探的影响还可以通过更新统计信息来缓解但临时表几乎没有兜底能力参数一变行数剧烈变化问题就会被放大好几倍。那天晚上这个存储过程很明显就是被一个大参数触发的。如果它一直处理的数据量都那么小这个 bug 可能永远都不会暴露。所以这类问题不是代码写错了这么简单而是数据和参数分布的多变性终于在某一个阈值点引爆了。3. 还原当时完整的排查链路从等待类型到执行计划对比很多后台开发遇到慢 SQL 第一反应是加索引但加索引只对索引缺失导致的性能问题有效。如果根因是执行计划判断失误加再多索引也救不回来甚至可能因为多了无用索引反而拖慢写入。下面把当时从现象到根因的完整链路还原一遍你可以直接当成一份排查清单用。3.1 先抓会话和等待类型别急着看语句告警出现后我第一时间执行了下面这一组查询目的是看当前实例上到底在等什么SELECT er.session_id, er.blocking_session_id, er.wait_type, er.wait_time, er.cpu_time, er.reads, er.writes, st.text FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st WHERE er.session_id 50 ORDER BY er.wait_time DESC;sys.dm_exec_requests里可以实时看到每一个正在执行的请求的等待类型、阻塞会话、CPU 时间和读写次数。当时出来的结果里wait_type大面积是PAGEIOLATCH_SH说明问题出在物理 IO 读取上而不是锁等待。被阻塞的会话虽然也有LCK_M_X之类的等待但那是因为有人在长时间占用资源。顺着blocking_session_id一层层往上追最顶端的源头就是GetCustomerOrderSummary。这一步最大的价值是确定了排查方向这是磁盘 IO 瓶颈驱动的查询性能问题和死锁、锁升级完全是两码事。方向错了后面怎么调都是白费力气。3.2 用 SET STATISTICS IO 对比不同参数下的逻辑读接着我用两个不同范围的参数分别跑了一次存储过程同时打开统计信息输出SET STATISTICS IO ON; SET STATISTICS TIME ON; EXEC dbo.GetCustomerOrderSummary StartDate 2024-06-01, EndDate 2024-06-01; EXEC dbo.GetCustomerOrderSummary StartDate 2024-06-01, EndDate 2024-06-30;结果非常直观单日参数执行逻辑读只有几千一个月参数执行逻辑读直接冲到了百万级。百万级逻辑读对于这种量级的报表查询显然不正常。然后我在会话里开启SET STATISTICS PROFILE ON再执行一次逐步看每一步操作的实际 IO。走到#RecentOrders和OrderDetails的嵌套循环连接时那个Scan Count和数据页读取次数非常吓人基本可以断定是连接策略选错了。3.3 对比实际执行计划中的预估行数和实际行数如果你没有SET STATISTICS PROFILE直接用 SSMS 的包含实际执行计划也可以。拿到执行计划后重点观察这几个运算符的属性Estimated Number of Rows估计行数Actual Number of Rows实际行数Estimated Number of Rows to be Read估计读取行数当这两类数值差距超过一个数量级时执行计划就已经在错误的方向上走了。我当时看到的临时表扫描的估计行数只有 55实际行数是 41 万。这不是统计信息轻微过期的问题是优化器完全没有能参考的有效信息。3.4 查计划缓存确认参数嗅探痕迹为了验证是不是参数嗅探我查了一下计划缓存里这个存储过程的执行记录SELECT TOP 10 execution_count, total_worker_time, total_logical_reads, total_elapsed_time, total_elapsed_time / execution_count AS avg_elapsed_time, st.text, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE st.text LIKE %GetCustomerOrderSummary% ORDER BY qs.total_elapsed_time DESC;sys.dm_exec_query_stats记录了计划缓存里各条语句的聚合性能信息能看到执行了多少次、平均耗时多少、逻辑读多少。当时查出来这个存储过程最近几十次执行的表现严重分化小参数执行非常快大参数执行慢得离谱这就和参数嗅探的表现完全吻合。这里要特别说明一点参数嗅探本身不是 bug它是 SQL Server 为了复用执行计划而做的优化。只有在参数导致数据量分布差异巨大的时候它才会变成问题。所以排查的目标不是禁用参数嗅探而是让优化器在一个合理的前提下生成稳定的计划。4. 临时表性能陷阱的几种解法与实测对比定位到根因之后接下来就是怎么改的问题了。网上说到这种情况常见的方案无非是改成 CTE、表变量、给临时表加索引、加OPTION (RECOMPILE)。但这些方案不是所有场景都适用我逐个试过每一步都有不同的坑。下面是我当时的实测结论。4.1 方案一把临时表改成 CTE 或派生表——不一定能解决问题一个很自然的想法是既然临时表统计信息不可靠那干脆别用临时表把中间结果集内联到一条查询里用 CTE 或者派生表实现;WITH RecentOrders AS ( SELECT o.CustomerID, o.OrderID, o.OrderDate FROM dbo.Orders o WHERE o.OrderDate BETWEEN StartDate AND EndDate ) SELECT c.CustomerName, COUNT(DISTINCT r.OrderID) AS OrderCount, SUM(od.Quantity * od.UnitPrice) AS TotalAmount FROM RecentOrders r JOIN dbo.Customers c ON c.CustomerID r.CustomerID JOIN dbo.OrderDetails od ON od.OrderID r.OrderID GROUP BY c.CustomerName ORDER BY TotalAmount DESC;这段代码在逻辑上和原来的临时表版本完全等价而且更简洁。但实测下来它的问题在于CTE 只是语法糖优化器会把 CTE 展开成底层查询然后重新估算 Orders 表扫描后的行数。Orders 表是有统计信息的所以这一步估算通常比临时表准确很多但如果你在 CTE 里做了非常复杂的计算或者一个 CTE 在查询里被引用了多次优化器可能会为每一次引用重新独立评估反而导致重复扫描和重复计算。我那次测试的 SQL 语句比较简单CTE 版本执行计划里对 Orders 表范围的预估有统计信息支撑实际跑出来的逻辑读比临时表版本好了太多。所以如果你的场景只是这种简单的筛选后关联CTE 替换往往是最干净的解法。但如果说你的步骤非常多中间结果还需要多次使用这个方案就不太好使了。4.2 方案二给临时表加索引并显式更新统计信息——针对性强但要注意开销另一个思路是保留临时表但主动把优化器缺失的情报补上。给临时表创建索引索引创建过程中会自动生成统计信息优化器就能看到它大概有多少行JOIN 时也会更聪明。我在原流程的SELECT INTO后面加了几行CREATE NONCLUSTERED INDEX IX_RecentOrders_CustomerID ON #RecentOrders(CustomerID);效果非常明显。创建完索引后重新执行大参数查询优化器对临时表的估计行数变得接近实际情况执行计划从嵌套循环切换成了哈希连接逻辑读从百万级降到了几万级。这个方法非常适合中间结果确实需要多次复用的场景。但这里有个容易被忽略的代价给临时表创建索引本身是有开销的。临时表是创建在 tempdb 里的插入数据后还要再扫描一遍数据去建索引数据量越大这个额外 IO 越明显。如果临时表本来就很大创建索引浪费的时间可能让你得不偿失。所以这个方案比较适合中间结果集虽然大但后续要反复JOIN和过滤的场景如果临时表只用一次就不值得建索引。4.3 方案三改用表变量——老版本 SQL Server 上更容易踩坑我看到很多人遇到临时表性能问题第一反应是那把临时表改成表变量试试。这里必须说清楚表变量在某些时候确实能避免重编译和事务日志开销但它有自己的问题。在 SQL Server 2012 之前优化器对表变量的行数估计几乎总是固定值 1在基于行数估计的连接策略选择上表变量比临时表更不靠谱。如果你把 40 万行数据塞进一个表变量再拿它去 JOIN那个执行计划大概率惨不忍睹。SQL Server 2019 引入了表变量延迟编译Table Variable Deferred Compilation之后情况改善了不少但它依然不会像临时表那样自动更新统计信息。当时的服务器是 SQL Server 2016我实测了表变量方案结果比临时表方案还要差。所以我的建议是不要无脑用表变量替代临时表。表变量的适用场景是数据量很小几百行以内、需要避免不必要的重编译的时候一旦数据量上来它可能比临时表更危险。4.4 方案四加 OPTION (RECOMPILE)——治标不治本但能救命在生产紧急修复时我还试过在存储过程末尾加OPTION (RECOMPILE)SELECT ... FROM #RecentOrders r JOIN dbo.Customers c ON c.CustomerID r.CustomerID JOIN dbo.OrderDetails od ON od.OrderID r.OrderID GROUP BY c.CustomerName ORDER BY TotalAmount DESC OPTION (RECOMPILE);OPTION (RECOMPILE)会让 SQL Server 在每次执行这条语句时都重新编译生成新的执行计划。这样一来优化器会在编译时重新评估临时表当前的数据量从而避免复用那个错误的缓存计划。实测效果确实立竿见影大参数查询的性能直接恢复正常。但这只是治标而不是治本。每次重编译都会有 CPU 开销如果这个存储过程被高频率调用CPU 压力会明显上升。我当时用这个方法先顶住生产随后还是把代码改成了更合理的写法。作为紧急止血手段OPTION (RECOMPILE)是可以接受的但我不建议把它当成长期方案到处贴。4.5 各方案实测效果对比为了让大家直观感受我整理了一下在同样的 40 万行中间结果条件下各个方案在同一台测试机上的对比结果方案逻辑读约执行时间约适用场景风险原临时表无索引120 万18s数据量小、范围稳定数据量大时执行计划崩坏CTE/派生表重写6 万1.2s中间结果只需用一次复杂 CTE 多次引用可能导致重复扫描临时表索引5 万1.0s中间结果需多次复用建索引本身有额外 IO 开销表变量200 万30s数据量很小的场景老版本上预估行数固定为1临时表RECOMPILE5 万1.1s紧急修复、参数分布极不均每次重编译带来 CPU 开销临时表手动更新统计信息5 万1.0s能接受手动调优步骤需要额外代码维护统计信息很明显在这个案例里SQL 重写CTE 方案和临时表加索引是效果最好的两个方向。二选一取决于后续代码结构如果中间结果只使用一次我倾向于重写成 CTE让优化器直接基于基表统计信息做判断如果中间结果要跨多个步骤反复使用那就保留临时表但一定记得建索引。5. 临时表并不是不能用关键是知道它的边界这次排查结束之后我并没有从此逢临时表就指标签因为在真实业务里临时表仍然是处理复杂中间结果的重要工具。关键是要知道什么时候用、怎么用不会踩坑。5.1 真正适合临时表的场景如果你的处理流程是下面这种临时表仍然是一个合理的选择中间结果集需要被后续多个步骤多次引用比如先算出符合条件的一批客户然后分别去关联订单、关联退款、关联售后数据需要分阶段验证比如刚才能跑出一个中间结果你想看看这个结果对不对再继续下一步基表数据量巨大且过滤条件复杂直接一条大查询会导致优化器生成极其复杂的执行计划拆成临时表反而能降低优化难度。在这些场景里只要记得给临时表补上必要的索引或者确认数据量小到不需要担心统计信息问题临时表是可以放心用的。5.2 我自己的几个判断标准踩过这次坑之后我给自己定了几条很朴素的判断标准写代码时遇到临时表就会过一遍临时表预计行数超过一万且后续要和别的表 JOIN那必须给它建索引不能让它裸奔。临时表里插入数据后如果大概率还要做 UPDATE、DELETE 等改动要在改动完成后考虑是否需要更新统计信息。存储过程入口参数的数据范围波动很大比如 1 天到 30 天都可能出现那我既不会完全依赖隐式统计信息也不会靠RECOMPILE硬扛而是优先考虑 SQL 重写让优化器直接基于有统计信息的基表做判断。一个查询如果只是把临时的中间结果用一次那就优先考虑 CTE 或者派生表临时表不是必须的。5.3 后续的长期治理思路单次问题修复不代表整个系统不会再出现同类问题。这次之后我在监控里多加了两个检查项一是对关键存储过程定期抽查执行计划重点对比Estimated Number of Rows和Actual Number of Rows之间的偏差二是把sys.dm_exec_query_stats里逻辑读异常偏大的语句单独拉一个告警项。这样即使未来又有临时表的统计信息问题也能在业务方感知之前暴露出来。另外如果你手里有一批旧存储过程不知道里面哪些用了临时表、到底有没有隐患可以写一个脚本去sys.sql_modules里搜索#开头的临时表引用先把清单列出来。然后针对性地执行几次不同参数规模下的测试重点观察执行计划是否需要回退。这个过程虽然有点费时间但确实能提前发现一批潜在的雷。最后再分享一个我个人的小习惯在存储过程里用临时表时我会在代码注释里标明这个临时表预期数据量大概是多少、后续怎么使用。等哪天数据量长了、性能出了问题后接手的人能快速判断当时的意图不用再把整个逻辑重新读一遍。这种细节看起来不起眼但在排障的深夜里一份靠谱的注释比什么都值钱。