SQL Server 中关于系统库Tempdb总结

Tempdb系统数据库是一个全局资源，可供连接到SQL Server实例的所有用户使用。

存储的内容项：

1、用户对象

用户对象由用户显示创建。这些对象可以位于用户会话的作用域中，也可以位于创建对象所用例程的作用域中。例程可以是存储过程、触发器或用户自定义函数。用户对象可以是一下项内容之一：

• 用户定义的表和索引
• 系统表和索引
• 全局临时表和索引
• table变量
• 表值函数中返回的表

2、内部对象

内部对象是根据需要由SQL Server数据库引擎创建的，用户处理SQL Server语句。内部对象可以在语句的作用域中创建和删除。内部对象可以是下列项之一：

• 用于游标或假脱机操作以及临时大型对象（LOB）存储的工作表
• 用于哈希连接或哈希聚合操作的工作文件
• 用于创建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中间排序结果，或者某些GROUP BY、ORDER BY或UNION查询的中间排序结果。
• 每个对象至少使用9页：一个IAM页，一个8页的区。

3、版本存储区

版本存储区是数据页的集合，它包含支持使用行版本控制的功能所需的数据行。主要用来支持快照（Snaphot）事务隔离级别，以及SQL Server2005推出的一些其它提高并法度的新功能。在SQL Server2005中，有两个版本存储区：公用版本存储区和索引生产版本区。包含以下内容：

• 由使用快照隔离界别或已提交读隔离界别（基于行版本控制）的数据库中的数据修改事务生成的行版本
• 由数据修改事务为实现联机索引操作、多个活动的结果集（MARS)以及AFTER触发器等功能而生成行版本

在SQL Server2005以后，tempdb需要的磁盘空间会多于早期版本。因此当升级到SQL Server2005或SQL Server2008后，管理员提供的tempdb磁盘空间可能得比以前要大一些，用以容纳当前生产工作负荷并满足使用tempdb的SQL Server功能的额外空间要求。

Tempdb空间使用跟踪

tempdb空间使用的一大特点，是只有一部分对象，比如：临时表、table 变量等，可以用sys.allcoation_units和sys.partitions这样的管理视图来管理。所以用sp_spaceused的结果和真实情况会有很大差异。

提示：tempdb的空间使用是不能用sp_spaceused来跟踪的。

DBCC SHOWFILESTATS还是可以反映数据库文件使用的整体情况的。

在2005版本以后引入了一个新的视图：sys.dm_db_file_space_usage;这张视图能反映出tempdb在如下几个大类的空间里使用分布：

select * from sys.dm_db_file_space_usage

其中：

database_id：数据库ID

file_id：文件ID,file_id映射到sys.dm_io_virtual_file_stats中的file_id,并且映射到sys.sysfile中的fileid

unallocated_extent_page_count：文件未分配区中的总页数，不包含已分配区中的未使用页

version_store_reserved_page_count：为版本存储分配的统一区中的总页数

user_object_reserved_page_count：为从统一区为数据库中的用户对象分配的总页数。计数中包括已分配区中未使用的页。可以使用sys.allocation_units目录视图中的total_pages列来返回用户对象每个分配单元保留的页计数

internale_object_reserved_page_count：从统一区为文件中的内部对象分配的总页数。计数包括已分配区中未使用的页

mixed_extent_page_count：文件的已分配混合区中的已分配和未分配总页数。混合区包含分配给不同对象的页。此计数器包含文件中的所有的IAM页

通过这个表的监视，就能知道temdb的空间被那个一块对象使用掉了，是用户对象（user_object_reserved_page_count)，还是系统对象（internal_object_reserved_page_count）,还是版本存储区对象（version_store_reserved_page_count）。

tempdb在生产环境中的设置的初始大小其实是一个经验值，也就是说没有一个好的固定值，但是我们可以根据工作负荷来确定该值的大小值：

1、设置tempdb的自动增长

2、模拟各个单独的查询或工作任务，同时监视tempdb空间使用

3、模拟执行一些系统维护操作，例如，重新生成索引，同时监视tempdb空间

