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

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

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

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

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

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

    - 显式调用 sp_recompile。

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

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

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

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

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

Temporary table

If n 6, RT = 6. If 6 = n = 500, RT = 500. 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) ）.脚本如下所示：

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id=object_id(N[dbo].[TEST]) AND OBJECTPROPERTY(id, NIsTable)=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等。如下所示

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

EXEC dbo.Usp_Recompile_TEST 1;

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

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

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

Temporary table If n 6, RT = 6. If 6 = n = 500, RT = 500. 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跟踪、你会看到下面结果，如果还不太明白，结合截图好好理 解一下：

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

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

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

SQL SERVER 临时表导致存储过程重编译(recompile)的一些探讨 原文:SQL SERVER 临时表导致存储过程重编译(recompile)的一些探讨     SQLSERVER为了确保返回正确的值，或者处于性能上的顾虑，有意不重用缓存在内存里的执行计划，而重新编译执行计划的这种行为，被称为重编译（recompile）。
SQL SERVER 临时表的排序问题 原文:SQL SERVER 临时表的排序问题   在SQL SERVER 2005/2008中，如果将有序的记录插入临时表，则从临时表查询出来的记录是有序的（不依赖ORDER BY也是有序状态），但是从SQL SERVER 2012开始，即使插入的记录集有序，查询出来的结果变成无序了。
SQL Server 临时表的删除 原文:SQL Server 临时表的删除 临时表与一般的表不同，它是保存到tempDb表中。临时表的表名与你所建的表名也不一样，因为他要为不同人的相同操作创建不同的临时表。 1、错误的删除操作： --错误的临时表删除操作，因为所在数据库不同IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N [dbo].
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载