Tempdb space usage

rx2528

My Tempdb is filling up fast How can I use DPA to identify the spid using most tempdb space?

mandevil

Here is one way:

Step 1: Set up an alert for tempdb filling up.

Grab the script you feel most comfortable with to check tempdb used space (or free space if you like to look at it from that perspective). There are a lot out there and some are % based, some are byte based, so don't want to assume anything here.

If you get an alert from DPA that TempDB is filling past a threshold you define, then move to step 2.

Step 2: Query against the instance alerting the following to get the data you are looking for.

SELECT SU.session_id,
sum (SU.internal_objects_alloc_page_count) as session_internal_alloc,
sum (SU.internal_objects_dealloc_page_count) as sesion_internal_dealloc,
sum (SU.user_objects_alloc_page_count) as session_user_alloc,
sum (SU.user_objects_dealloc_page_count) as sesion_user_dealloc,
sum (TS.internal_objects_alloc_page_count) as task_internal_alloc ,
sum (TS.internal_objects_dealloc_page_count) as task_internal_dealloc,
sum (TS.user_objects_alloc_page_count) as task_user_alloc ,
sum (TS.user_objects_dealloc_page_count) as task_user_dealloc
FROM sys.dm_db_session_space_usage SU
inner join sys.dm_db_task_space_usage TS
on SU.session_id = TS.session_id
where SU.session_id > 50  
GROUP BY SU.session_id;

-- You could also add an order by clause desc so that you rank them by most used to least by spid.