tempdb(转载)

这些天看了一篇微软官方发布的MS SQL Server2008性能问题处理及优化的英文文档，里面知识点介绍地很详细，在现实工作中也很实用，遂产生了想把它翻译一下的念头。翻译的过程，既可以帮助自己复习一下这些技术，也可以向其他还不熟悉这一块的朋友介绍一些新的知识，何乐而不为呢。只是这篇文章有点长，我会分成几篇随笔去介绍，所以，不光是对我耐性的考验，也是对你的考验哦！

--------------------------------------------

tempdb全局存储内部对象，用户对象，临时表，临时对象，以及SQL Server操作创建的存储过程。每个数据库实例只有一个tempdb，所以可能存在性能以及磁盘空间瓶颈。各种形式的可用空间及过度饿DDL/DML操作都会导致tempdb负载过重。这会导致运行在服务器上不相干程序运行缓慢或者运行失败。

　　tempdb的一些常见通病如下：

　　--耗完了tempdb的所有存储空间

　　--读取tempdb时的I/O瓶颈造成的查询运行缓慢。

　　--过度的DDL操作造成在系统表上的瓶颈。

　　--分配竞争

　　在我们开始诊断问题之前，让我们首先看一下tempdb的空间都用在了哪些地方。可以分成四个主要的类比：

类别	描述
用户对象	这些是明确地由用户创建并且在系统类别中进行追踪。他们包括下面的： --表及索引 --全局性质的临时表（##t1）以及索引 --局部临时表（#t1）及索引　　会话范围的　　存储过程范围的 --表变量（@t1）　　会话范围的　　存储过程范围的
内部对象	SQL Server在运行查询的时候会创建或销毁许多语句范围的对象。这些没有在系统类别中被追踪。他们包括以下内容： --工作文件（hash连接） --排序运行 --工作表（游标，池以及临时的大对象数据类型（LOB）存储） --这里有两种情况除外：临时的大对象存储是批处理范围的，另一是游标工作表是会话范围的
版本存储	这个被用来存储行版本。MARS，在线索引，触发器，以及快照隔离级别都是基于行版本的。
空闲空间	这个显示出了可用于tempdb的磁盘空间

　　总的tempdb空间=用户对象+内部对象+存储的版本信息+空闲空间。

　　这个空闲空间大小跟tempdb性能计数器上空闲空间是一样的。

监测tempdb空间

　　提前避免问题的发生总是比出现问题之后再去解决要好的多。你可以使用Free Space in tempdb(kb)性能计数器去监测正在使用的tempdb空间数量。这个计数器以kb为单位追踪空闲空间。管理员可以使用这个计数器去判断tempdb是否因为空闲空间倒置运行缓慢。

　　然而，明确前面提到的四种类别如何使用tempdb磁盘空间的，就显得更有趣也更有效。

　　下面的查询语句返回用户及内部对象使用的tempdb空间。

select SUM(user_object_reserved_page_count)*8 as user_objects_kb,
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

　　下面是实例输出（空间大小按kb计算）

user_objects_kb internal_objects_kb version_store_kb freespace_kb
320 320 　　　　　　　　128 　　　　　　　　6208

　　请注意这些结果值没有计算包含在混合事物中的页。混合事物中的页可以分配给用户及内部的事物。

空间问题故障排除

　　用户对象，内部对象，以及版本存储都可以导致tempdb出现空间问题。在这个章节里，我们考虑如何针对这四个类别进行故障排除。

用户对象

　　因为用户对象不专属于任意指定的会话，你需要参照应用程序创建这个对象的规范去调整tempdb需求的大小。你可以找到各个用户对象使用的空间通过exec sp_spaceused @objname='<user-object>'. 例如，你可以运行下面的脚本去枚举所有的tempdb对象。

 declare userobj_cur cursor for
 select sys.schemas.name +'.'+sys.objects.name
 from sys.object,sys.schemas
 where object_id>100 and
 type_desc='USER_TABLE' and sys.objects.schema_id=sys.schemas.schema_id
 go
 
 open userobj_cursor
 go
 
 declare @name varchar(256)
 fetch userobj_cursor into @name
 while (@@FETCH_STATUS=0)
 begin
 exec sp_spaceused @objname = @name
 fetch userobj_cursor into @name
 end
 close userobj_cursor

版本信息

　　SQL Server 2008提供了一个行版本的框架。当前，下面这些特性使用到了行版本的框架：

　　--触发器

　　--MARS

　　--在线索引

　　--行版本控制的隔离级别：需要在数据库级别上设定一个选项。

　　更多信息，可以参考row versioning resource usage.

　　行版本可以在各个会话间共享。在行版本被回收时，创建者并不能控制。你可能需要找到然后停掉那个最长的，正在运行的会阻止行版本清空的事务。下面的查询返回了最上面的两个最长时间运行的事务，取决于存储在版本中心中版本。

 select top 2 
 transaction_id,
 transaction_sequence_num,
 elapsed_time_seconds 
 from sys.dm_tran_active_snapshot_database_transactions
 order by elapsed_time_seconds desc

