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
|
