在SQL2005/2008多了sys.***相關的VIEW，DBA利用這些VIEW可以獲得很多寶貴的資訊，同時編寫管理面的script，而微軟也相當貼心的提供下載。
--查詢使用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