　　实例输出，结果显示id为8609的事务已经激活了6,523秒。

transaction_id　　transaction_sequence_num　　elapsed_time_seconds

8609　　　　　　　　3　　　　　　　　　　　　　　6523

21056　　　　　　　25　　　　　　　　　　　　　　783

　　因为第二个事务已经激活了一段不太长时间，你或许可以通过停掉第一个事务从而释放掉版本中心里的一定数量。然而，并没有一个方式可以列举出来，一旦释放后，到底有多少版本空间可以被释放出来。你获取需要停掉其它更多的事务从而释放更多重要的空间。

　　你可以通过设定针对某一账户在tempdb上的版本存储属性来缓和这一问题，如果可能的话，消除长时间运行的快照隔离级别的事务或者长时间运行的已提交读快照隔离级别。你可以使用下面的公式粗略估计下版本存储所需要的大小。（乘以2的原因是需要计算最坏打算的情况，也就是当两个最长运行的事务重叠时。）

[Size of version store]=2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

　　在所有启用了基于行版本控制隔离级别的数据库中，事务的版本存储数据跟日志数据一样，都是每分钟生成一次。然而，这里也有一些例外：仅仅是在记录更新日志时会有一些不同；并且一个最新的插入的数据行没有被记录行版本信息，但是它会被记录在事务日志中，如果它是一个bulk-logged操作并且恢复模型不是完整回复模型。

　　你也可以使用Version Generation Rate和Version Cleanup Rate性能计数器去优化你的计算。如果你的Version Cleanup Rate是0，可能是因为一个长时间运行的事务阻碍了版本存储中心的清理。

　　顺便提一下，在生成一个out-of-tempdb-space错误之前，SQL Server 2008会最后一搏尝试强制清除版本存储中心。在清除过程中，最长的那个还没有生成任何行版本的事务会被标记为受害者。这会将它们使用的版本空间释放出来。错误日志会为每个受害者存储一个编号为3967的消息。如果某个事务被标记为了受害者，它就不能在向版本存储中心中存储版本信息，也不可以去读取存储在里面的行版本信息。当受害者事务尝试去读取行版本信息时，3966号错误会生成，并且这个事务会被回滚。如果清除版本存储中心成功的话，tempdb就会有更多的可用空间。否则，tempdb就会无空间可用。

内部对象

　　内部对象是为每个语句而创建或销毁的，这点已经在前面的概述中有所介绍。如果你注意到有很多的tempdb空间被分配，你应该确定下是哪个会话或者任务正在使用这些空间，并且可能的话采取对应的措施。

　　SQL Server 2008提供了两个DMVs，sys.dm_db_session_space_usage和sys.dm_db_task_space_usage，去检测被分配给会话或者任务的空间。尽管任务是运行在会话的上下文中的，但是被会话中的任务使用的空间只能在任务完成之后才会被统计出来。

　　你可以使用下面的查询去查找分配内部对象最多的会话。注意，这个查询仅包含会话中已经完成的任务。

select 
    session_id,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count desc

　　你可以使用下面的查询去查找分配内部对象最多的会话，包含当前活动的任务。

select 
   t1.session_id,
   (t1.internal_objects_alloc_page_count + task_alloc) as allocated,
   (t1.internal_objects_dealloc_page_count + task_dealloc) as     deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum(internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id>50
order by allocated desc

　　下面是实例输出：

session_id　　allocated　　deallocated

------------ ------------ --------------

52　　　　　　5120　　　　5136

51　　　　　　16　　　　　　0

　　在你已经定位到了分配很多内部对象的任务和会话后，你可以找出是哪个T-SQL语句，进而做更深的分析。

select 
    t1.session_id, 
    t1.request_id, 
    t1.task_alloc,
    t1.task_dealloc,
    t2.sql_handle, 
    t2.statement_start_offset, 
    t2.statement_end_offset, 
    t2.plan_handle
from (Select session_id, 
             request_id,
             sum(internal_objects_alloc_page_count) as task_alloc,
             sum (internal_objects_dealloc_page_count) as task_dealloc 
from sys.dm_db_task_space_usage 
      group by session_id, request_id) as t1, 
      sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and 
     (t1.request_id = t2.request_id)
order by t1.task_alloc DESC

下面是实例输出：

session_id request_id task_alloc task_dealloc

---------------------------------------------------------

sql_handle statement_start_offset

-----------------------------------------------------------------------

0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356

52 0 1024 1024

statement_end_offset plan_handle

---------------------------------

-1 0x06000500D490961BA8C19503000000000000000000000000

你可以使用sql_handle和plan_handle列去获取sql语句及查询计划。

select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)

