RiCo技術農場

在SQL2005/2008多了sys.***相關的VIEW，DBA利用這些VIEW可以獲得很多寶貴的資訊(在也不用亂猜了)，

小弟最後在微軟網站找到相關文件(Oracle就沒這麼貼心><)，分享連結給需要的朋友。

 

--查詢使用tempdb空間最大的10個session
select top 10
t1.session_id,
t1.request_id,
t1.task_alloc,
    t1.task_dealloc, 
    (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                  ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +  user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_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) and
      t1.session_id > 50
order by t1.task_alloc DESC   

 

--找出lock的來源

SELECT
  t1.request_session_id AS spid,
  t1.resource_type AS type,
  t1.resource_database_id AS dbid,
  (case resource_type
   WHEN 'OBJECT' THEN object_name(t1.resource_associated_entity_id)
   WHEN 'DATABASE' THEN ' '
   ELSE (SELECT object_name(object_id)
      FROM sys.partitions
      WHERE hobt_id=resource_associated_entity_id)
  END) AS objname,
  t1.resource_description AS description,
  t1.request_mode AS mode,
  t1.request_status AS status,
   t2.blocking_session_id
FROM sys.dm_tran_locks AS t1 LEFT OUTER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address
GO

 

下載SQL2005 system view

下載SQL2008 system view