October 18, 2012 at 8:11 am
Hi All
I have the following query to check which currently running statements are knocking tempdb
The query works great in showing which statements are using internal tempdb allocations(sorts, joins etc).
The problem is that it doesn't show any user tempdb allocations (temp tables), why is this?
I've created a temp table, started an insert loop of 5000000 rows and the script doesn't pick it up
Script:
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
Thanks
October 18, 2012 at 11:18 pm
Try with dm_db_Session_space_usage.
http://msdn.microsoft.com/en-us/library/ms187938.aspx
"Page counters are initialized to zero (0) at the start of a session. The counters track the total number of pages that have been allocated or deallocated for tasks that are already completed in the session. The counters are updated only when a task ends; they do not reflect running tasks."
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply