1 Reply Latest reply on Jun 6, 2018 11:33 AM by mandevil

    Tempdb space usage

    rx2528

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

        • Re: Tempdb space usage
          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.