SQL Server 2014，改善的临时表缓存-阿里云开发者社区

SQL Server 2014，改善的临时表缓存

2017-11-02 1190

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介：

在一些先决条件下，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不能缓存你的临时表。你可以从下面例子里的DMVsys.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需要创建1000个单独的临时表，这个从SSMS的输出窗口就可以看到。

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

 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值创建了一次临时表并重用它了：

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

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

 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