SQL SERVER 临时表导致存储过程重编译(recompile)的一些探讨

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

   SQLSERVER为了确保返回正确的值,或者处于性能上的顾虑,有意不重用缓存在内存里的执行计划,而重新编译执行计划的这种行为,被称为重编译 (recompile)。那么引发存储过程重编译的条件有哪一些呢?下面罗列了一些导致重编译(recompile)的条件:

    - 对查询所引用的表或视图进行更改(ALTER TABLE 和 ALTER VIEW)。

    - 对执行计划所使用的任何索引进行更改。

    - 对执行计划所使用的统计信息进行更新,这些更新可能是从语句(如 UPDATE STATISTICS)中显式生成,也可能是自动生成的。

    - 删除执行计划所使用的索引。

    - 显式调用 sp_recompile。

    - 对键的大量更改(其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改)。

    - 对于带触发器的表,插入的或删除的表内的行数显著增长。

    - 使用 WITH RECOMPILE 选项执行存储过程。

    - 有些DBCC FREEPROCCACHE;分离、附加数据库、数据升级也会清除内存里缓存的执行计划

好 了,切入到今天我们要关注的问题:临时表的数据变化导致存储过程重编译问题,其实临时表的数据变化导致存储过程重编译实质上是因为临时表的数据变化,导致 了临时表统计信息的自动更新,从而引起的重编译。那么触发临时表的统计信息的更新的条件或阀值是什么呢?说来也简单,就是下面一个这个公式(n表示变更前临时表的数据记录数,确切的说是上一次采集统计信息时临时表的记录数

Temporary table

  1. If n < 6, RT = 6.
  2. If 6 <= n <= 500, RT = 500.
  3. If n > 500, RT = 500 + 0.20 * n.

有个网友说存储过程中的临时表数据变更的阀值有问题:他的原话如下

If n < 6, Recompilation threshold = 6.
If 6 <= n <= 500, Recompilation threshold = 500.

上面这两个区间没有问题。但是大于500的之后,根本就不是变化大于20%之后再重编译。看了他提出的问题,其实我也不是特肯定,毕竟没有实际验证过。实践才是检验整理的唯一标准,那么我们就开始做实验吧,首先准备一下测试环境(Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) ).脚本如下所示:

USE MyDBA;
GO
 
IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id=object_id(N'[dbo].[TEST]') AND OBJECTPROPERTY(id, N'IsTable')=1 )
BEGIN
 DROP TABLE dbo.TEST;
 
    CREATE TABLE TEST
    (
          ID INT IDENTITY(1, 1) ,
          NAME VARCHAR(40)     ,
          CONSTRAINT PK_TEST PRIMARY KEY(ID)
    )
END
GO
 
INSERT INTO TEST VALUES(NEWID())
GO 10000
 
CREATE PROCEDURE Usp_Recompile_TEST(@Index INT)
AS
BEGIN
 CREATE TABLE #T(ID   INT , NAME VARCHAR(40));
 
 INSERT INTO #T SELECT ID, NAME FROM TEST WHERE ID <=@Index;
 
 SELECT  m.* FROM #T m INNER JOIN TEST n ON m.ID = n.ID
END
GO

准备好测试环境后,那么此时我们打开SQL Server工具SQL Server Profiler,选择“SP:Recompile”和“SP:Complete”事件,然后取消一些选择列,仅仅选择一些需要的列,例如 EventClass、TextData等。如下所示

clipboard

开启Profile跟踪后,我们打开一个会话窗口,勾选“包括实际的执行计划”,然后再窗口执行下面SQL语句

EXEC dbo.Usp_Recompile_TEST 1;

如下所示,实际的执行计划中,我们看到“估计行数”和“实际行数”是一致的。

clipboard[1]

EXEC dbo.Usp_Recompile_TEST 2;
 
EXEC dbo.Usp_Recompile_TEST 6;

执行上面两个语句,我们会发现“估计行数”与“实际行数”开始出现偏差,因为数据库对临时表#T没有最新的统计信息,还是上一次收集的统计信息时的数据(1行数据)

clipboard[2]

