认识Tempdb----概览和用法

23ee

tempdb是一个临时数据存储，用于应用程式和内部操作，它很类似其他数据库，因为它有一个数据文件和一个日志文件，能够在SSMS看到，但是，它有一些独特的特征，这些特征影响你如何使用和管理它。使用一个实例的任何人都共享同一个tempdb。在学习、使用、调校和故障排除时，你应该考虑tempdb的如下功能和属性：

• 重启后，存储在tempdb中的任何东西都不会保存，因为SQL Server每次启动的时候会重建tempdb。
• tempdb的恢复模式常被设置为“Simple”，这意味着每次检查点之后，已提交的事务的日志记录都被标记为复用，意味着你不需要为tempdb备份事务日志，事实上，你不能备份tempdb。
• tempdb仅能有一个文件组（PRIMARY文件组），你不能再额外添加。
• tempdb用于存储3种类型的对象：用户对象，内部对象和版本存储。
  

用户临时对象
要临时存储数据，你可以使用本地临时表、全局临时表、或表变量，所有这些都存放在tempdb。定义本地临时表是以“#”为前缀的，作用域是你创建的对话。这意味着么人能够看见它，当你断开连接、或则对话随着连接池被重置时，表就被删除了。下面是个例子：

• CREATE TABLE #TempTable ( ID INT, NAME CHAR(3) ) ;  
• INSERT INTO #TempTable ( ID, NAME )  
• VALUES ( 1, 'abc' ) ;  
• GO  
• SELECT *  
• FROM #TempTable ;  
• GO  
• DROP TABLE #TempTable ;  
  

全局临时表能够被连接到服务器的所有对话看到，它们的定义是以“##”为前缀的。它们的使用和本地临时表完全一样，唯一的不同是每个人都能看见它们。它们不常被使用，因为如果你需要多个用户使用同一张表，你更可能用一个正常的表，而不是一个全局临时表。下面是全局临时表的例子：

• CREATE TABLE ##TempTable ( ID INT, NAME CHAR(3) ) ;  
• INSERT INTO ##TempTable ( ID, NAME )  
• VALUES ( 1, 'abc' ) ;  
• GO  
• SELECT *  
• FROM ##TempTable ;  
• GO  
• DROP TABLE ##TempTable ;  
  

表变量的使用类似本地临时表，下面是例子：

• DECLARE @TempTable TABLE ( ID INT, NAME CHAR(3) ) ;  
• INSERT INTO @TempTable ( ID, NAME )  
• VALUES ( 1, 'abc' ) ;  
• SELECT *  
• FROM @TempTable ;  
  

表变量的作用域是批（batch），而不是对话（session）。上面额例子中，如果你把“GO”置于SELECT语句前，那么SELECT语句会报“object does not exist”。
临时表 VS 表变量
统计（Statistics）：临时表和表变量主要的不同在于表变量不会创建统计。不管表变量有多少行数据，查询优化器总是估计一行，因为它没有可靠的统计来产生更好的估计。
索引：尽管能创建约束，但不能对表变量创建索引。
Schema修改：修改临时表的Schema是可能的，但不能对表变量修改。虽然可以对临时表修改Schema，但要避免使用它们，因为这会导致重新编译使用该表的语句。
下表是临时表和表变量之间对比的小结：

表变量不是在内存里创建的，你可以通过sys.dm_db_session_space_usage来验证。表变量和临时表中，最好使用临时表，因为临时表引起的问题比表变量少，表变量中的数据会不断积累，以至于影响性能。
内部临时对象
内部临时对象是查询处理期间SQL  Server用来临时存储数据的对象。操作如排序（sort）、spool、哈希连接和游标等，它们都需要tempdb的空间来运行。要查看每个对话有多少页被分配给内部对象，你可以查看sys.dm_db_session_space_usage中的列internal_object_alloc_page_count。
版本存储（Version Store）
在SQL Server 2012中，很多功能需要保持多个行版本，版本存储用于存储这些索引和数据行的不同版本。下面的功能会使用版本存储：

• 触发器----从SQL Server 2005开始使用行版本，而不是像SQL Server 2000中扫描事务日志。
• 快照隔离和已提交读快照隔离----这两种新的隔离级别基于行版本，而不是锁。
• 在线索引操作----在索引重建期间行版本支持索引更新。
• MARS（Multiple Active Result Sets）----行版本支持单连接下的交叉多批请求（interleaving multiple batch requests）。
  

版本存储开销
行版本的开销是是每行14个字节，它们由事务序列号（XSN）和行标识符（RID）组成，如下图所示：

XSN用于把同一行的多个版本串在一起，RID用于定位tempdb中的行版本。14个字节的开销没有降低8060字节的最大可能的行大小，它被添加是一行首次被修改或插入的时候（使用快照隔离、基础表有触发器、使用MARS、正在对表进行在线索引重建），它被移除是在这些情况下，即关闭快照隔离、移除触发器、停止使用MARS，以及在线索引重建完成。你应该也会意识到，如果数据页满了，创建额外的14字节能够引起页分离，并影响磁盘空间需求。
只附加存储（Append-Only Stores）
有两种行版本写入只附加存储；索引重建有自身的版本存储，其他的会使用普通的版本存储。为了增加可扩展性，在版本存储中，每个CPU调度都有自己的页用来存储行。如下图有4个CPU内核的计算机中所示的：

你可以使用DMV sys.dm_tran_version_store来查看版本存储的完整内容，但是使用它要当心，因为运行它很耗资源。为了展示如何使用行版本，下图展示了快照隔离下一个有多个读取和写入的事务操作的例子：

图8-7中，底部的0~60表示时间轴，水平箭头表示一个特定事务的持续时间。事件发生的先后顺序如下：

• 在时间轴10处，Read1事务开始，并读取与XSN-100关联的行；
• 在20处，另一个事务Write1开始，它想修改行。快照隔离保证Read1的可重复读，并确保任何新的读者能够在写入开始的这个时间点读到已提交的数据。因此，它把与XSN-100相关的行复制到版本存储，并允许Write1修改XSN-110下的行；
• Read2在Write1提交之前开始了，因此，在版本存储中，版本链从XSN-110穿越到XSN-100，以获取最后提交的值。
• Read3在Write1提交后开始了，它读取XSN-110的值。
• Write2现在开始了，它想修改行。Read1和Read2仍然需要XSN-100下面的版本，Read3需要XSN-110下的版本，因此，一个新的版本被创建为XSN-120，XSN-110被搬到tempdb中的版本存储。
• Write2提交XSN-120.
• Read1完成，但是XSN-100仍然被Read2使用
• Read2完成，XSN-100成为过去。
• Read3完成，XSN-110成为过去。
  

后台线程每分钟一次移除tempdb中过时的行版本，因此，在那个时间点，由事务Write2执行的写操作的结果将被保持，在tempdb中没有之前的版本可用或存储。图8-8表示数据页上行的状态，在时间轴0处版本存放在tempdb中。你可以看到，唯一可用的结果是当前提交的XSN-100的值：

图8-9显示了时间轴45处的状态。两个版本正被维护进tempdb，来为Read1、Read2和Read3事务提供可重复读：

图8-10显示了时间轴60。需要之前版本以维持快照隔离级别的所有事务，现在都完成了，因此，存储在tempdb中的过时的版本被后台线程清除：