DROP FUNCTION dbo.udf_GetTempDBUsagePerSPID
Go

CREATE FUNCTION dbo.udf_GetTempDBUsagePerSPID()
RETURNS @udf_TempDBUsagePerSPID TABLE
   (
       Session_ID              INT,
       Allocated               INT,
       Deallocated             INT,
       Session_Database        SYSNAME,
       Session_HostName        VARCHAR(100),
       Session_ProgramName     VARCHAR(100),
       Session_LoginName       VARCHAR(100),
       Session_Query           VARCHAR(MAX)
   )
AS
/***************************************************************
Purpose:   To retrieve an indication of which process is consuming
           excessive TempDB resource (using counts of the number of
           objects created/destroyed in TempDB)

           The following DMVs are used:
               sys.dm_db_session_space_usage   - to retrieve page count for internally Allocated/Deallocated objects
                                               - these are objects that utilise tempdb
                                               - session is the scope
                                                
               sys.dm_db_task_space_usage      - to retrieve page count for internally Allocated/Deallocated objects
                                               - these are objects that utilise tempdb
                                               - tasks related to sessions is the scope
                                              
               sys.dm_exec_sql_text            - contains a record of every SQL command that has been run.
                                              
               NOTE:  Both these DMVs only record stats for COMPLETED sessions                  
          
          
Author:        Unknown
History:   1 Dec 2010
           28 Jun 2012 - ChillyDBA - Original query just had allocation numbers.
               Added the session information, including currently running query as
               this may help indicate the area of application code responsible for the
               high usage

****************************************************************/

BEGIN

   INSERT @udf_TempDBUsagePerSPID
       (
           Session_ID,
           Allocated,
           Deallocated,
           Session_Database,
           Session_HostName,
           Session_ProgramName,
           Session_LoginName,
           Session_Query
       )
      
   SELECT  
       t1.session_id                                               AS Session_ID,
       (t1.internal_objects_alloc_page_count + t2.task_alloc)        AS Allocated,
       (t1.internal_objects_dealloc_page_count + t2.task_dealloc)    AS Deallocated,
      
       DB_NAME(t3.dbid)                                            AS Session_Database,
       t3.hostname                                                 AS Session_HostName,
       t3.program_name                                             AS Session_ProgramName,
       t3.loginame                                                 AS Session_LoginName,
       CONVERT(NVARCHAR(MAX), q.text)                               AS Session_Query      
   FROM sys.dm_db_session_space_usage AS t1  
   INNER JOIN
       (
           SELECT
               session_id,  
               SUM(internal_objects_alloc_page_count)      AS task_alloc,
               SUM (internal_objects_dealloc_page_count)    AS task_dealloc  
           FROM sys.dm_db_task_space_usage
           GROUP BY session_id
       ) AS t2
       ON t1.session_id = t2.session_id
   INNER JOIN master..sysprocesses t3
       ON t1.session_id = t3.SPID
   CROSS APPLY sys.dm_exec_sql_text(t3.sql_handle) AS q
   WHERE t1.session_id >50
   ORDER BY Allocated DESC
  
   RETURN
END