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