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

Get space used by queries in tempdb Expand / Collapse
Posted Saturday, December 1, 2012 7:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 26, 2015 1:13 PM
Points: 9, Visits: 446
Comments posted to this topic are about the item Get space used by queries in tempdb
Post #1391687
Posted Tuesday, December 4, 2012 6:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 27, 2015 9:31 AM
Points: 59, Visits: 245
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
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
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
(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 <> 50 -- ignore system
and t1.session_id <> @@SPID -- ignore this request itself
AND s1.status = 'running'
order by t1.task_alloc_pages
Post #1392449
Posted Tuesday, May 3, 2016 6:55 AM


Group: General Forum Members
Last Login: Wednesday, June 22, 2016 6:21 AM
Points: 7,841, Visits: 755
Thanks Harsha for the script.
Post #1782862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse