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

在一些先决条件下，SQL Server可以缓存临时表（cache Temp Tables）。缓存临时表意味着当你创建反复创建同个临时表时，SQL Server可以重用它们。这会从整体上大幅度提高你的工作量（性能），因为SQL Server不需要访问内存里的特定页（PFS，GAM，SGAM），经常访问这些页在工作量大的情况下会引起加锁竞争（Latch Contention）。Paul White有一篇很棒的文章详细描述这个情况，可以点此围观下。 　　临时表缓存的条件之一是不能在存储过程里混合使用DML（Data Manipulation Language 数据操作语言）和DDL（Data De 在SQL Server 2014中，为了提升性能，数据库引擎引入了对临时表的缓存功能。这一改进显著减少了数据库在处理大量数据时的内存访问和锁竞争，从而优化了整体的工作负载。临时表缓存允许SQL Server重用已创建的临时表，避免重复的创建过程，这对于频繁使用相同临时表的场景尤其有利。 临时表缓存的一个关键前提是避免在存储过程中混合使用DML（Data Manipulation Language）和DDL（Data Definition Language）语句。DML语句包括INSERT、UPDATE、DELETE等用于操作数据的命令，而DDL语句如CREATE TABLE、ALTER TABLE等则用于定义或修改数据结构。当这两种类型的语句混用时，SQL Server无法缓存临时表，因为DDL操作改变了表的结构，这可能导致内存中的缓存失效。 以下是一个示例，展示了如何不正确地混合使用DML和DDL语句： ```sql CREATE PROCEDURE PopulateTempTable AS BEGIN CREATE TABLE #TempTable (Col1 INT IDENTITY, Col2 CHAR(4000), Col3 CHAR(4000)) CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1) DECLARE @i INT = 0 WHILE (@i < 10) BEGIN INSERT INTO #TempTable VALUES ('Woody', 'Tu') SET @i += 1 END END ``` 在这个存储过程中，我们首先创建了一个临时表，并为它添加了一个唯一聚簇索引，然后插入了一些数据。由于在DML操作（插入数据）之后进行了DDL操作（创建索引），所以SQL Server无法缓存这个临时表。可以通过监视`sys.dm_os_performance_counters`动态管理视图的`Temp Tables Creation Rate`计数器来验证这一点，你会发现每次调用存储过程时都会创建新的临时表。 为了避免这种问题，可以将索引创建与数据插入分开，或者使用 PRIMARY KEY 约束来隐式创建唯一聚簇索引，这样就不会混合使用DDL和DML语句了。例如： ```sql ALTER PROCEDURE PopulateTempTable AS BEGIN CREATE TABLE #TempTable (Col1 INT IDENTITY PRIMARY KEY, Col2 CHAR(4000), Col3 CHAR(4000)) DECLARE @i INT = 0 WHILE (@i < 10) BEGIN INSERT INTO #TempTable VALUES ('Woody', 'Tu') SET @i += 1 END END ``` 在这个修正后的版本中，我们使用 PRIMARY KEY 约束代替了显式的索引创建，这样临时表的创建与数据插入就不再混合，SQL Server可以成功地缓存该临时表，从而提高了执行效率。 总结起来，理解并利用SQL Server 2014的临时表缓存机制，以及避免在存储过程中混合使用DML和DDL语句，是优化性能的关键。在编写存储过程时，应确保临时表的生命周期与数据操作分离，以便充分利用缓存机制，减少内存访问和锁竞争，从而提升系统性能。