How to diagnose a deadlock and/or frequent time out issue

Question

How to diagnose a deadlock and/or frequent time out issue

Shane N

Ten Centuries

Points: 1323
More actions
July 8, 2009 at 12:48 am

#134159

So we've started having this issue where our logging DB is starting to time out when data is getting inserted into it....
and occasionally it has deadlock issues
The code that inserts records into the logging table calls an sproc which has the following execution
-selects from a table that has 3 rows (do nothing with the results)
-inserts into logging table
that's all it does
we have no code in our entire system that accesses/selects/updates/deletes data from that logging table. The ONLY access to this table is through this stored procedure from one method that always does that exact thing...
It seems like with something that simple there shouldn't be deadlocks right?
I deleted a non clustered index on there cause I saw something about that possibly helping 🙂 so we'll see if that works.
ideas?
Is there a way to get a view of everything a process is causing to LOCK?
I used the profiler and checked the Lock:Acquired and Lock:release events but I can make no sense of how to use the data in there.. I had it retrieve the object ids but I have no idea how to use those 🙂
So my second question 🙂
How do I get insight into what an sproc is locking?
It'd be nice to run an sproc and get some sort of idea of all the objects that it's locking...
Thank you in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ramuvanparti SSCrazy Points: 2368 More actions · Answer 1

hi,

in google i have found one sp named sp_blocker when executed on the desired server will show all dead lock chains wait type with their respective spids and also the actual command being issued by these spid which are blocking and which are beinging blocked

u have to run the sp_blocker when ever you observe a issue

Shane N Ten Centuries Points: 1323 More actions · Answer 2

yea the problem is that I don't know how to catch it when it is an issue really

I just check the logs and I see exceptions in there

So I'll need to have it email me as an alert so I can try to trap it

but it'd be nice to just figure it out while I'm sitting here 🙂

ramuvanparti SSCrazy Points: 2368 More actions · Answer 3

http://support.microsoft.com/kb/271509

go through the url it will help u.

keep a eye on cpu and memory utilisation also

GURSETHI SSCommitted Points: 1912 More actions · Answer 4

Hi,

KB article provided by my colleague is good one and apart from that in order to understand Blocking i will suggest you to collect Profiler with below counters.

Locks:

Deadlock Graph

Lock:Deadlock

Lock:Deadlock Chain

Performance:

Showplan All

Showplan Statistics

Stored Procedure:

RPC:Started

RPC:Completed

SP:Starting

SP:Completing

SP:StmtStarted

SP:StmtCompleted

TSQL:

SQL:StmtStarted

SQL:StmtCompleted

SQL:BatchStarting

SQL:BatchCOmpleted

This I am suggesting so that we can capture complete Deadlock graph along with queries and then understand who is blocking whome and what sort of lock types are.

Below is a good link in order to understand Deadlock Graph:

Analysing Deadlocks with SQL Server Profiler

http://msdn.microsoft.com/en-us/library/ms188246.aspx

Detecting and Ending DeadLocks

http://msdn.microsoft.com/en-us/library/ms178104.aspx

Let me know if you need more inputs.

Regards

GURSETHI