Get space used by queries in tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Harsha Majety » Get space used by queries in tempdb

Get space used by queries in tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

harsha.majety

Posted Saturday, December 01, 2012 7:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 6, Visits: 396

Comments posted to this topic are about the item Get space used by queries in tempdb

Post #1391687

james.anderson 82942

Posted Tuesday, December 04, 2012 6:50 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:52 AM
Points: 2, Visits: 55

In case this is helpful to anybody, I have a similar query a to identify processes actively running that take up Tempdb space.

I found this useful as sometimes we would have extremely large stored procedures with lots of nested procedures that were taking up too much tempDb space and this helped identify the specific query text that was the culprit.

USE tempdb
SELECT
query_text=
coalesce((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(t2.sql_handle)) , 'Not currently executing')
, t1.session_id
, t1.request_id
, task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
, task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
, host= case when t1.session_id =50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
,t2.plan_handle
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
(Select session_id, request_id
, task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)
, task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on t1.session_id = t2.session_id
and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
t1.session_id=s1.session_id
where
t1.session_id <> 50 -- ignore system
and t1.session_id <> @@SPID -- ignore this request itself
AND s1.status = 'running'
order by t1.task_alloc_pages

Post #1392449

« Prev Topic | Next Topic »

Permissions