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()