SQL Server2005性能调优（简化翻译版） - 2

转： http://blog.joycode.com/juqiang/archive/2006/12/11/88853.aspx

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就是上述4个东西的和。

   监视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)

转载于:https://www.cnblogs.com/waiter/archive/2006/12/20/598321.html