SQL deadlock victim

Question

SQL deadlock victim

WhiteLotus

SSChampion

Points: 12120
More actions
October 6, 2015 at 6:46 pm

#321171

Hi All ,
I have a deadlock in my system and am suspicious about index defragmentation that I run at 8 PM . How do we make SQL server always choose index defragmentation as a victim ?
Many thanks on your feedback !
Cheers

Viewing 13 posts - 1 through 12 (of 12 total)

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

You can use the SET DEADLOCK_PRIORITY to make it the lowest priority. That should result in it always being the victim.

"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

WhiteLotus SSChampion Points: 12120 More actions · Answer 2

OK COOL . Thanks for that

Btw how do I catch the deadlock process so I will know which queries that are involved at specific time?

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

Turn traceflag 1222 on. That will result in a deadlock graph being written to the error log.

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

WhiteLotus SSChampion Points: 12120 More actions · Answer 4

Thanks so much Gail !!

I just enable it using DBCC TRACEON(1222,-1)

and will see how it goes

Can I turn it on forever ? just in case one day I need to check the deadlock again

cheers

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

You're already capturing deadlock information in the system_health extended event session. You don't have to clutter up your error log with deadlock graphs through the traceflag if you don't want to. The only issue with this is that the output is in XML. However, here's a query[/url] to make that simple too.

"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 6

Grant Fritchey (10/7/2015)
You're already capturing deadlock information in the system_health extended event session.

On 2008?

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

GilaMonster (10/7/2015)
Grant Fritchey (10/7/2015)
You're already capturing deadlock information in the system_health extended event session.
On 2008?

Yep.

No gui, but it works.

"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

WhiteLotus SSChampion Points: 12120 More actions · Answer 8

HI guys ,

I got something from Error Log but I couldn’t read the deadlock culprit ( The result as attached )

I assume the yellow highlighted is the Victim and the Red one is the culprit ?? But the information that I can read is the object name , No query there

Still confused with it

Anyone knows how to read it ?Please kindly help

Many thanks

WhiteLotus SSChampion Points: 12120 More actions · Answer 9

Hi Guys ,

I just run the script from Extended Event and here what I get ( but I still don’t know what is the cause of this deadlock ) – The query that I highlighted in Yellow is the deadlock Victim

Can you please kindly help how to read this ?

</event>

<victim-list>

</victim-list>

<process-list>

</frame>

</frame>

</executionStack>

(@p0 int,@p1 bigint)DELETE FROM ManifestExportRecord WHERE Id = @p0 AND VersionNumber = @p1 </inputbuf>

</process>

</frame>

</frame>

</executionStack>

(@p0 int,@p1 bigint)DELETE FROM ManifestExportRecord WHERE Id = @p0 AND VersionNumber = @p1 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</event>

<victim-list>

</victim-list>

<process-list>

</frame>

</frame>

</executionStack>

(@p0 int,@p1 bigint)DELETE FROM ManifestExportRecord WHERE Id = @p0 AND VersionNumber = @p1 </inputbuf>

</process>

</frame>

</frame>

</executionStack>

(@p0 int,@p1 bigint)DELETE FROM ManifestExportRecord WHERE Id = @p0 AND VersionNumber = @p1 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

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

WhiteLotus (10/7/2015)
I assume the yellow highlighted is the Victim and the Red one is the culprit ?? But the information that I can read is the object name , No query there

No. The yellow is one of the queries (not the victim process) and the red is one of the wait resources. The two queries involved in the deadlock are the same, both sessions are running that delete. Since there's a user transaction, there was probably a statement run before the delete. You'll need to dig into the .net code and identify it

This may help you read the XE dedlock graph: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 11

They're both running this query:

(@p0 int,@p1 bigint)DELETE FROM ManifestExportRecord WHERE Id = @p0 AND VersionNumber = @p1

Deadlocks are usually multi-statement issues involving access across multiple resources, not a single statement. As Gail says, something else is involved in there.

"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

WhiteLotus SSChampion Points: 12120 More actions · Answer 12

Thanks so much Gail

That’s a very good Information for me! I will work it out with my team