TempDB:
TempDB是一个全局数据库，存储内部和用户对象还有零食表、对象、在SQLServer操作过程中产生的存储过程等数据。在一个SQLServer实例中，只有一个TempDB。所以容易成为性能和磁盘空间瓶颈。TempDB可能因为空间可用程度和过量的DDL和DML操作而变得超负荷。这可能导致不相关的应用运行缓慢甚至失败。
常见的TempDB问题如下：
l TempDB空间超支。
l 因为TempDB的I/O瓶颈而导致查询缓慢。这可以查看前面的I/O瓶颈章节。
l 过渡的DDL操作导致系统表产生瓶颈。
l 资源分配争用。
在开始诊断TempDB问题之前，先看看TempDB的空间是如何使用的。可以总结为4部分：
TempDB的总使用空间等于用户对象（userobjects）加上内置对象（internal objects）加上版本存储（version store）加上可用空间。
可用空间等于性能计数器中tempdb 的可用空间值。
监控Tempdb空间（Monitoring tempdb Space）：
提早发现问题总比出现了再解决要强。你可以使用性能计数器：Free Space in tempdb(KB)来监控TempDB的空间使用情况。这个计数器按KB来跟踪TempDB。DBA可以使用这个指针来判断tempdb是否运行在低空间环境。但是，标识不同类别，就像签名定义的一样，tempdb使用磁盘空间的情况是非常丰富的。下面的查询返回tempdb被用户和内置对象使用情况，注意，这仅仅适用于tempdb：
Select
SUM(user_object_reserved_page_count)*8 asuser_objects_kb,
SUM(internal_object_reserved_page_count)*8 asinternal_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
本机结果：
user_objects_kb internal_objects_kb version_store_kb freespace_kb
-------------------- ---------------------------------------- --------------------
NULL NULL NULL NULL
注意这些数据是不包含混合区的计算，混合区被分配给用户和内置对象。
空间分配故障排查：
用户对象、内置对象和版本存储能引起tempdb的空间申请，下面我们看看如何检查每部分的故障问题。
用户对象（User objects）:
因为用户对象不属于任何特定会话（specific sessions），你需要理解规范的应用程序应该根据特定的要求创建和调整用户对象。你可以通过运行exec sp_spaceused @objname=’<user-object>’来找到由个别用户对象使用的空间。比如，运行以下脚本来列举所有tempdb 对象：
DECLARE userobj_cursor CURSOR FOR
select
sys.schemas.name + '.' + sys.objects.name
from sys.objects, 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
版本存储（Version Store）：
SQLServer2008 提供一个行版本框架，目前为止，以下特性被用于行版本框架：
l 触发器
l MARS
l 联机索引
l 基于行版本隔离级别：需要在数据库级别设置选项
更多信息请查看联机丛书：RowVersioning 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
以下是示例结果：
transaction_id transaction_sequence_numelapsed_time_seconds
-------------------- --------------------------------------------
8609 3 6523
20156 25 783
因为第二个活动事务在一个短时期内被引用，所以你应该把时间花在第一个事务中。但是，没有办法预估多少版本空间将被停止的事务释放。所以建议停止多一点的事务来清空这部分的空间。
可以通过特定账号来固定tempdb中版本存储的空间或者靠清除，如果可能，应该清除运行最久的快照隔离事务或者使用已提交读快照隔离的运行最久的查询。可以使用以下公式大概预估行版本存储所需空间：
[Sizeof version store] = 2 * [version store data generated per minute] * [longestrunning time (minutes) of the transaction]
在所有允许使用行版本隔离级别的数据库中，版本存储每分钟产生的事务和日志数据产生的相同。但是也有例外：在更新时的日志记录；还有最近插入的数据是没有形成版本，但会被记录日志。如果是大容量日志操作，并且恢复模式不是完全恢复，你可以使用Version Generation Rate 和Version Cleanup Rate性能计数器来计算。如果VersionCleanup Rate为0，则运行久的事务会防止行版本存储被清空。附带说明，在发生tempdb空间不足的错误前，SQLServer2008会坚持到最后，防止行版本存储被收缩。在收缩过程中，运行最久的事务却没产生任何行版本的将被标记为“受害者”，并清空这部分的空间。消息3967就是在错误日志中显示每个受害事务的信息。一旦事务被标记为受害者，将不能创建或者访问行版本信息。消息3966记录受害事务尝试读取行版本时将被回滚的情况。当收缩行版本存储成功后，tempdb将有更多的可用空间，否则，tempdb将耗尽。
内置对象（Internal Objects）：
内置对象是被每个语句创建或销毁的对象，处理在前面说道的部分之外，其他都会创建。如果你发现在tempdb中有一个很大的空间被分配，你就要检查哪个会话或任务消耗了这部分空间，然后尽可能校正。
SQLServer2008提供DMVs：
sys.dm_db_session_space_usage 和sys.dm_db_task_space_usage
来追踪tempdb空间被哪些会话或者任务分配了。虽然任务是在会话环境下运行，但是任务的空间使用在任务完成之后才被会话占用的。可以使用以下查询来找到排行前列的会话分配。注意这些结果只包含任务已经完成的会话：
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order byinternal_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 ast1,
(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_usagegroup bysession_id) ast2
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
当你有一个隔离的任务或者产生大量内置对象分配的任务时，可以使用下面语句来发现这些语句和他们的详细执行计划：
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 bysession_id, request_id)as t1,
sys.dm_exec_requests ast2
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
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
-----------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
statement_end_offset plan_handle
---------------------------------
-1 0x06000500D490961BA8C19503000000000000000000000000
可以使用sql_handle和plan_handle列来得到语句的执行计划：
select text from sys.dm_exec_sql_text(@sql_handle)
select * fromsys.dm_exec_query_plan(@plan_handle)
注意，当你想访问这些执行计划的时候可能它们不再缓存中，为了保证执行计划的可用性，缓存的执行计划会频繁轮询和保存结果，以便更好地使用。所以它可能会在后来才查到。
当SQLServer重启时，tempdb会初始化并增长到配置大小。这可能导致tempdb出现碎片，和引起间接开销，包含数据库自动增长时申请新区而导致的阻塞，扩张tempdb空间。这可能导致你的工作负载增大而影响性能。建议预先设置tempdb到适合的大小。
过度的DDL和分配操作：
Tempdb争论的两个点为：
创建和删除大数据量的临时表或者表变量会引起源数据的争用。在SQLServer2008中，局部临时表和表变量只是缓存最小的源数据。但是，下面的条件必须满足。否则，这些临时对象将不会被缓存：
l 没有创建命名约束
l 作用在表上的DDL语句，在临时表创建后没有运行，比如CREATE INDEX或者CREATE STATISTICS语句。
l 没有使用动态SQL创建的临时对象，如sp_executesqlN’create table #t(a int)’。
l 在别的对象中创建的临时对象，比如存储过程、触发器或者用户自定义函数、或者在临时对象中返回用户自定义函数、表值函数。
具有代表性的是，几乎所有的在堆中的临时/工作表都有这种情况。所以，一个增、删、或者drop操作都会英气PFS(空页面空间)页面的严重资源争用。如果大部分这些表都小于64KB和使用混合区来分配空间，会给SGAM（共享全局分配映射）页也带来很重的负担。
SQLServer2008缓存一个数据页和一个IAM页给均不临时表作为最小分配资源。工作表的缓存改进了。当一个查询执行时，计划也会被缓存，工作表在多个执行中的计划里面被使用，但很少清空。此外，第一个工作表的9个页面会被保留。
因为SGAM和PFS页发生在数据文件中固定间隔发生。所以容易找到它们的资源描述。所以，比如2：1：1表示在tempdb中的第一个PFS页（databaseid=2,fileid=1,pageid=1），2：1：3表示第一个SGAM页。SGAM页在每511232个页面后产生一个。PFS页会在每8088个页面后产生一个。你可以通过这个特性去tempdb中超找所有PFS和SGAM页。任何时候一个任务都会等待得到这些页上的闩锁（latch），这些信息保存在sys.dm_os_waiting_tasks表中。由于闩锁等待是很短暂的，所以你可以经常查询这些表（大概10秒一次）。并且收集这些信息做后续分析。比如，你可以使用下面面查询去加载所有在tempdb页中等待的任务到Analysis数据库的waiting_tasks表中：
-- get the current timestamp
declare @now datetime
select @now = getdate()
-- insert data into a table forlater analysis
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like‘PAGE%LATCH_%’ and
resource_description like ‘2:%’
任何时候当你在表中发现tempdb页中的latch申请，你就能分析是否基于PFS/SGAM页。如果是，意味着在tempdb中存在分配争用。如果看到争用在tempdb的其他页，并且如果你能识别这些也属于系统表，意味着存在过多的DDL操作引起了资源争用。
在tempdb对象分配造成的不正常增长，也可以监控下面的性能计数器：
1. SQL Server:Access Methods\Workfiles Created /Sec
2. SQL Server:Access Methods\Worktables Created /Sec
3. SQL Server:Access Methods\Mixed Page Allocations /Sec
4. SQL Server:General Statistics\Temp Tables Created /Sec
5. SQL Server:General Statistics\Temp Tables for destruction
解决：
如果tempdb由于过度的DDL操作引起资源争用。你可以检查应用程序和看看是否最小化DDL操作。可以尝试以下建议：
l 从SQLServer2005开始，临时对象在前面所说的情况下被缓存。但是，如果你依然遇到重大的DDL争用。你就需要查找哪些临时对象没有被缓存和为什么会发生这种情况。如果这些对象发生在循环或者存储过程里面，考虑把它们移出存储过程或者循环中。
l 检查执行计划，是否有一些计划创建了大量的临时对象、假脱机、排序或者工作表。对此，你需要把一些临时对象清理掉。比如，在列中创建用于order by的索引可以考虑移除排序。
如果争用是由于SGAM/PFS页引起，可以通过以下方式减缓：
l 增加tempdb数据文件，来平衡磁盘和文件的负载。理想的情况下，应该和CPU个数持平。
使用TF-1118来移除混合区的分配。