Credits:
Author: Unknown
Date: 1 Aug 2012
Description
Provides a summary of currently running query information.
If a Database Name is supplied then the scope will be restricted to the database, otherwise all
queries on the server will be returned.
Code
Function:
DROP FUNCTION dbo.udf_GetCurrentlyRunningProcesses GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO
CREATE FUNCTION dbo.udf_GetCurrentlyRunningProcesses ( @DatabaseName SYSNAME ) RETURNS @CurrentlyRunningProcesses TABLE ( SPID INT, ECID INT, DatabaseName SYSNAME NULL, UserName VARCHAR(100) NULL, RequestStatus VARCHAR(100) NULL, RequestWaitType VARCHAR(100) NULL, BlockingSPID INT, IndividualQuery NVARCHAR(MAX), ParentQuery NVARCHAR(MAX), ProgramName VARCHAR(100) NULL, HostName VARCHAR(100) NULL, NTDomain VARCHAR(100) NULL, StartTime DATETIME, LastBatch DATETIME, WaitTime INT, CPUTime INT, ElapsedTime INT, Reads INT, Writes INT, LogicalReads INT, [RowCount] INT, GrantedQueryMemoryKB INT ) AS /*************************************************************** Purpose: To retrieve the currently running queries against a database. If no database parameter is provided, then results are for the whole SERVER The following DMVs are used: sys.dm_exec_requests - details of currently executing requests sys.sysprocesses - extended details of all processes sys.dm_exec_sql_text - the query text associated with a SQL_Handle Author: Ian Stirk History: 24 Jul 2008 - Initial Issue 1 Aug 2012 - ChillyDBA - Converted to a table valued function 23 Aug 2012 - ChillyDBA - Augmented with additional resutls (reads. writes, wait time etc)
****************************************************************/
BEGIN
INSERT @CurrentlyRunningProcesses ( SPID, ECID, DatabaseName, UserName, RequestStatus, RequestWaitType, BlockingSPID, IndividualQuery, ParentQuery, ProgramName, HostName, NTDomain, StartTime, LastBatch, WaitTime, CPUTime, ElapsedTime, Reads, Writes, LogicalReads, [RowCount], GrantedQueryMemoryKB ) SELECT er.session_Id AS SPID, sp.ecid AS ECID, --execution context ID (ie sub-thread ID) DB_NAME(sp.dbid) AS DatabaseName, sp.nt_username AS UserName, er.status AS RequestStatus, er.wait_type AS RequestWaitType, sp.blocked AS BlockingSPID,
SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,( ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 ) + 1 ) AS IndividualQuery,
qt.text AS ParentQuery, sp.program_name AS ProgramName, sp.Hostname AS HostName, sp.nt_domain AS NTDomain, er.start_time AS StartTime, sp.last_batch AS LastBatch, er.wait_time AS WaitTime, er.cpu_time AS CPUTime, er.total_elapsed_time AS ElapsedTime, er.reads AS Reads, er.writes AS Writes, er.logical_reads AS LogicalReads, er.row_count AS [RowCount], er.granted_query_memory*8 AS GrantedQueryMemoryKB FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. AND sp.dbid = ISNULL(DB_ID(@DatabaseName), sp.dbid) ORDER BY er.session_Id, sp.ecid RETURN END
|
Test Code:
SELECT * FROM dbo.udf_GetCurrentlyRunningProcesses (NULL)
SELECT * FROM dbo.udf_GetCurrentlyRunningProcesses ('master')
|
 Updating...
Andy Hughes, Aug 23, 2012, 11:31 AM
|