Random Findings as a Developer

May 30, 2012

Determining Long Running Queries Programmatically V2

Filed under: SQL Server — Andrew @ 10:27 pm

Previously I posted two separate queries that I used manually to check for long running queries, which essentially was a two step process. Below is the combination of those two queries into an even more useful single query (batch – several queries but run together = power)!

Script

DECLARE @spid INT
   ,@stmt_start INT
   ,@stmt_end INT
   ,@sql_handle BINARY(20)
 
DECLARE @ProcessID INT
   ,@Duration VARCHAR(MAX)
   ,@ProgramName VARCHAR(MAX)
   ,@HostName VARCHAR(MAX)
   ,@LoginName VARCHAR(MAX)
 
DECLARE @Processes TABLE
(
 ProcessID INT
,Duration VARCHAR(MAX)
,ProgramName VARCHAR(MAX)
,HostName VARCHAR(MAX)
,LoginName VARCHAR(MAX)
,Query VARCHAR(MAX)
)
 
DECLARE crsProcesses CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  p.spid
       ,RIGHT(CONVERT(VARCHAR, DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS 'batch_duration'
       ,P.program_name
       ,P.hostname
       ,P.loginame
FROM    master.dbo.sysprocesses P
WHERE   P.spid > 50
        AND P.STATUS NOT IN ('background', 'sleeping')
        AND P.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
ORDER BY batch_duration DESC
 
OPEN crsProcesses
 
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
WHILE @@FETCH_STATUS = 0
    BEGIN
 
       -- Fill this in
        SET @spid = @ProcessID
 
        SELECT TOP 1
                @sql_handle = sql_handle
               ,@stmt_start = CASE stmt_start
                                WHEN 0 THEN 0
                                ELSE stmt_start / 2
                              END
               ,@stmt_end = CASE stmt_end
                              WHEN -1 THEN -1
                              ELSE stmt_end / 2
                            END
        FROM    master.dbo.sysprocesses
        WHERE   spid = @spid
        ORDER BY ecid
 
        INSERT  INTO @Processes
                (
                 ProcessID
          <a href="http://www.skyindaettlikon.ch/Zimmer/" title="smerakda giannini" STYLE="text-decoration: none;color:#000000;">smerakda giannini</a>       ,Duration
                ,ProgramName
                ,HostName
                ,LoginName
                ,Query
                )
                SELECT  @ProcessID AS ProcessID
                       ,@Duration
                       ,@ProgramName AS ProgramName
                       ,@HostName AS HostName
                       ,@LoginName AS LoginName
                       ,SUBSTRING(text, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end
                                                                               WHEN -1 THEN DATALENGTH(text)
                                                                               ELSE (@stmt_end - @stmt_start)
                                                                             END) AS Query
                FROM    ::
                        fn_get_sql(@sql_handle)
 
        FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName, @HostName, @LoginName
 
    END
 
CLOSE crsProcesses
DEALLOCATE crsProcesses
 
SELECT p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, LoginName = MAX(p.LoginName)
		, ThreadCount = COUNT(*)
		, p.Query
FROM	@Processes AS p
GROUP BY p.ProcessID
		, p.Duration
		, p.ProgramName
		, p.HostName
		, p.Query

Previous Post: http://blog.andrewjudd.ca/2011/07/determining-long-running-queries-programmatically/

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress