Capture long running query in SQL Server

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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

[sourcecode language=”sql”]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 [/sourcecode]

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.

Monitoring SQL Azure Database & Server

Monitoring SQL Azure Database & Server

By far I have written quite a few articles to monitor and manage SQL Server (find it here under “DBA Related Articles” section) but what about SQL Azure?

Note: if you are new to Cloud and SQL Azure, do look at “Microsoft Windows / SQL Azure:” section to get understanding from scratch.

Microsoft has very good concept of system views to provide the very important and crucial information to the admin. SQL Azure is not an exception. Herewith, I am going to explain some of common and very powerful system views which cater you with so many important information you required to accomplish your admin task in SQL Azure.

sys.dm_exec_connections : This view will return a row per each connection made toward SQL Azure database. Some of the important fields are session_id, connect_time, num_read, num_write, last_read, last_write and many more.

sys.dm_exec_sessions: This DMV will return all the active connection no matter whether it is user connection or system’s connection for internal task. Important fields from this DMV are session_id, login_time,  host_name, program_name, login_name

sys.dm_exec_requests: This system view will cater your need to know each request comes to your database. Some of the important fields are session_id, start_time, status, command, database_id, user_id, blocking_session_id, wait_type, percent_complete.

sys.dm_tran_database_transactions: This DMV will return database level transaction; here is the list of important columns from this DMV: transaction_id, database_id, database_transaction_begin_time, database_transaction_type, database_transaction_state.

sys.dm_db_partition_stats: This DMV returns page & row-count information for each partition in current database. Some of the important fields from this DMV are partition_id, object_id, used_page_count, reserve_page_count, row_count to name a few.

I will have detail follow up article which will return meaningful information by joining few of the DMVs together till then have fun !!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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

use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance

Many time we need to find who are logged in to SQL Server instance with which tool/program/software. SQL Server gives this very useful information to us with following two DMVs.

sys.dm_exec_requests
sys.dm_exec_sessions

I have written VIEW in my SQL Server Instance to capture all these necessary information.

here is the TSQL script for the same:

[sourcecode language=”sql”]
CREATE VIEW WhoisLoggedIn
AS
SELECT
ISNULL(sess.host_name, ”) as host_name,
ISNULL(DB_NAME(req.database_id) , ”)  as database_Name,
ISNULL(sess.program_name, ”)   as ProgramName,
ISNULL(sess.login_name, ”)  as login_name,
ISNULL(CAST(req.start_time AS VARCHAR), ”)  as start_time,
DATEDIFF(MINUTE,ISNULL(req.start_time,GETDATE()),GETDATE())  as RunningMinutes,
req.Session_id

FROM
sys.dm_exec_requests req
INNER JOIN
sys.dm_exec_sessions sess
ON
req.session_id=sess.session_id
WHERE
–will eliminate all system processes
req.session_id >= 50
AND
sess.host_name IS NOT NULL
[/sourcecode]

Once you create this VIEW in your SQL Server, you don’t need to remember complex query but to use only user friendly name “WhoisLoggedIn” with standard SELECT statement like this:

[sourcecode language=”sql”]
SELECT * FROM WhoisLoggedIn
[/sourcecode]

Note: I have written one blog post in past which was finding currently running processes in SQL Server with the help of above given two DMVs.  click here to read it.

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions

As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:

sys.dm_exec_requests
sys.dm_exec_sessions

I have created one SQL Server view with the help of following script which I used to use, often, during business hours to check whether everything is going smooth or not.

 

[sourcecode language=”sql”]
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
[/sourcecode]

Now, whenever I want to see the status whether anything is blocking to my query/queries or which query/SP is being executed from which time. How many reads/writes happening by each objects. if there is a task like backup/restore, I can even see, how many percent complete by just executing one simple SELECT statement:

 

[sourcecode language=”sql”]
SELECT * FROM WhatIsGoingOn
[/sourcecode]

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations