How to find the values that are passed to the parameters of a stored procedure which participates in deadlock

Question

Post reply

How to find the values that are passed to the parameters of a stored procedure which participates in deadlock

vamshikrishnaeee

SSCrazy

Points: 2519
More actions
August 5, 2010 at 4:22 am

#142334

HI,
On our servers deadlocks are appearing frequently. We deployed notification services to monitor which queries or stored procedures that are participating in deadlocks.By this I am able to get the stored procedure which is participating in the deadlock.But the storedprocedure in its text is showing parameter values like @p,@r. But I need the values which are passed to stored procedure(values passed to parameters p,r) at the time of deadlock. Is there any way to find out these values.
Please help me on this ....
Thanks,
Vamsy

Viewing 9 posts - 1 through 8 (of 8 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may find this article useful in resolving deadlocks.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Adiga (8/5/2010)
You may find this article useful in resolving deadlocks.

That article is for SQL 2000. There are better ways to get deadlock information on SQL 2005 and above. Traceflag 1222 gives far more information than 1204 did and is far easier to read.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 3

Pradeep,

Thanks for your reply.

I need the values that are passed to the parameters of stored procedure at the time of deadlock.

I read many articles on the deadlock but I haven't found how to find the values.

Take a scenario like let say a storedprocedure 'test_sp' have two parameters @p and @r.

Some one passed two values to 'test_sp' and a deadlock had occured (this sp may be victim or may not).By notification services I can get the stored procedures and adhoc queries which participates in the deadlock But I need to know the values passed by him at the time of deadlock.

Thanks,

Vamsy

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Depending on what was deadlocked and how it resolves, you may not get this information. Usually, when we have a system that is experiencing deadlocks, we put a server-side trace on it so that we can get the successful procedure call and it's parameters, for sure. It won't get you everything you want, but it's better than nothing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 5

Thanks for your reply Grant...

We are experiencing deadlocks frequently.

We have

1.Read committed isolation level

2.Indexes are placed on all required columns

3.Index Rebuilds are running as maintenance tasks.

Please suggest me how to minimise the deadlocks. I have been following major of your's suggestions mentioned in the sqlserver query performance tuning distilled'.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

First suggestion, follow Gail's advice. Enable traceflag 1222. It will give you a lot of useful information. If you're experiencing lots of deadlocks, usually, it's your TSQL code. It could be any number of things, you're locking tables in different orders within different queries, you've got very long running transactions that hit a lot of tables, all kinds of stuff could be causing it. Like Gail said, get the traceflag enabled and see what you can gather.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Grant Fritchey (8/6/2010)
If you're experiencing lots of deadlocks, usually, it's your TSQL code.

Or your indexes. Or both.

If what you mean is single column indexes on each column, that's far from optimal in most cases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 8

vamshikrishnaeee (8/6/2010)
We have
1.Read committed isolation level
2.Indexes are placed on all required columns
3.Index Rebuilds are running as maintenance tasks.

You're not running the rebuilds during the day or while other tasks are trying to access those tables, are you?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.