Get Indication of TempDB Usage

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

SELECT * FROM dbo.udf_GetTempDBUsagePerSPID()