Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

TEMPDB Usage Expand / Collapse
Posted Tuesday, October 16, 2012 6:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 13, 2016 1:22 AM
Points: 1,416, Visits: 2,931
Hi All

I'm trying to test the affect on tempdb of a query with a large sort operation.

I'm using the below scripts to check tempdb

--script 1
select top 10
(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


--script 2
select ES.session_id
, ES.login_time
, DB_NAME(SU.database_id)
, ES.host_name
, user_objects_alloc_page_count
, user_objects_dealloc_page_count
, internal_objects_alloc_page_count
, internal_objects_alloc_page_count
from sys.dm_db_session_space_usage SU
inner join sys.dm_exec_sessions ES
on SU.session_id = ES.session_id
where DB_NAME(database_id) = 'tempdb'
and ES.is_user_process <> 0

The problem is that for the first script, I'm seeing significant amounts in the t1.task_alloc column but in the second script, is shows zero for internal_objects_alloc_page_count
and user_objects_alloc_page_count for the same session_id

Am I missing something here?

Post #1373198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse