Technical Article

Find Currently Running Query – T-SQL

,

Following script retrieves the queries currently executing on the server.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- What SQL Statements Are Currently Running?
SELECT
        [Spid] = [er]. [session_Id] ,
        [sp] .[Ecid] ,
        [Database] = DB_NAME ([sp]. [dbid]) ,
        [User] = [sp]. [nt_username] ,
        [Status] = [er]. [status] ,
        [Wait] = [er]. [wait_type] ,
        [er] .[Start_time] ,
        [er] .[Cpu_time] ,
        [er] .[Total_elapsed_time],
        [Individual Query] = SUBSTRING ([qt]. [text], [er].[statement_start_offset]/ 2,
                                       ( 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) ,
        [Parent Query] = [qt]. [text] ,
        Program = [sp]. [program_name] ,
        [er] .[Command] ,
        [sp] .[Hostname] ,
        [sp] .[Nt_domain]      
     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
        [er] .[session_Id]> 50              -- Ignore system spids.
        AND [er]. [session_Id] NOT IN ( @@SPID )      -- Ignore this current statement.
    ORDER BY [er].[session_Id] ,[sp]. [ecid]

Rate

2.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

2.8 (5)

You rated this post out of 5. Change rating