DMV Queries - Get Currently Running Processes

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

ċ
udf_GetCurrentlyRunningProcesses.sql
(4k)
Andy Hughes,
Aug 23, 2012, 11:31 AM
Comments