SQL Server 2005性能排错（4）

核心提示： SELECTt1.session_id,(t1.internal_objects_alloc_page_count + task_alloc) as allocated,(t1.internal_objects_dealloc_page_count + task_dealloc) asde

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

一旦你隔离出生成大量对象分配的任务或会话，你能找到任务的那条Transact-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

这是示例的输出。