Database Dynamic management view DMV (4)

-- Query whether one or more page locks are being acquired in tempdb.
Select session_id, wait_duration_ms, resource_description from SYS. dm_ OS _waiting_tasks
Where wait_type like 'page % latch _ % 'and resource_description like '2: %'

-- Query the execution query that triggers a maximum of page allocation and recovery operations in tempdb
Select top 10
T1.session _ id,
T1.request _ id,
T1.task _ alloc,
T1.task _ dealloc,
T2.plan _ handle,
(
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 SQL _text
)
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
Join SYS. dm_exec_requests as T2 on t1.session _ id = t2.session _ id and t1.request _ id = t2.request _ id
Where t1.session _ id> 50
Order by t1.task _ alloc DESC