前两天,我司 Flink 调用一个 SQL 存储过程,2 分钟没跑出结果。这个存储过程,拉的是每小时的增量数据。
这个 Job 在测试时,一会儿流畅,一会儿就奇慢无比。任何一段超过 2 分钟的增量,那就是故障。于是,开发们找到我
看到他们一快一慢的测试 SQL,我就加了一个 hint,立马秒出,于是,我嘚瑟在朋友圈秀了一番。
看到微信群的朋友们,那么好学,于是我打算好好讲讲这个 SQL Parameter Sniffing 的内幕。涉及到投行的保密协议,我这里暂时用这个 sp 替换下:
CREATE PROCEDURE [dbo].[ProductTransactionHistoryByReference] @ReferenceOrderID INT
AS
BEGIN
SELECT p.Name,
p.ProductNumber,
th.ReferenceOrderID
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th
ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @ReferenceOrderID;
END
代码来自微软的 AdventureWorks2019 示例数据库
微软 Microsoft
你看,很简单的 SQL 存储过程,不同的参数值,性能就很不稳。
我先不说 parameter sniffing 是什么,怎么解决。就执行下面两条 SQL,大家认为执行计划会不一样吗 ?
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 816
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 53465
在没有做任何优化的情况下,这两段 SQL 的执行计划,是一样的
SQL 的查询引擎,在背后做了很多自动优化,并将优化过的执行计划,都缓存了起来,等到下次执行,就可以充分利用这些缓存中的执行计划,以避免过量,重复地编译同一个存储过程,或 SQL 片段。
因此,上面的存储过程,在第一次执行时,就被编译好,存储到了缓存中。但有一点特别关键,编译时,优化器自动把第一次执行的参数值,也给编译起来了。这就是 Parameter Sniffing.
从执行计划的 SELECT 操作符中,可以看到 compiled value 属性,包含了第一次执行时,参数的值
正由于这个原因,有些奇异值,它的最佳执行计划就不能被充分开发,就会出现文章开头所说的,跑了 2 分钟,存储过程还没结果
下面就给大家演示下,两段 SQL,在 parameter sniffing 干扰下,他们的神奇变化
第一步,依次执行:
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 816
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 53465
对比下两者的 存储过程:
从两个存储过程可以看到,@ReferenceOrderID = 816 的时候,数据只有一行;而当 @ReferenceOrderID = 53465 时,却有 200 多万行。
很明显,index seek + key lookup 的方式,在访问 TransactionHistory 时, 产生大量的回表随机读,而更有效率的 Index Scan 却没有生成
第二步,清下缓存
dbcc freeproccache
这一步非常重要。
正常的生产环境中,都会有很多并发的 session 在执行任务,每个 session 又都可能运行着数百条 SQL 语句,如果每条语句,都要占用 CPU 一段编译时间,整个系统就会僵住,延迟会大量出现。
数据库一般会帮我们做好优化,有效缓存这些 SQL,等待后续的调用。如果一段时间内,缓存的执行计划没有再次运行,数据库会启动后台进程,将这些缓存计划清除,释放内存。
但,也要注意,平时在生产环境的数据库上,不要执行这条语句,以免系统 CPU 瞬时产生大量编译,带来奔溃
第三步,颠倒下次序,再执行这两段 SQL
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 53465
exec dbo.[ProductTransactionHistoryByReference] @ReferenceOrderID = 816
此时再看,两段 SQL 的执行计划,又一模一样了。
但唯一的不同, @ReferenceOrderID = 53465, 耗时从 22s 降到 14s, 节省 1/3 的时间,执行计划从回表变成 index scan.
这一切产生的本质,来自于数据分布的不平衡:
select TOP 10 ReferenceOrderID,count(*)*1.00 / count(*) over() cnt
from Production.TransactionHistory
GROUP BY ReferenceOrderID
order by cnt desc
我故意多造了 53465 这个键下的数据,使他的记录条数,占总表体量的 63%, 因此走 index scan 比 index seek 有效
现象已经提出来了,原理也说了,接下来说说解决方案
加语句级别的 recompile
recompile 既可以加在存储过程级别,也可以加在单段 SQL 后面,比如这样:
CREATE PROCEDURE [dbo].[ProductTransactionHistoryByReference_PT] @ReferenceOrderID INT
AS
BEGIN
SELECT p.Name,
p.ProductNumber,
th.ReferenceOrderID
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th
ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @ReferenceOrderID
OPTION(RECOMPILE)
END
GO
注意这个语句级别的 hint, 对于改写执行计划,非常重要
OPTION(RECOMPILE)
recompile 起到重新完整生成执行计划的作用。而且不会存于缓存中
2. 使用本地(local)变量
本地(local)的意思,看上去会有点变扭,不好理解。但是,一看代码就好懂了
ALTER PROCEDURE [dbo].[ProductTransactionHistoryByReference_PT] @ReferenceOrderID INT
AS
BEGIN
DECLARE @LocalReferenctOrderID INT
SET @LocalReferenctOrderID = @ReferenceOrderID
SELECT p.Name,
p.ProductNumber,
th.ReferenceOrderID
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th
ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @LocalReferenctOrderID
END
GO
上面的 @LocalReferenceOrderID,就是本地变量。本质上,还是变量的作用域。
参数 @ReferenceOrderID 值是存储过程编译的一部分,而变量 @LocalReferenceOrderID 只是存储过程定义的组成部分
再看参数编译列表:
与之前不加本地变量的执行计划相比:
缺少了“参数编译值”。
但,“参数编译值”虽然不随计划一起缓存了,那它的计划,是不是就一定最优呢?并不是
再看看这一步 index seek, 为什么走错了,本该走的是 index scan
在 index seek 这一步,仔细看这个公式:
2359296 / 168 (1404342%)
实际读取的行数,2359296,而估计的行数,168(167.879四舍五入),相差 14043 多倍。这导致生成的执行计划,性能谬以千里
那么更新统计信息(statistics)就好,可以使用 SSMS UI 操作
也可以使用 SQL 语句:
update statistics Production.TransactionHistory with fullscan
使用本地变量的方法,会让程序有一种多此一举的感觉,而且性能效果不一定达到,因此不是最优解
3. OPTIMIZE FOR
这种方法就比较灵动。
经过一段时间的数据沉淀,数据倾斜度已经形成,小数据量的 SQL 查询性能一直都很稳定,大数据量的 SQL 开始遭遇到 parameter sniffing 副作用。
正如上面讲述的两个 SQL 中,类似 @ReferenceOrderID = 53465 这样的查询,越来越会有性能的抖动。针对这类 SQL,指定一个样例执行计划的模板,就是个低成本的解决方案
ALTER PROCEDURE [dbo].[ProductTransactionHistoryByReference_PT] @ReferenceOrderID INT
AS
BEGIN
SELECT p.Name,
p.ProductNumber,
th.ReferenceOrderID
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th
ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @ReferenceOrderID
OPTION( OPTIMIZE FOR (@ReferenceOrderID = 53465))
END
GO
特别关注下这份 hint:
OPTION( OPTIMIZE FOR (@ReferenceOrderID = 53465))
此时,执行计划的编译值又存进缓存了,不过是指定的值,而非第一次执行的值。由此缓存起来的执行计划,对大数据量的查询,非常有用,能解决数据倾斜带来的查询高延迟。
强调下原理,大数据量的回表,会降低性能,应让其走 Index Scan 而非 Index Seek + Key Lookup
再执行 @ReferenceOrderID = 816
即使参数编译值是 53465,它依旧很稳。
由此可见,此时采用的调优措施,既解决了大数据量查询缓慢的窘境,也没影响原有小数据量的查询,是最优解。当然你碰到的情况,会有所不同,具体场景,具体分析
下面推荐这本跟我很多年的书,第九章讲解的缓存结构,其中就提到 parameter sniffing 破解之法
还有以下这本,最新的 SQL Server 2022 版性能调优,第十三章,将 parameter sniffing 单拎出来详解,非常过瘾。本书还没引进国内,我也只拿到了这一章
--完--
往期精彩: