Capture long running query in SQL Server

Capture long running query in SQL Server, finds most resource intensive query/SP and fix it to meet optimal performance is one of the prime responsibility as DBA has to keep up the performance of SQL Server and use the ideal resource available on the server. Database Administrator (DBA) man not be in front of computer 24*7 but it is his duty to capture  all long running processes / query / Stored Procedure to find and fix later on.

There are many way to do this task and out which, I am going to show one of my favorite way to capture long running query.

Note: Before we move further for this article, I would like to update all of you that I have used the view “WhatIsGoingOn” as a base script for this article so if you don’t have that view or you have not read that article, I will highly recommend to read that article, click here to read it.

Once you read the article I recommended in above paragraph, let us continue with this article.

Create table “LongRunningCapture” in your database along with two indexes (one clustered index and one non clustered index). “LongRunningCapture” is going to be huge table once you collect your long running data for few weeks or months so these indexes will help you to select data faster from your table “LongRunningCapture”. Data growth will be depending on how many time you will run the stored procedure we will create and how many long running queries you get based on the criteria you select.

Anyway, have a look at the table along with all its fields and index to create it in your server.

CREATE TABLE LongRunningCapture
(
ID INT IDENTITY(1,1)
,ObjectName VARCHAR(100)
,statement_text  VARCHAR(MAX)
,DatabaseName  VARCHAR(50)
,CPU_Time  INT
,RunningMinutes  INT
,Percent_Complete  INT
,RunningFrom  VARCHAR(100)
,RunningBy  VARCHAR(250)
,SessionID  INT
,BlockingWith  INT
,reads  INT
,writes  INT
,[program_name]  VARCHAR(250)
,login_name  VARCHAR(50)
,status  VARCHAR(25)
,last_request_start_time  DATETIME
,logical_reads  BIGINT
)
GO

CREATE CLUSTERED INDEX IDX_LongRunningCapture_ID ON LongRunningCapture(ID)
GO

CREATE NONCLUSTERED INDEX IDX_LongRunningCapture_last_request_start_time ON LongRunningCapture(last_request_start_time)
GO

Once you will done with creating the table “LongRunningCapture” and index, make sure you have “WhatIsGoingOn” view available. If you don’t have it, create it with following script.

CREATE VIEW WhatIsGoingOn
AS
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads

FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText

Once you have “LongRunningCapture” table and “WhatIsGoingOn” view ready with you, please create stored procedure given in following TSQL snippet.

CREATE PROC SPGet_LongRunning
AS
IF OBJECT_ID('tempdb..#CaptureLongProcessWithObjectName') IS NOT NULL
DROP TABLE #CaptureLongProcessWithObjectName

--Capturing All long running operations in Temp table
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
INTO
#CaptureLongProcessWithObjectName
FROM
WhatIsGoingOn
WHERE
--want to capture each query/SP which is taking more than 1 min.
--you can increase limit as per your need
RunningMinutes>1

--Please note that full/differential/log backup may take more time
--if you wish, you can exclude it by filtering either by ObjectName
--or by Statement_Text as per your need

--separating record in two temp table
--based on with and without ObjectName
IF OBJECT_ID('tempdb..#CaptureLongProcessWithoutObjectName') IS NOT NULL
DROP TABLE #CaptureLongProcessWithoutObjectName

SELECT
*
INTO
#CaptureLongProcessWithoutObjectName
FROM
#CaptureLongProcessWithObjectName
WHERE
ObjectName IS NULL

DELETE FROM
#CaptureLongProcessWithObjectName
WHERE
ObjectName IS NULL

ALTER TABLE #CaptureLongProcessWithObjectName
ADD AutoID INT IDENTITY(1,1)

ALTER TABLE #CaptureLongProcessWithoutObjectName
ADD AutoID INT IDENTITY(1,1)

DECLARE @Count INT,@CountMax INT
SET @Count=1

SELECT @CountMax=COUNT(*) FROM #CaptureLongProcessWithoutObjectName

--if there is no ObjectName came by default
--generating ObjectName,
--generally it happens in Ad-Hoc query of in some system processes
WHILE @Count <= @CountMax
BEGIN
DECLARE @Session INT
SELECT
@Session=SessionID
FROM
#CaptureLongProcessWithoutObjectName
WHERE AutoID=@Count

DECLARE
@InputBuffer TABLE
(
EventType NVARCHAR(MAX),
Parameters NVARCHAR(MAX),
EventInfo NVARCHAR(MAX)
)
DECLARE @Command NVARCHAR(MAX),@ObjectName VARCHAR(MAX)

SELECT  @Command = 'DBCC INPUTBUFFER(' + CAST(@Session AS VARCHAR) + ') WITH NO_INFOMSGS'

INSERT INTO @InputBuffer
EXEC (@Command)

SELECT
@ObjectName = LEFT(REPLACE(REPLACE(REPLACE(EventInfo, CHAR(13), ''), CHAR(10),''), '  ', ' '),50) + '.....'
FROM @InputBuffer

UPDATE
#CaptureLongProcessWithoutObjectName
SET
ObjectName=@ObjectName
WHERE
AutoID=@Count

SELECT @Count=@Count+1
END

--inserting all long running query/sp into table
INSERT INTO LongRunningCapture (
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
)
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
FROM
#CaptureLongProcessWithObjectName
UNION ALL
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
FROM
#CaptureLongProcessWithoutObjectName
GO 

Now you are almost ready to start capturing your long running queries / stored procedure / processes. You are just one step behind; let us complete that last step.

You have to schedule above given SP “SPGet_LongRunning” in your SQL Server Job or Windows Schedule tasks with help of SSIS package to run it at every 2 minutes to capture any process / job / stored procedure / query which are running for more than 1 minute.

You increase your limit of capture from stored procedure “SPGet_LongRunning” as well as in Job. I generally prefer to run it at every 2 minute but in this case you will have too much data to handle in “LongRunningCapture” is you have slow performing server. You have to make policy to clean up old data in “LongRunningCapture” table.

You might delete two-week old data from “LongRunningCapture” table.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.