SQL Server2005性能调优（简化翻译版） - 2
TempDB
每个实例只有一个tempdb，所以这里很可能成为性能或者磁盘空间的瓶颈。
常见的tempdb问题如下：
· 把磁盘空间用光了
· 因为tempdb的瓶颈，导致I/O很差。参见第一部分。
· DDL带来的对系统表的瓶颈
· 内容分配
诊断问题之前，先看看tempdb是如何利用空间的。
用户对象
· 表和索引
· 全局临时表 (##t1)和索引
· 局部临时表和索引(#t1) and index.
· 当前连接的
· 存储过程内的
· 表变量（同上）
内部对象
· Work file (hash join)
· Sort run
· Work table (cursor, spool和临时大对象)
版本存储
２００５新增的
空闲空间
tempdb暂时没有用到的磁盘剩余空间.
监视tempdb剩余空间很简单，监测这个指标即可。Free Space in tempdb (KB)。下面这个DMVs很强大的说，上面四个都能看到。
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_objets_kb internal_objects_kb version_store_kb freespace_kb
---------------- -------------------- ------------------ ------------
8736 128 64 448
分析空间使用问题
用户对象
跑这个，能看出来到底谁干的。
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
版本存储
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
这里显示一个带有XSN3的事务（ID是8609），已经激活了6523秒。
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783
Kill掉第一个trans，我们可以释放iding数量的version store。但是，没有办法能够估计出来，kill掉之后，究竟能释放多少。
内部对象
SQL Server 2005提供了两个DMVs: sys.dm_db_session_space_usage和 asys.dm_db_task_space_usage，用以跟踪sessions和tasks在tempdb中的空间变化。
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
对于tasks，可以执行下面的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
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
----------------------------------------------------------------------- 0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
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)