4、使用前面2和3步中tempdb空间使用值来预测总的工作负荷下，会使用多少空间；并针对计划的并发度调整此值。例如，如果一个任务会使用10GB的tempdba空间，而在生产环境里，最多可能会有4个这样的任务同时运行，那就要至少预留40GB的空间。

5、设置tempdb在生产环境下的初始大小。同时也开始自动增长。

Temp文件个数以及大小设置，不但满足用户任务的需求，还要考虑到性能优化。

 我们利用脚本来跟踪一下，看看是什么样的操作导致了SQL Server要这样大规模的使用tempdb,然后分析一下是正常行为还是异常行为，监视SQL Server的行为当然可以利用SQL Trace来完成。可是管理员并不能预期造成大量使用tempdb的语句会在什么时刻运行。这些语句可能一周只做一次。而且开跟踪毕竟对SQL Server来讲是一个比较昂贵的事情，就算没有性能副作用，如果一直开着SQL Trace,也会产生大量的跟踪文件，对硬盘是个比较重的负担。

推荐的“轻量级”的操作是以一定的时间间隔运行能够监视系统运行状况的DBCC命令、查询管理视图（DMV）以及管理函数（DMF）等，把这个结果输出到一个文件里。

新开一个连接，我们输入以下常规脚本：

select @@SPID
go
use AdventureWorks
go 
select GETDATE()
go 
select * into #MyOrdersDetail
from Sales.SalesOrderDetail
--创建一个临时表
--这个操作应该会申请用户对象页面
go
waitfor delay '0:0:2'
select GETDATE()
go
drop table #MyOrdersDetail
--删除一个临时表
--这个操作用户对象页面数量应该会下降
go
waitfor delay '0:0:2'
select GETDATE()
go
select top 100000 * from
[Sales].[SalesOrderDetail] 
inner join [Sales].[SalesOrderHeader] 
on [Sales].[SalesOrderHeader].[SalesOrderID] =
[Sales] .[SalesOrderHeader].SalesOrderID;
--这里做了一个比较大的连接，应该会有系统对象申请
go
select GETDATE()
--join 语句做完以后系统对象页面数目应该会下降
go

然后另开一个窗口，我们输入监控脚本，输出结果以文本方式存储

use tempdb
--每隔1s运行一次，直到用户手工终止脚本运行
while 1=1
begin
select GETDATE()
--从文件级看temp使用情况
dbcc showfilestats
--第一个会话
--返回所有做过空间申请的会话信息
select 'Tempdb' as DB,GETDATE() as Time,
sum(user_object_reserved_page_count)*8 as user_obectskb,
SUM(internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM(version_store_reserved_page_count)*8 as version_store_kb,
SUM(unallocated_extent_page_count)*8 as freespace_kb
from sys.dm_db_file_space_usage
where database_id=2

--第二个会话
--这个管理视图能够反映当时tempdb空间的总体分配
select t1.session_id,
t1.internal_objects_alloc_page_count,
t1.user_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count,
t1.user_objects_dealloc_page_count,
t3.*
from sys.dm_db_session_space_usage t1,
--反映每个会话累计空间申请
sys.dm_exec_sessions as t3
--每个会话信息
where 
t1.session_id=t3.session_id
and (t1.internal_objects_alloc_page_count+t1.internal_objects_dealloc_page_count+t1.user_objects_alloc_page_count+t1.user_objects_dealloc_page_count)>0

--第三会话
--返回正在运行并且做过空间申请的会话正在运行的语句
select t1.session_id,
st.text
from sys.dm_db_session_space_usage as t1,
sys.dm_exec_requests as t4
cross apply sys.dm_exec_sql_text(t4.sql_handle) as st
where t1.session_id=t4.session_id
and t1.session_id>50
and (t1.internal_objects_alloc_page_count+t1.internal_objects_dealloc_page_count+t1.user_objects_alloc_page_count+t1.user_objects_dealloc_page_count)>0

waitfor delay '0:0:1'
end

我们先运行监控，然后再运行一般脚本，可以分析一下跟踪结果。

可以看到在新建临时表的时候，对于Tempdb的申请空间有所增加。