Deadlock Detection using Profiler in SQL Server
2005
The amount of features introduced in SQL Server 2005
are numerous. I think we have taken a tour of some T-SQL enhancements, DTS
a.k.a. SSIS, XML and others before. But in this article we will take a
look at another toolset that is available in SQL Server 2000 world but has been
enhanced in this version, the Profiler. In this article we would look into
an specific area of profiler enhancements, detecting dead locks in SQL
Server 2005.
Setting up the Profiler
Open Profiler from the Programs directory. Select a
New Template. Get to the Events Selection Tab and select the events required
for detecting deadlocks. A typical selection for this article would look like:
In the above diagram, the most important events are
Deadlock Chain, Deadlock Graph and Deadlock. In SQL Server 2000, we used to get
ONLY deadlock event. That used to list the deadlock victim. We had to trace to
which SPID blocked this and had to trace to the statement that blocked the
same. So this was something very difficult. I've also seen people using the
snapshot of the Enterprise Manager to detect locks/deadlocks. I would consider
these are quite in-efficient. Moving forward we will see how this has been
simplified.
Setting up a deadlock
The last thing on earth any programmer would want it
to simulate a deadlock. In reality it is not difficult ... So the simple and
short answer to get a deadlock will be to access the tables data in a reverse
order and hence introducing a cyclic deadlock between two connections. Let me
show you code:
Create table vin_deadlock (id int, Name
Varchar(30))
GO
Insert into vin_deadlock values (1, 'Vinod')
Insert into vin_deadlock values (2, 'Kumar')
Insert into vin_deadlock values (3, 'Saravana')
Insert into vin_deadlock values (4, 'Srinivas')
Insert into vin_deadlock values (5, 'Sampath')
Insert into vin_deadlock values (6, 'Manoj')
GO
Now with the tables ready. Just update the columns in
the reverse order from two connections like:
-- Connection 1
Begin
Tran
Update vin_deadlock
SET Name = 'Manoj'
Where id = 6
WAITFOR DELAY '00:00:10'
Update vin_deadlock
SET Name = 'Vinod'
Where id = 1
and from connection 2
-- Connection 2
Begin
Tran
Update vin_deadlock
SET Name = 'Vinod'
Where id = 1
WAITFOR DELAY '00:00:10'
Update vin_deadlock
SET Name = 'Manoj'
Where id = 6
I know this is a quick and dirty trick to get
deadlocks. But to illustrate the new feature let us run these commands from two
windows. After running the commands, switch to the profiler window and watch
the fun. The profiler window reads as:
The output in order shows, the two statements getting
executed. The point where we have a deadlock chain. Shows we have a ROWID lock
that got escalated. Shows the deadlock victim that was selected using the
Deadlock event. Lastly the very interesting, Deadlock graph. On selecting the
Deadlock Graph we see:
Personally, I consider this as amazing amount of
information that a DBA/Analyst would want to debug the deadlock situation.
Interesting take away would be:
-
Know the SPID's of the deadlock process
-
Object causing the issue. Here it is
DemoDB.dbo.vin_deadlock
-
Type of Lock escalated. Here RID Lock.
-
Locks requested and granted. Like SPID 58, had "X"
lock and requested "U" locks.
This I personally feel is a great set of information.
But having said that we have more to this. The Deadlock Chain can also be saved
as a XML data. Right click on the Deadlock graph and select Extract Event Data.
This will save the same in an XML format.
The XML can be later opened in SQL Management Studio
and we can get the same information that we got from Profiler. But on closer
look you will find that this is an XML file in reality. So open the same in
Notepad or IExplore. And the inside content looks like:
Interestingly, this gives us more information that
expected. And I loved this ... This is a standard XML file and you can write
your own XSL to transform and get more information. The XML contains:
-
The Statements that were on the connection. I.e.
Input Buffer Output.
-
All the information that you would want from
sysprocesses and sp_who/sp_who2 for the given SPID.
-
Transaction Isolation Levels that were used prior to
deadlock and mony more ...
Conclusion
SQL Server 2005 is full of new features that are
yet to be explored. Each and every toolset has been enhanced to amazing extend.
So do spend time with each tools introduced to capitalize its features.
BTW, Profiler has also been enhanced to an anazing extend. We will surely
try to look into more such features in the future too. As I said earlier,
"Yukon Rocks !!!" ...
|