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.
Reference: Ritesh Shah
Sharing IT tips at “Quick Learn“
Note: Microsoft Books online is a default reference of all articles.