Mikel
来源： sql server 临时表(上) Tempdb概述 – 花阴偷移 – 博客园
sql server 临时表(上) Tempdb概述
一.概述
在SQL server里临时表存储在TempDB库中，TempDB是一个系统数据库，它只有Simple恢复模式，也是最小日志记录操作。主要用于存放局部临时表，全局临时表，表变量，都是基于临时特征，每次服务器或服务重启后，都会按照Model库的配置重新创建TempDB库。在SQL server 2012中TempDB表可以配置在故障转移中。在TempDB库中存放三类对象包括：用户对象, 内部对象, 行版本存储。TempDB库只有一个文件组，就是primary文件组，增加其它文件组会提示报错。在权限方面，所有用户默认都具有访问TempDB库权限。
1.1 Tempdb 的物理属性
下表列出了 tempdb 数据和日志文件的初始配置值。 对于不同版本的 SQL Server，这些文件的大小可能略有不同。
| 文件 | 逻辑名称 | 物理名称 | 文件增长 |
| 主数据 | tempdev | tempdb.mdf | 按 10% 自动增长，直到磁盘已满 |
| 日志 | templog | templog.ldf | 以 10% 的速度自动增长到最大 2 TB |
1.2 使用Tempdb的作用
(1) 通过tempdb库可以缓存临时表和表变量，用于减少数据表的查询次数以及锁问题。缓存允许删除和创建临时对象的操作能快速地执行，并减少页分配的争用问题。
(2) 分配页闩锁协议得到改善。 从而减少使用的 UP（更新）闩锁数。
(3) 减少了 tempdb 的日志开销。 从而减少了 tempdb 日志文件上的磁盘 I/O 带宽消耗。
(4) 分配混合的页中的算法tempdb得到了改进。
1.3 使用tempdb的限制, 不能对 tempdb 数据库执行以下操作：
(1)添加文件组。
(2)备份或还原数据库。
(3)更改排序规则。 默认排序规则为服务器排序规则。
(4)更改数据库所有者。 tempdb 的所有者是 sa。
(5)创建数据库快照。
(6)删除数据库。
(7)从数据库中删除 guest 用户。
(8)启用变更数据捕获。
(9)参与数据库镜像。
(10)删除主文件组、主数据文件或日志文件。
(11)重命名数据库或主文件组。
(12)运行 DBCC CHECKALLOC。
(13)运行 DBCC CHECKCATALOG。
(14)将数据库设置为 OFFLINE。
- 二. TempDB存储的三类对象
在SQL 2005以后，引入了一张新的管理视图：sys.dm_db_file_space_usage。通过查询这张视图，能了解tempdb的空间使用情况，能知道tempdb的空间是被哪一块对象使用掉的，是用户对象(user_object_reserved_page_count字段)，还是系统对象（internal_object_reserved_page_count字段），还是版本存储区（version_store_reserved_page_count字段。在了解这个dmv视图之前，先了解tempdb库的三类对象。
2.1 用户临时对象(user_object_reserved_page_count)
由用户会话显示创建的对象，可以在系统表目录中找到，用户临时对象主要包括以下内容：
(a) 创建临时表和索引，不过这些表在重启后清空。
(b) 全局临时表以 ##开头的表。适用于所有会话操作该表信息。需要显示的删除或重启服务才能清除表。
(c) 局部临时表以 #开头的表。适用于当前会话操作该表信息。会话结束清除表。
(d) 表变量, 以@开头。
下面是简单示例，演示局部临时表，临时表索引，全局临时表，表变量，脚本如下：
— 创建局部临时表 CREATE TABLE #TempTable(id INT,NAME VARCHAR(10)) — 根据已有表,创建临时表 SELECT * INTO #TempTable2 FROM tablename –创建局部临时表, 带有聚集索引 CREATE TABLE #tempWithCLUSTERED([SID] INT PRIMARY KEY CLUSTERED, model VARCHAR(50)) — 创建全局临时表 CREATE TABLE ##TempTable3(id INT,NAME VARCHAR(10)) — 根据已有表创建全局临时表 SELECT * INTO ##TempTable4 FROM dbo.ACT_AnswerTab — 删除全局临时表 DROP TABLE ##TempTable4 — 创建表变量 DECLARE @tablevar TABLE (id int,NAME varchar(10))
下面简单介绍一下，临时对象中表变量与临时表区别
(1)统计信息
表变量没有统计信息，统计信息要么为0，要么为1。sql server查询优化器只会把表变量当作里面只有1条数据或没有数据的表对待，脚本演示如下所示：
— 创建表变量示例 DECLARE @tablevar TABLE (id int,NAME varchar(10)) INSERT INTO @tablevar(id,name) SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values SELECT * FROM @tablevar
(2) 索引
表变量使用索引，只能是主键(可以是聚集和非聚集索引)或者唯一约束，需要预先定义好，定义后不能再添加索引了，注意：即使是有索引，也没有统计信息。表变量使用聚集索引如下所示：
DECLARE @tablevar TABLE (id INT PRIMARY KEY CLUSTERED,NAME varchar(10)) INSERT INTO @tablevar(id,name) SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values SELECT * FROM @tablevar WHERE id=1
(3) 架构修改
架构修改可以出现在临时表上，但不会出现在表变量上面，修改会产生重编译，非预期的重编译不是好事。下面是临时表架构修改，将name字段长改为50, 脚本如下：
ALTER TABLE #TempTable ALTER COLUMN name NVARCHAR (50) NULL USE tempdb go sp_help #TempTable
(4) 对比表格
| 特征 | 临时表 | 变量表 |
| 命名 | 以#开头 | 以@开头 |
| 统计信息 | 有 | 无 |
| 索引 | 有 | 仅约束可用 |
| 架构修改 | 允许 | 不允许 |
| 在sp_executesql中使用 | 可以 | 不可以 |
| 使用insert into xxx exec | 可以 | 不可以 |
| 存在于内存中 | 否 | 否 |
总结: 建议:小于100行使用表变量。数据都不是存入于内存中，而是存放在TempDB表中，通过sys.dm_db_session_space_usage这个dmv可查看空间占用情况。后面监控时再说这dmv。
2.2 内部对象（internal_object_reserved_page_count）
内部对象是根据需要 由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。主要是查询过程中存储临时数据的对象，这些临时数据的对象可以在语句的作用域中自动创建和删除。 内部对象主要包括以下内容：
(a) 游标
(b) 哈希(Hash)联接或哈希聚合操作的查询
(c) 某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果.
在内部对象中比较常见的是Worktable, 这个临时表是自动生成，自动销毁，主要用于游标，假脱机，临时大对象的数据类型(LOB)存储，这个内部临时表用于返回查询的中间结果，监视分析Worktable可以通过打开statistics io 选项，执行sql语句再查看，要注意的是：高效的查询下很少出现这种现象。
2.3 版本存储（version_store_reserved_page_count)
版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。这类并发模式会借用TempDB来存放修改前的版本数据，第一行数据被修改前，都会在TempDB中创建一个相同的行，并加上14bytes的长度，包含: (1)修改前的事务序列号(XSN)长度为6 bytes (2) TempDB的行标识符(RID)长度为8bytes。版本存储用于存储行版本、MARS、联机索引、触发器、基于快照的隔离级别。如果开启了乐观并发模式(已提交读快照和快照二种隔离级别)，可能造成Tempdb库的非预期增长， 需要对Tempdb库进行监控。
