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
|