EXEC dbo.Usp_Recompile_TEST 7;  此时已经触发了对临时表统计信息的采集更新(请见后面阐述)。

clipboard[3]

EXEC dbo.Usp_Recompile_TEST 130;
 
EXEC dbo.Usp_Recompile_TEST 500;
 
EXEC dbo.Usp_Recompile_TEST 506;
 
EXEC dbo.Usp_Recompile_TEST 507;

那么执行上面SQL语句,130我们确信不会导致临时表#T去更新统计信息,501会触发#T表的统计信息更新吗? 如果不会触发,那么确切的值是多少呢?答案是507,如下截图所示:

clipboard[4]

想必有些人会说,我实验的结果不一样哦(啪啦啪啦说一大堆),那么你是否真正的理解了下面公式呢? n表示临时表变跟前的记录数(确切的说是统计信息采集时的记录数),后面的RT表示变跟的记录数。

Temporary table

  1. If n < 6, RT = 6.
  2. If 6 <= n <= 500, RT = 500.
  3. If n > 500, RT = 500 + 0.20 * n.

由于我第一次执行的是EXEC dbo.Usp_Recompile_TEST 1,那么数据库的记录数为1,那么1+ 6 =7; 也就是上图EXEC dbo.Usp_Recompile_TEST 7时才触发临时表#T的统计信息更新,而为什么是507(7+500=507)呢,因为最后一次统计信息的采集,临时表#T的记录数为7 ,所以7+500=507,是否有点不解,那么你按我这个SQL执行一遍,然后用Profile跟踪、你会看到下面结果,如果还不太明白,结合截图好好理 解一下:

DBCC FREEPROCCACHE;
 
EXEC dbo.Usp_Recompile_TEST 2;
 
EXEC dbo.Usp_Recompile_TEST 6;
 
EXEC dbo.Usp_Recompile_TEST 7;
 
EXEC dbo.Usp_Recompile_TEST 8;

clipboard[5]

如果还没有理解的话,我的表达能力已到极限了,自己再好好琢磨一下吧! 那么接下来才是我们重点想要验证、测试的。

DBCC FREEPROCCACHE; 
 
EXEC dbo.Usp_Recompile_TEST 501; 

此时临时表#T的记录数为501,那么当临时表#T里面的记录数变更了多少时, 才会触发统计信息的更新呢? 由于是插入,那么根据公式应该是501 + (500 + 0.2*501) = 1101.2 ,那么应该是1101,即使是1100也不会变化。下面SQL Server Profile可以验证我们的推测

EXEC dbo.Usp_Recompile_TEST 1100; 
 
EXEC dbo.Usp_Recompile_TEST 1101; 

如果我们继续使用该存储过程,那么当参数为什么值时才会触发统计信息更新呢? 1101 +(500+0.2*1101)=1821.2,也就是说必须是1821才会触发统计信息更新,下面SQL Server Profile的截图也验证了我们的推测。

EXEC dbo.Usp_Recompile_TEST 1300; 
 
EXEC dbo.Usp_Recompile_TEST 1320; 
 
EXEC dbo.Usp_Recompile_TEST 1321; 
 
EXEC dbo.Usp_Recompile_TEST 1820; 
 
EXEC dbo.Usp_Recompile_TEST 1821;

clipboard[6]

所以综上述实验验证,SQL SERVER 临时表导致存储过程重编译(recompile)的那些阀值确实是正确的,也是没有问题的。当然如有疏漏或不对的地方,敬请指出。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
8天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
51 10
|
30天前
|
存储 SQL Go
sqlserver存储过程
sqlserver存储过程
19 0
|
30天前
|
存储 SQL 数据库
sqlserver中常用的几个存储过程
sqlserver中常用的几个存储过程
48 0
|
1月前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
|
18天前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
16 0
|
28天前
|
存储 SQL 数据库
sql serve存储过程
sql serve存储过程
14 0
|
8天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
68 6
|
4天前
|
SQL 安全 网络安全
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
8 0
|
9天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
12天前
|
SQL 数据安全/隐私保护
SQL Server 2016安装教程
SQL Server 2016安装教程
17 1