快捷搜索:
来自 网络数据库 2019-11-01 10:41 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

Server数据库的存款和储蓄进程中定义的不常表,

 

在一些先决条件下,SQL Server可以缓存临时表(cache Temp Tables)。缓存临时表意味着当你创建反复创建同个临时表时,SQL Server就可以重用它们。这会从整体上大幅度提高你的工作量(性能),因为SQL Server不需要访问内存里的特定页(PFS,GAM,SGAM),经常访问这些页在工作量大的情况下会引起加锁竞争(Latch Contention)。Paul White有一篇很棒的文章详细描述这个情况,可以点此围观下。

本文出处: 

临时表缓存的条件之一是不能在存储过程里混合使用DML(Data Manipulation Language 数据操作语言)和DDL(Data Definition Language 数据定义语言)语句。我们来看下面的代码:

 

 1 -- Create a new stored procedure
 2 CREATE PROCEDURE PopulateTempTable
 3 AS
 4 BEGIN
 5     -- Create a new temp table
 6     CREATE TABLE #TempTable
 7     (
 8         Col1 INT IDENTITY(1, 1),
 9         Col2 CHAR(4000),
10         Col3 CHAR(4000)
11     )
12 
13     -- Create a unique clustered index on the previous created temp table
14     CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)
15 
16     -- Insert 10 dummy records
17     DECLARE @i INT = 0
18     WHILE (@i < 10)
19     BEGIN
20         INSERT INTO #TempTable VALUES ('Woody', 'Tu')
21         SET @i  = 1
22     END
23 END
24 GO

 

这里你通过DDL语句(CREATE UNIQUE CLUSTERED INDEX )创建了索引,这就是说你混合使用了DDL和DML语句。因此SQL Server不能缓存你的临时表。你可以从下面例子里的DMV sys.dm_os_performance_counters ,通过跟踪性能计数器Temp Tables Creation Rate 来验证:

问题背景

 1 DECLARE @table_counter_before_test BIGINT;
 2 SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters
 3 WHERE counter_name = 'Temp Tables Creation Rate'
 4 
 5 DECLARE @i INT = 0
 6 WHILE (@i < 1000)
 7 BEGIN
 8     EXEC PopulateTempTable
 9     SET @i  = 1
10 END
11 
12 DECLARE @table_counter_after_test BIGINT;
13 SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters
14 WHERE counter_name = 'Temp Tables Creation Rate'
15 
16 PRINT 'Temp tables created during the test: '   CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test)
17 GO

