My Tempdb is filling up fast How can I use DPA to identify the spid using most tempdb space?
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.