Get Currently Running Processes

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