在写SQL Server存储过程中,如果存储过程中定义了临时表,
有些人习惯在存储过程结束的时候一个一个显式地删除过程中定义的临时表(drop table #tName),有些人又没有这个习惯,
对于不明真相的群众或者喜欢思考的人会问,存储过程中定义的临时表,最后要不要主动删除,为什么?
或者说是不是存储过程结束的时候删除临时表更加规范?
不止一个人问过这个问题了,说实在话,本人之前确实不清楚,只是认为,显式删掉或者不删都行,临时表在当前Session断开之后会自动释放
那么存储过程中定义的临时表,在使用完之后,到底删还是不删?显式删除与不做删除有无区别?
本文将对此问题进行一个粗浅的分析,如有不对的地方,还望指出,谢谢。

当你运行这个代码时,SQL Server需要创建1000个单独的临时表,这个从SSMS的输出窗口就可以看到。

 

图片 1 

存储过程中临时表的表结构也有缓并且会被重用

通过PRIMARY KEY约束来强制UNIQUE CLUSTERED INDEX就很容易克服这个问题。在这个方式下,你没有混合使用DDL和DML语句,SQL Server最后也能缓存你的临时表。

那么到底需不需要显式删除,显式删除或者是不删除有什么区别?
这中间涉及到一个临时表缓存的知识点,首先看什么是临时表的缓存。
缓存临时表是SQL SERVER 2005以来的一个新特性,
临时表的创建时需要往临时库系统表中写入数据(元数据,临时表的表结构信息),跟普通的增删改操作一样,这个过程需要一定的资源消耗
在满足一定条件的情况下(后面说需要满足的条件是什么),
每当用户请求完成之后(当然这个用户请求的SQL中包含了临时表),临时表的元数据将会保存在临时库(tempdb)的系统表中
虽然在用户看来,当前Session创建的临时表,对其他Session事不可见的,在Session断开或者临时表被删除(drop)之后,将不可访问。
但是当新的Session调用同样的包含了创建临时表的代码,SQL Server内部会重用之前Session执行时创建过的临时表,而无需再次定义临时表。
这样的话可以节约一些创建表的步骤所消耗的资源。

 1 ALTER PROCEDURE PopulateTempTable
 2 AS
 3 BEGIN
 4     -- Create a new temp table
 5     CREATE TABLE #TempTable
 6     (
 7         Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
 8         Col2 CHAR(4000),
 9         Col3 CHAR(4000)
10     )
11 
12     -- Insert 10 dummy records
13     DECLARE @i INT = 0
14     WHILE (@i < 10)
15     BEGIN
16         INSERT INTO #TempTable VALUES ('Woody', 'Tu')
17         SET @i  = 1
18     END
19 END
20 GO

 

当你重新执行刚才用来跟踪相关计数器的代码,可以看到SQL Server值创建了一次临时表并重用它了:

上面是理论,下面来做个小实验演示上面的理论,首先来看不同Session之间临时表“重用”的现象。
首先这里要借助系统视图sys.dm_os_performance_counters 来判断临时表的创建次数,该系统表中计数器的名称为:Temp Tables Creation Rate。

图片 2

创建如下存储过程,存储过程中定义了一个临时表,

 

create procedure Proc_TestTempTable
as
begin

    create table #t20170413
    (
        col_1 varchar(100) ,
        col_2 varchar(100)
    )

    insert into #t20170413 values ('aaa','bbb');

    select * from #t20170413
    --select * from tempdb.sys.tables where name like '#t20170413%'
end

这个结论也意味着,当你创建额外的非聚集索引(Non-Clustered Indexes)时,SQL Server也不能缓存临时表,因为在你的存储过程里,你又一次混合使用DDL和DML语句。

在存储过程创建之后,第一次执行的时候,来观察一个现象,如下截图

但在SQL Server 2014里,你就可以克服这个限制,因为现在你可以在CREATE TABLE语句行里创建索引。来看下面的代码:

图片 3

 1 ALTER PROCEDURE PopulateTempTable
 2 AS
 3 BEGIN
 4     -- Create a new temp table
 5     CREATE TABLE #TempTable
 6     (
 7         Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
 8         Col2 CHAR(100) INDEX idx_Col2,
 9         Col3 CHAR(100) INDEX idx_Col3
10     )
11 
12     -- Insert 10 dummy records
13     DECLARE @i INT = 0
14     WHILE (@i < 10)
15     BEGIN
16         INSERT INTO #TempTable VALUES ('Woody', 'Tu')
17         SET @i  = 1
18     END
19 END
20 GO

很明显,sys.dm_os_performance_counters系统表中的Temp Tables Creation Rate计数器加了1,也就是说在执行存储过程中过程中发生了一次临时表的创建动作
然后继续再次执行上面的代码

如你所见,我在创建临时表本身的时候,就在临时表上直接创建2个额外的非聚集索引。又一次我们没有混合使用DDL和DML语句,SQL Server又一次可以缓存并重用你的临时表。

图片 4

图片 5 

同样的代码,这一次sys.dm_os_performance_counters系统表中的Temp Tables Creation Rate计数器没有加1,
为什么明明是存储过程中定义了临时表,上面执行一次,Temp Tables Creation Rate加1,然后再次执行就不加1了?
这个就是临时表重用的现象(严格说是临时表的表结构或者表定义,而不包含数据),
因为第一次执行存储过程的时候创建了临时表,然后再次执行存储过程的时候就重用了第一次的临时表。  

在SQL Server 2014里,在临时表上定义行内定义索引,避开混合使用DML和DDL语句,让临时表只创建一次并重用,是一个很棒的功能!

  那怎么证明该存储过程第二次执行的时候重用了第一次创建的临时表?
  对存储过程稍作修改,存储过程中加一句代码,查询临时库中该临时表信息

这个新功能怎样?欢迎在下面评论里告诉我。 

图片 6

参考文章:

https://www.sqlpassion.at/archive/2013/06/27/improved-temp-table-caching-in-sql-server-2014/

  然后执行两次如下代码,下面截图是第二次执行的结果(下面会做解释为什么是第二次的执行的结果),
  在临时表被重用的时候查询出来当前临时表的信息,发现临时表创建次数并没有增加,也就是说临时表被重用了

图片 7

  既然说临时表重用了,那么临时表一定存在于临时库的系统表中,那么如何证明这个存储过程的临时表在临时库中呢?
  上面显示的临时表的Id是-1297292959,那么这里就临时库中查询Id = -1297292959的表信息,发现果然存在这个一张表。
  临时库中的这个表信息除了名字和modify_date不一样,modify_date据观察是临时表被重用的时间,也就是临时表被重用一次就修改一次modify_date
  其他信息完全一致,这就是说明,存储过程第一次执行完成之后,它所创建的临时表被缓存了起来(至于名字不同,后面再解释),
  当再次执行该存储过程的时候可以重用第一次执行存储过程时候创建的临时表的表结构。

图片 8

 

存储过程中显式删除临时表,到底有没有用处?

对上面的存储过程做如下修改,在存储过程结束之前显式删除定义的临时表

图片 9

  然后再次执行如下的测试代码,注意截图是第二次执行的结果(下面会做解释为什么是第二次的执行的结果)

图片 10

  然后继续在临时库的系统表中查询上述Id的系统,发现临时表依旧存在于系统表中,即便是存储过程中显式删除(drop table #t20170413)

图片 11

  这里说明,即便在存储过程中显式调用了删除临时表的操作,临时表依旧会存在得临时库的系统表中,也就是说临时表依旧会被缓存。
  并不会因为在存储过程中显式删除而真正的删除,临时表对象会缓存在临时库的系统表中。
  之所以Session中查询到的临时表的名字与系统表中查询到的临时表的名字不同,
  原因是临时表从创建到缓存(当前Session断开之后),在内部只是发生了一个对当前Session临时表重命名的过程。
  被缓存的临时表的重用的过程与上面的类似,也是将缓存的换反向重命名。

事实证明:
对于存储过程的临时表,在满足可缓存的前提下(只是表结构,当然不包括临时表的数据),
你删,或者不删,他都会缓存在临时库中,并不因为显式Drop临时表,临时表就会被真正的删除,这是SQL Server专门为此做的优化,你真的不用为删除临时表而操心或者纠结
这里回到一开始的问题,存储过程中有没有必要显式删除临时表就有答案了:对于存储过程的创建的临时表,没必要删除,对于满足可缓存的临时表对象,想删也删不掉!

 

存储过程中定义的临时表,只有满足一定的条件,才会被缓存重用

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:Server数据库的存款和储蓄进程中定义的不常表,

关键词: