DMV Queries - Get Indication of TempDB Usage

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        1 Dec 2010

Description

This function retrieves a count of the number of (internal) objects allocated and deallocated in TempDB per session.
This provides an indication of which sessions are excessively using TempDB.
Also includes are some session attributes (Database Name, Host Name, Login Name and SQL Query) which will help in the tracing of which application/code is the most needy.
 
NOTE:  The counters per session are reset to zero when the session commences.  A session may have many tasks.  The task counter component to the results are only updated as each task completes.
 

Code

Function:

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  

 
 
 
Test Code:

SELECT * FROM dbo.udf_GetTempDBUsagePerSPID()


ċ
udf_GetTempDBUsagePerSPID.sql
(3k)
Andy Hughes,
Jun 28, 2012, 11:53 AM
Comments