　　请注意，当你想去访问一个查询计划时，有可能它没有在缓存中。去保证查询计划的可用性，经常投票计划缓存并且保存结果，最好是保存在表中，这样的话以后就可以直接查询结果了。

　　当SQL Server重启以后，tempdb数据的大小会恢复到初始化配置的大小，并且按照需求增长。这回导致tempdb碎片并招致开销，包括数据库自动增长的过程中分配事物时导致的阻塞，并且对tempdb大小的扩展。这会影响你工作负载的性能。我们推荐你提前微软tempdb分配一个合适的大小。

过度的DDL及分配操作

　　tempdb中的两种资源竞争会导致下面的情况。

　　创建及销毁大量的临时表及表变量会引起在元数据上的竞争。在SQL Server2008中，本地的临时表及表变量会被缓存并最小化元数据的竞争。然而，下面的情况必须满足；否则，临时的对象不会被缓存：

　　--未创建命名的约束

　　--临时表创建以后，影响表的DDL语句却没有运行，例如Create Index或者Create Statistics语句

　　--临时对象不是通过使用动态SQL创建的，例如：sp_executesql N'create table #t(1 int)'

　　--临时对象是在其它的对象中创建的，例如一个存储过程，触发器，或者用户定义的函数；或者临时对象是由用户自定义的表值函数返回过来的。

　　典型地，许多临时的/工作表是堆形式的；这就会导致，一个insert，delete或者drop操作会在空闲页空间上导致繁重的竞争。如果大部分的表下月64kb并且为分配及交互分配使用固定的范围，这会导致在共享全局分配映射（Shared Global Allocation Map）上形成严重的竞争。

　　SQL Server 2008缓存一个数据页和一个IAM页去最小化分配时的竞争。工作表缓存被改善。当一个查询执行计划被缓存以后，计划所需要的工作表不会在多个执行计划中被销毁掉，但是几乎会被清除掉。此外，工作表的第一个前九页被保持了下来。

　　因为共享全局分配映射(SGAM)和页空闲空间（PFS）发生在数据文件中的固定间隔里，所以非常容易去查找它们的资源描述。所以，例如，2:1:1代表tempdb中的第一个PFS页（database-id=2，file-id=1，page-id=1）并且2:1:3代表第一个SGAM页。SGAM页发生在每511,232个页之后，并且每个PFS及SGAM页贯穿在tempdb的所有文件间。你可以使用这个去查找tempdb中的所有其它的PFS和SGAM页。任意时间，这些页上正在等待获取闩锁的任务，都可以在sys.dm_os_waiting_tasks中查看。因为闩锁等待是瞬间变化的，所以你应该经常性地查询这个表（大概每10秒钟一次）并且获取这些数据用于之后的分析。例如，你可以使用下面的查询去将所有等待tempdb页的任务载入到waiting_tasks这个用于分析的表中。

--get the current timestamp
declare @now datatime
select @now=getdate()

--insert data into a table for later analysis
 insert into analysis..waiting_tasks
select 
        session_id,
        waiting_duration_ms,
        resource_description,
        @now
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%' and resource_description like '2:%'

　　任意时刻你看到任务正在等待获取tempdb页上的闩锁时，你可以分析下是否是由PFS或者SGAM页导致的。如果是这样的话，这会意味着tempdb上存在着分配竞争。如果你在tempdb的其它页上看到了竞争，并且如果你可以明确了解到这个页属于系统表，这个竞争是由过度的DDL操作导致的。

　　你也可以检测下面的性能监测计数器，从而去检测任何存在于tempdb对象分配及交互活动上的不正常增长：

　　--SQL Server:Access Methods\Workfiles Created/Sec

　　--SQL Server:Access Methods\Worktables Created/Sec

　　--SQL Server:Access Methods\Mixed Page Allocations/Sec

　　--SQL Server:General Statistics\Temp Tables Created/Sec

　　--SQL Server:General Statistics\Temp Tables for destruction

解决方案

　　如果tempdb中的经常是由过度的DDL操作导致的，你应该查看你的应用程序并查看你是否可以最小化那些DDL操作。你可以尝试下面的建议：

　　--从SQL Server 2005开始，如果是前面所描述的那些情况，临时对象会被缓存下来。然而，如果你仍然需要存在明显的DDL竞争，你需要查看下有哪些临时对象没有没缓存下来以及它们发生在哪里。如果这个对象发生在循环或者存储过程里，考虑将它们从循环或者存储过程中移出来。

　　--检查查询计划去查看下是否有些计划创建了许多的临时对象，线轴，排序或者工作表。你可能需要去消除一些临时的对象。例如，在一个列上创建一个索引可能会消除排序。

　　如果经常是由SGAM和PFS导致的，你可以通过下面的方式减轻它：

　　--增加临时数据文件以在不同文件或者硬盘上异步分散工作负载。典型地，你想创建多个文件，因为那里有多个CPU。

　　--使用TF--1118去消除固定范围的分配。