Category Archives: Uncategorized

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.

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.

Book Review – SQL Server Interview Questions and Answers

Well, I am not as qualified in SQL Server as the authors of this book (Pinal Dave & Vinod Kumar) though I dare to write my review for his book as both of the authors are in my favourite list and I don’t even miss any article written by them then how could I miss a book written by them? After reading the book, I really tempted to share my personal opinion with my blog reader.
“SQL Server Interview Questions and Answer” makes first impression that this book is written to prepare you for the interview of DBA or SQL Developer or BI professional but NO, this book is not ONLY for those who are preparing for an interview, even seasoned developer or DBA could refer this book to master the basics which we may avoid or forget over the time but knowing those may create a firm ground for the project we all are working on.
Both of these authors are well known to present hard & difficult concept in very simple yet powerful manner which directly execute INSERT command in your memory without any trigger or exception, just like strait thrown dart pinch in board, this really show very hard work of both authors.
I really impressed with the book for following points.
  • “Points to Ponder” section at the end of each chapter as a Quick references to Joes 2 Pros books (I had privilege to read few of them)
  • Very inspiring quote at the begging of chapters (I enjoy it in Vinod Kumar’s twits too, in twitter)
  • Links to SQLAuthorty’s articles
When I first heard about this book, I though how much more content could be there in this book? As Pinal has already written the series articles on this topic but with my surprise, there are LOT MORE to learn in book so even if you have read all articles on this series in Pinal’s blog, don’t hesitate to have this book, you will have so many (80%) new stuffs to look at.
This book scores 10/10 and I personally highly recommend this book as a good & quick reference to any professional who are dealing with SQL Server at any level.
To order the copy of the book for your own, visit SQLAuthority.com
Happy Reading!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Last chance to win free Apple iPad this year

SQL Server MVP/Auther/Trainer Jacob Sebastian has launched another quiz “SQL Server DBA Quiz 2011” which is the last quiz of this year. So don’t miss this chance and answer the quiz question asked by Quiz master.
My question is already published regarding Dead Lock, Live Lock and Blocking. If you have not participated yet, still, you have chance to win as all questions are still open. 
If you are not seasoned DBA, still you can refer all questions and answer given by expert to enhance your knowledge.
If you are experienced DBA and you know the answer, you can answer the question, share you knowledge with community and have chance to win iPad. So what are you waiting for? Try to answer as more question as possible.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Get your server hardware information via DM_OS_SYS_INFO in SQL Server 2005/2008/Denali

DMV(Dynamic Management View) is very powerful tool given my Microsoft in SQL Server 2005+ version; you can get much crucial information from it. Today I am going to show you “sys.dm_os_sys_info” which will reveal some of the hardware information of the server which you are using for your SQL Server instance. 
SELECT
      cpu_count asTotal_Logical_CPU_In_Server
      ,HyperThread_Ratio
      ,cpu_count/HyperThread_Ratio asphysical_CPU_In_Server
      –converting bytes of physical memory to MB
      ,(physical_memory_in_bytes/1024)/1024 as Total_Memory_Of_Server_In_MB
      ,(Virtual_memory_in_bytes/1024)/1024 as Current_Usage_Of_Memory_SQLServer_In_MB
      –following fields will work only in SQL Server 2008+
      –if you are using SQL Server 2005, comment following field.
      ,sqlserver_start_time
     
FROM
      sys.dm_os_sys_info
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Take mirror backup of database in SQL Server 2005/2008/Denali

Backup is one of the most important tasks of DBA. Without having proper backup, you are running in danger mode always so it is good to keep more than backup file with you always so in any emergency if one backup gets corrupt or missing, you can use other one.
In early days of SQL Server, especially before SQL Server 2005, DBA used to take full backup of database and copy it somewhere else via DOS command XCOPY to make another copy of the same to other disk drive or somewhere in network but SQL Server 2005+ provides the facility to take backup with mirror copy so you don’t need to put additional efforts to make copy of your database backup and move it somewhere else.
Suppose my database name “SQLHub” so I would use following command to take copy of that database in D drive and mirror copy in E drive.
–database back up of SQLHub
–full back in D drive SQLHubBackup folder
–same backup copy will be copied at E drive SQLHubBackup folder
BACKUP DATABASE SQLHub
      TO DISK= ‘D:\SQLHubBackup\SQLHub.bak’
MIRROR
      TO DISK= ‘E:\SQLHubBackup\SQLHub.bak’
WITH FORMAT
GO
“With Format” option in backup is not mandatory. If we take backup of database with backup file name SQLHub.bak on 8th July 2011, when we again run the same database backup with same backup file name “SQLHub.bak” on 9th July 2011, SQL Server keeps previous backup of database in SQLHub.bak file and copy the next so your both backup taken on 8thand 9th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option. 
If you want to take backup on 9th July 2011 and want to remove database back taken on 8th July 2011, which is there with the same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SQL Server Quiz 2010, one of the biggest online events of the year:

One of the SQL Server GURU and one of my favorite MVP Jacob Sebastian has teamed up with 30 well known SQL Server MVP/Author/Experts to organize one of the biggest online event of the year 2010 “SQL Server Quiz 2010”. I am so much excited to participate in this quiz competition, one can enhance their knowledge of SQL Server at the same time share their knowledge with community in these challenges.
This event will be going to start from 1st October 2010 to 31st October 2010. Each day one SQL Server quiz master will ask one question and will moderate the discussion and answer of that question. Finally Quiz Master will rate your answer between rank 1 and 10. Score of all 31 questions will be summed up to identify the winner of the competition after 31st October 2010. 
This event is not only for DBAs or SQL Developer, I have already told my .Net developers to register there to sharpen their skills in SQL Server.
You don’t have only change to share/enhance your knowledge but have chance to win some of exciting prices like Apple iPad,  Amazon Kindle, Apple iPod Nanos and free license of some cool SQL Server tools .
So what are you waiting for??? 
just grab the opportunity. If you are still not registered member of http://beyondrelational.com . Register now!!!. 

Along with Jacob, here are the names of our favorite SQL Server personality who are going to contribute in the Quiz:
Erland Sommerskarg, Itzik Ben-Gan, Andy Leonard, Louis Davidson, Arnie Rowland, Pinal Dave, Madhu Nair, Peter Larsson, Brad Schulz, Deepak Rangarajan, Greg Low, Rob Farley, Sankar Reddy, Alejandro Mesa, Glenn Berry, Jessica Moss, Madhivanan, David Barbarin, Aurelian Verla, Dinesh Asanka, Adam Haines, Jonathan Keyhayias, Vidya Sagar, Jacob Sebastian, Satya Jayanti, Michael Coles, Sugesh Kumar, Erik Jensen, Hilary Cotter, Allen White, and Paul Nielsen.

Hope to see all of you there in Quiz. Just two days remain to start event. BTW, Common Wealth Games is also about to start in India so It would be interesting for me to see which event (CWG or SQL Server Quiz 2010) is going to get more response :)

And last but not least, here are few important links related to SQL Server Quiz 2010:

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah