Find Blocking in SQL Server and use it to send an alert

Find Blocking in SQL Server and use it to send an alert:

As a DBA, I am always interested to find whether blocking is going on, in my environment or not? How long blocking exists? Which are the queries/SPs creating blocking etc.

Blocking generally occurs when one SQL Server connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

Sometime It may happens that blocking gets converted into deadlock situation where neither of both connection able to finish its job and one of the connection identified as deadlock victim by SQL Server. In short, Locking, Blocking and deadlocking is not good to keep. This is the reason I always eager to know the details about blocking happening in my system.

Few days back, I have created one SQL Server View “WhatIsGoingOn” which was using sys.dm_exec_requests and sys.dm_exec_sessions DMVs to find information about currently running processes in SQL Server instance along with sessionID and blocking Session ID, if any. I am going to use “WhatIsGoingOn” SQL Server View in one SP to find only blocked session and insert its detail into following SQL Server table.

[sourcecode language=”sql”]
CREATE TABLE tblBlockingDetails

(

ID INT IDENTITY(1,1),

ObjectName SYSNAME NULL,

BlockingObjectName SYSNAME NULL,

StatementText VARCHAR(MAX),

BlockingStatementText VARCHAR(MAX),

SessionID INT,

BlockingSessionID INT,

Status VARCHAR(50),

BlockingStatus VARCHAR(50),

DateCreated DATETIME

)[/sourcecode]

After having above table in my database, I will create following Stored Procedure which will capture details of any session being locked and insert that detail in “tblBlockingDetails” table.

[sourcecode language=”sql”]
CREATE PROC uspInsertBlockingRecords

AS

INSERT INTO tblBlockingDetails

SELECT

W1.ObjectName AS ObjectName,

W2.ObjectName AS BlockingObjectName,

W1.Statement_Text AS StatementText,

W2.Statement_Text AS BlockingStatementText,

W1.sessionid AS SessionID,

W2.sessionid AS BlockingSessionID,

W1.status AS Status,

W2.status AS BlockingStatus,

GETDATE() AS DateCreated

FROM

WhatIsGoingOn W1 INNER JOIN WhatIsGoingOn W2

ON

W1.BlockingWith > 0

AND W1.BlockingWith = W2.sessionid

[/sourcecode]

Now, I can schedule SP “uspInsertBlockingRecords” every few minutes for business hours in SQL Server Job Agent so that I can see details of blocking anytime I wish from table “tblBlockingDetails”. I can even set email schedule either to get information about blocking, if I wish.

If you want to create DeadLock situation forcefully to check its behavior, you can use the script given in one of my past article here.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

NOLOCK hint in SQL Server, Good guy or bad guy?

Introduction

It is quite often in production environment that multiple application and/or users try to access same resource at the same time which may create locking and blocking issues. It is tricky to deal with this situation as there is no rocket science which suits for all situations. It is all depends on situation because in some production environment, it is not possible to resolve blocking issues easily over the night.
The main root cause for locking is, we have long running transaction which keeps your object locked and meanwhile any request comes to access the same object, has to wait until the current transaction complete its operation. Best choice should be to minimize the transaction length so that it releases the lock quickly and other request doesn’t need wait due to lock but unfortunately it is possible to solve it easily in ALL environments.

Getting ready

I am going to perform this example in my SQL Server 2012 RTM version but it may work as it is in SQL Server 2005 / 2008 too.
Connect to your SQL Server and open three query window. We will call these query windows with following name:
·         Win1
·         Win2
·         Win3

How to do it…

1.       After connecting to SQL Server Management Studio (SSMS), open Win1
2.       Create one sample database, named SQLHub and create one table with sample rows with following T-SQL script:
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLE orders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 100000 sample rows into table
INSERT INTO orders(OrderDate, Amount, Refno)
SELECT TOP 100000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
3.  Execute one UPDATE statement in Win1 with BEGIN TRANSACTION. Note that we don’t have COMMIT or ROLLBACK after the UPDATE Statement at the moment. ROLLBACK is commented so it won’t be executed.
BEGIN TRANSACTION
UPDATE Orders SET Amount=5.00 WHERE OrderID<10
–ROLLBACK
4.  In Win2, try to execute following SELECT statement:
select * from Orders Where orderID<=15
5.       You will observe that SELECT statement wouldn’t return any results so in WIN3, try to execute following T-SQL and know what is going on behind the screen:
sp_who2
go
6.       We will get many rows in the result set from the above command but we have to look at last two rows of SQLHub database as per given in following screen capture:
7.       We can see that out UPDATE statement runs with SPID 54 and SELECT statement runs with SPID 55. SELECT query is blocked by SPID 54 given in row no.2 column no.5 in screen capture. Now I have two ways. Either I issue COMMIT / ROLLBACK command which is not a good idea to interrupt UPDATE or cancel SELECT query in SSMS which I will do now.
8.       Since we have cancelled SELECT query, we will not execute same SELECT statement with NOLOCK hint.
select * from Orders WITH (NOLOCK) Where orderID<=15
9.       Here is the result return by above query.
10.   Go to Win 1 and execute “ROLLBACK” statement.
11.   Go back to Win 2 and execute SELECT statement with or without “NOLOCK”. For eg:
select * from Orders  Where orderID<=15
12.   Here is the screen shot of result return by above query:

How it works…

When we have executed UPDATE statement in Step no 3 without COMMIT or ROLLBACK, It updates the records but didn’t release the lock it has acquired on the table so SELECT query was not able read data and return it.
How do you know that whether UPDATE has really updated the values or not? Since our select query is not giving results. We have executed same select query with “WITH (NOLOCK)” hint in step no. 8 and we can confirm that values are update with the screen capture given in step 9. Isn’t it good situation? Yes, may be as we were not even able to get the result of SELECT statement in step no 4. But I would say we can’t decide whether it is a good or bad without evaluating the business need.
Since UPDATE statement has updated the value but it is not saved so there is a chance to ROLLBACK. If you see resultset given in steps no 9, you will see “5.00” in “Amount” column which may not be a proper value as after ROLLBACK, it comes back to “2.00” again as per step no. 12.
Think if this would be a result of stock exchange, what would happen? Can we run with dirty read we seen in screen shot in step no.9? No certainly not that is why I have conveyed that NOLOCK is all depends on the business need and situation. Use it wisely as it is two sided sword.

See also

NOLOCK as known as READUNCOMMITED concept is somehow related to ISOLATION level.  Here are the links of some of my past article on the subject.
Happy Reading!!!
Reference: Ritesh Shah
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

Deadlocks in SQL Server 2008

There are few different kinds of lock available in SQL Server but the most resource consuming especially CPU power is DeadLock. It is better to know about deadlock and try to reduce as much as possible. First of all let us understand what is deadlock and how deadlock happens?
Deadlock happens when two or more processes waiting for the resource and wait for other process to finish and eventually neither of them ever does it at that time SQL Server select one process and abort other process.
Let us see one small example to create dead lock.
–create one two tables for demo and insert dummy records.
create table DL1
(
id int,
name varchar(10)
)
go
create table DL2
(
id int,
name varchar(10)
)
go
insert into DL1
select 1,‘ritesh’ union all
select 2,‘rajan’
go
insert into DL2
select 1,‘vipul’ union all
select 2,‘darshan’
go
–open other query window, will consider this window as A1
begin tran
update dl1 set name=‘rit’ where id=1
go
–open other query window, will consider this window as A2
begin tran
update dl2 set name=‘vi’ where id=2
go
–again come back to window A1
update dl2 set name=‘d’ where id=2
go
Now this statement will create lock and query got hold but it is not a dead lock so far.
–now comeback to A2 window and execute following command
update dl1 set name=‘raj’ where id=1
go
Now, this statement creates dead lock and you will be greeted with the error message something like below and your
Msg 1205, Level 13, State 45, Line 2
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As soon as you execute COMMIT or ROLLBACK, your lock gets release. So, this is how deadlock generates so while designing the database, keep relatively normalize. Keep your transaction as short as possible and try to use TRY….CATCH block so if there is any error in your TRY block, it transfer the focuses to CATCH block and you can ROLLBACK transaction from there.
BTW, if you want to find tables which are being locked, have a look at my article here.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah