【翻译】临时对象的缓存

原文地址：http://www.sqlmag.com/Article/ArticleID/101993/sql_server_101993.html
原作者：Itzik Ben-Gan
翻  译：周强

     SQL SERVER 2005 在缓存临时对象方面作了很大的增强。你能在Working with tempdb in SQL Server 2005这篇文章中找到关于这些增强的详细信息及其它一些关于TEMPDB方面的增强，我强烈推荐你阅读这篇文章。

    在这些增强中，我最感兴趣是的SQL SERVER 在一些常见程序调用的时候（比如：存储过程、触发器、和UDF）,可以缓存由此产生的临时对象（比如：临时表、表变量）。它能在缓存中保持表现临时对象的元数据和一些页面（数据页和IAM页），这样就能够防止当你每一次运行程序的时候都需要对其进行相关资源的分配和释放。对于这些调用非常频繁的程序来说，它可以节省时间并且能够减少对系统目录表的竞争和分配页。

    你需要意识到，在某些情况下SQL SERVER不会缓存临时对象。其情形之一是，当你对某个已经创建的临时对象应用的DDL语句以后，该临时对象将不会被缓存。这跟导致重新编译的原因十分相似，并且似乎很合理。另一种情形是当你使用了一个被命名的约束作为定义临时对象的一部分的时候，临时对象将不会被缓存。我觉得这非常匪夷所思，但实际的测试确实证明了是这样。

   接下来，我将用实例演示临时对象的缓存行为及一些不能被缓存的情况。

   首先，运行下面的代码以创建一个存储过程，该过程被命令为TestCaching，这个过程创建了一个临时表，并且往这个表中填充了一些数据。
在运行这个存储过程以前，运行下面的代码以查看在TEMPDB数据库中当前是否有任何临时对象。
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
你不会得到任何输出，否则，输出的临时对象一定与我们上面的过程无关。

SET  NOCOUNT  ON ; 
USE  tempdb; 
IF   OBJECT_ID ( ' dbo.TestCaching ' ,  ' P ' )  IS   NOT   NULL
DROP   PROC  dbo.TestCaching; 
GO
CREATE   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT , filler  CHAR ( 2000 )); 
INSERT   INTO  #T1  VALUES
         ( 1 ,  ' a ' ), 
         ( 2 ,  ' a ' ), 
         ( 3 ,  ' a ' );  
SELECT  n, filler  FROM  #T1; 
GO

 译者注：该过程适用于SQL SERVER 2008 ,若测试环境为SQL SERVER 2005 则代码将修改为：

SET  NOCOUNT  ON
USE  tempdb 
IF   OBJECT_ID ( ' dbo.TestCaching ' ,  ' P ' )  IS   NOT   NULL
DROP   PROC  dbo.TestCaching 
GO
CREATE   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT , filler  CHAR ( 2000 ));  
INSERT   INTO  #T1  VALUES  ( 1 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES  ( 2 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES  ( 3 ,  ' a ' ) 
SELECT  n, filler  FROM  #T1; 
GO

    运行这个存储过程： 
   EXEC dbo.TestCaching 
   查看TEMPDB数据库中的临时对象 
   SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

   可以看到，即使这个过程已经被运行完毕，前面被提到的关于临时对象的相关信息仍然被保留，而这个过程随后的调用将能重新使用这些被保留下来的信息。你应该能够得到一个由这个存储过程所创建的临时表条目的输出。我得到的这个表叫#117F9D94。

   在进行下一轮测试之前，我们先运行下面的代码将这个存储过程标记为重新编译。
   EXEC sp_recompile 'dbo.TestCaching';
   再查看一下TEMPDB数据库中的临时对象：
   SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

   你将得到一个空集。

   下面一个例子将演示对一个临时对象应用了DDL语句以后，临时对象将不能被缓存。运行以下代码修改这个存储过程，在这个创建的表上创建一个索引。

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT , filler  CHAR ( 2000 )); 
CREATE   UNIQUE   INDEX  idx1  ON  #T1(n); 
INSERT   INTO  #T1  VALUES
     ( 1 ,  ' a ' ),  
     ( 2 ,  ' a ' ),  
     ( 3 ,  ' a ' ); 
SELECT  n, filler  FROM  #T1; 
GO

 译者注：上面的代码适合于SQL SERVER 2008 ,如果为SQL SERVER 2005 则需要修改代码如下：

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT , filler  CHAR ( 2000 )); 
CREATE   UNIQUE   INDEX  idx1  ON  #T1(n); 
INSERT   INTO  #T1  VALUES ( 1 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES ( 2 ,  ' a ' )  
INSERT   INTO  #T1  VALUES ( 3 ,  ' a ' ) 
SELECT  n, filler  FROM  #T1; 
GO

   运行存储过程:
   EXEC dbo.TestCaching;
   查询临时对象:
   SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

   你应该会得到一个空集。为了避免这个问题，使用关键词UNIQUE作为创建表的一部分，而不要显式的创建索引。就像如下代码所示：

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT   UNIQUE , filler  CHAR ( 2000 )); 
INSERT   INTO  #T1  VALUES
         ( 1 ,  ' a ' ), 
         ( 2 ,  ' a ' ), 
         ( 3 ,  ' a ' ); 
SELECT  n, filler  FROM  #T1; 
GO

译者注：以上代码适合于SQL SERVER 2008,如果为SQL SERVER 2005 则需要修改代码如下: 

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT   UNIQUE , filler  CHAR ( 2000 )); 
INSERT   INTO  #T1  VALUES ( 1 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES ( 2 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES ( 3 ,  ' a ' ) 
SELECT  n, filler  FROM  #T1 
GO

 
   再次运行这个存储过称:
    EXEC dbo.TestCaching;
   查询临时对象：
   SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%'
  
    这次的输出表明了临时对象被保持，我得到了一个表名为#1367E606的输出。

    再来看一下命名约束将会阻止临时对象缓存的例子，运行下面的代码修改先前的存储过程，过程中将会对约束进行命名。

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT   CONSTRAINT  UNQ_#T1_n  UNIQUE , filler  CHAR ( 2000 )); 
INSERT   INTO  #T1  VALUES
( 1 ,  ' a ' ), 
( 2 ,  ' a ' ), 
( 3 ,  ' a ' );
SELECT  n, filler  FROM  #T1;
GO

 译者注：以上代码适合于SQL SERVER 2008,如果为SQL SERVER 2005 则需要修改代码如下:

ALTER   PROC  dbo.TestCaching 
AS
CREATE   TABLE  #T1(n  INT   CONSTRAINT  UNQ_#T1_n  UNIQUE , filler  CHAR ( 2000 )); 
INSERT   INTO  #T1  VALUES ( 1 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES ( 2 ,  ' a ' ) 
INSERT   INTO  #T1  VALUES ( 3 ,  ' a ' ) 
SELECT  n, filler  FROM  #T1
GO

 

运行这个存储过程:
EXEC dbo.TestCaching;
查询临时对象:
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
这次系统将返回一个空集（在条目被删除之前可能需要短短几秒钟）。

因此，如果你想要缓存临时对象，你需要避免在临时对象被创建以后再对其应用DDL语句。并且，如果你要创建约束，请不要对约束指定名称。

Cheers,

BG

该文完。

转载于:https://www.cnblogs.com/zhouqiang52154/archive/2009/06/02/1493301.html