sql server 临时表(上) Tempdb概述 - 花阴偷移 - 博客园

baacloud免费翻墙vpn注册使用

来源: 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。

 

  1. 二. 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库进行监控。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