DEADLOCK PRIORITY issue

Question

DEADLOCK PRIORITY issue

mbhandari

Mr or Mrs. 500

Points: 528
More actions
February 22, 2015 at 8:08 am

#294801

I have a DEADLOCK PRIORITY hint set to 10 with one of of index maintenance job since I don't want this job to fail because of any deadlock issue. But recently its being selected as a victim against a process whose deadlock priority is not specifically set (default 0). Could anyone please provide some insight to it? Thank you!

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

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Do you happen to have the deadlock XML to post?

Based solely on the information you've provided, my best guess is that the processes aren't running with the priorities you think they are.

Without the XML I can't say for certain, though.

mbhandari Mr or Mrs. 500 Points: 528 More actions · Answer 2

Hi Jacob, here is the xml from deadlock graph. Thank you.

<deadlock-list>

<deadlock victim="process7bd708">

<process-list>

<executionStack>

<frame procname="adhoc" line="1" sqlhandle="0x0100060067117e0250abf42d020000000000000000000000">

ALTER INDEX MY_INDEX_NAME ON MY_TABLE_NAME REBUILD WITH (FILLFACTOR = 85, STATISTICS_NORECOMPUTE = OFF) </frame>

<frame procname="MY_PROCEDURE_NAME" line="93" stmtstart="6104" stmtend="6132" sqlhandle="0x03000600d05de177218c990041a400000100000000000000">

EXEC (@cmd) </frame>

<frame procname="adhoc" line="1" sqlhandle="0x010006004e27130830fdf6d9010000000000000000000000">

Exec MY_PROCEDURE_NAME </frame>

</executionStack>

<inputbuf>

Exec MY_PROCEDURE_NAME </inputbuf>

</process>

<executionStack>

<frame procname="MY_PROCEDURE_NAME" line="22" stmtstart="864" stmtend="1590" sqlhandle="0x03000600bdf2a8608a127c0078a300000100000000000000">

IF

(

SELECTCOUNT(*)

FROMMY_CODE

) > 0 </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 6 Object Id = 1621684925] </inputbuf>

</process>

</process-list>

<resource-list>

<objectlock lockPartition="17" objid="385436447" subresource="FULL" dbid="6" objectname="MY_TABLE_NAME" id="lock326f9e480" mode="Sch-S" associatedObjectId="385436447">

<owner-list>

<owner id="process5992e08" mode="Sch-S"/>

</owner-list>

<waiter-list>

<waiter id="process7bd708" mode="Sch-M" requestType="wait"/>

</waiter-list>

</objectlock>

<objectlock lockPartition="13" objid="385436447" subresource="FULL" dbid="6" objectname="MYOBJECT" id="lock1f6362180" mode="Sch-M" associatedObjectId="385436447">

<owner-list>

<owner id="process7bd708" mode="Sch-M"/>

</owner-list>

<waiter-list>

<waiter id="process5992e08" mode="Sch-S" requestType="wait"/>

</waiter-list>

</objectlock>

</resource-list>

</deadlock>

</deadlock-list>

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Thanks for that!

That is indeed interesting, as the priorities are exactly as you claimed, with the surviving spid at 0, and the victim at 10.

I have seen occasional indications that a higher priority does not guarantee surviving a deadlock, but I have generally assumed that was limited to situations in which there were more than 2 processes involved and killing a lower priority process wouldn't completely resolve the deadlock.

I do see that lock partitioning is involved (so you have a nice 16+ core machine!), and that can lead to some unusual deadlock scenarios (http://blogs.msdn.com/b/psssql/archive/2012/08/31/strange-sch-s-sch-m-deadlock-on-machines-with-16-or-more-schedulers.aspx, for example), and there have been a few bugs related to deadlocks with lock partitioning as well (http://support.microsoft.com/kb/2776344).

Unfortunately, I don't have a definitive answer, although I would wager that lock partitioning is involved in the unexpected behavior. What version of SQL Server are you on?

ScottPletcher SSC Guru Points: 100947 More actions · Answer 4

All true. However, you can improve the coding of the second query:

Instead of:

IF

(

SELECTCOUNT(*)

FROMMY_CODE

) > 0 </frame>

Use:

IF EXISTS(SELECT TOP (1) 1 FROM MY_CODE)

You're having to scan the table to fully count, which is greatly contributing the deadlock, when all you really need to do is check for one row, i.e., the count is "> 0".

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

That is an excellent point, Scott.

I'd written off commenting on the code when it seemed like it had been pretty heavily altered for presentation, but that was still worth mentioning.

I also think I've run tests on that sort of code in the past, and the optimizer ended up using the same plan for both the IF (SELECT COUNT(*)...) >0 and the IF EXISTS..., with similar performance, at least in SQL Server 2012 on the data I tested against. I should probably run those tests again, just to be sure my memory's not playing tricks on me.

Either way, it's definitely best not to rely on the optimizer to fix inefficient queries.

Thanks for covering that!

mbhandari Mr or Mrs. 500 Points: 528 More actions · Answer 6

Thanks for your invaluable comments. I looked at the execution plans for IF( SELECT COUNT(*)... ) and IF EXISTS options and its giving me identical plan. But I am going to implement your suggestions regardless.

Jacob, to answer your question, we have 2008 R2 version of sql server.

On further digging, I found that this is a small index with the page_count of only 3. As per the recommendations in other blogs, I decided to filter out any indexes less than 1000 page_count. I also noticed that even after index rebuild, fragmentation percentage of the index remained same. so it might be because of indexe being tiny. I am wondering if SQL Server somehow figured that out and chose this process as a deadlock victim, even if the DEADLOCK PRIORITY was set to 10.

Thank you!

ScottPletcher SSC Guru Points: 100947 More actions · Answer 7

mbhandari (2/25/2015)Thanks for your invaluable comments. I looked at the execution plans for IF( SELECT COUNT(*)... ) and IF EXISTS options and its giving me identical plan. But I am going to implement your suggestions regardless.

That doesn't make sense. The IF EXISTS() plan should have a TOP process that is not in the other plan. The plans may look similar, but they should never be "identical".

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

You would think so, but it didn't when I ran the tests many moons ago. What the OP is seeing is the same thing I remember seeing from my tests.

The optimizer wasn't smart enough in 2000 to convert them to the same plan, but starting in 2005 it should (http://blogs.technet.com/b/wardpond/archive/2007/08/27/if-exists-select-vs-if-select-count-1-0.aspx, for example).

If I get some time later today I'll put together some scripts for sample data, queries, and post the execution plans.

@mbhandari: That's a good find. Definitely no reason to reindex a 3 page index 🙂

Cheers!

ScottPletcher SSC Guru Points: 100947 More actions · Answer 9

That's interesting. I didn't realize SQL would convert that. Not sure exactly the optimizer knows when it's "safe" to do that.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

mbhandari Mr or Mrs. 500 Points: 528 More actions · Answer 10

Thanks Jacob and Scott. For now my issue is solved as this index will be filtered out from my maintenance plan (its the only one I have having issue with right now). I am not sure if we can assume that SQL instance chose the SPID in question as victim, because it would not do much (reduce fragmentation) and was cheaper for the instance to kill it. Doesn't sound very right to me though!!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

@mbhandari: Glad we could help!

Life got pesky, so it took me longer than I wanted to post the comparison of the IF EXISTS and IF COUNT(*)>0 queries, but here it is.

--Create our wonderful test table

CREATE TABLE ExistsVSCount (

SomeNumber int

)

GO

--Populate our test table with 100,000 instances of each of the integers from 1 to 10

WITH N (SomeNumber) AS (

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5

UNION ALL

SELECT 6

UNION ALL

SELECT 7

UNION ALL

SELECT 8

UNION ALL

SELECT 9

UNION ALL

SELECT 10

)

INSERT INTO ExistsVSCount (SomeNumber)

SELECT N1.SomeNumber

FROM N AS N1

CROSS JOIN N AS N2

CROSS JOIN N AS N3

CROSS JOIN N AS N4

CROSS JOIN N AS N5

CROSS JOIN N AS N6

--Let's check the number of pages in our newly populated heap. For me it says we have 3,345 data pages.

--We'll remember that for later.

SELECT au.data_pages FROM sys.tables AS t

INNER JOIN sys.indexes AS i ON t.object_id=i.object_id

INNER JOIN sys.partitions AS p ON i.object_id=p.object_id

INNER JOIN sys.allocation_units AS au ON au.container_id=p.partition_id

WHERE t.name='ExistsVSCount'

--Make sure we see the glorious IO stats

SET STATISTICS IO ON

--Check for the existence of values larger than 8 using IF EXISTS and SELECT TOP

IF EXISTS

(SELECT TOP (1) SomeNumber FROM ExistsVSCount WHERE SomeNumber>8)

SELECT 'There''s something larger than eight!'

--IO stats showed the following:

--Table 'ExistsVSCount'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Check for the existence of values larger than 8 using IF SELECT COUNT(*)...>0

--A terrible idea, because we'll just scan the whole table, right?

IF (SELECT COUNT(*) FROM ExistsVSCount WHERE SomeNumber>8)>0

SELECT 'There''s something larger than eight!'

--IO Stats for this one:

--Table 'ExistsVSCount'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Ok, so those are exactly the same, reading less than 10% of the table.

--The exact number of logical reads for these two will vary a bit if the script is run multiple times, but this run seemed representative.

--Looks like the optimizer was clever enough, as expected.

--Let's now get some confirmation that this is an optimization specifically for IF (SELECT COUNT(*)...>0, by switching that to >1

IF (SELECT COUNT(*) FROM ExistsVSCount WHERE SomeNumber>8)>1

SELECT 'There''s something larger than eight!'

--IO Stats for this one:

--Table 'ExistsVSCount'. Scan count 1, logical reads 3345, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Ah, now we're seeing what we originally thought would happen, and reading all 3,345 data pages in the heap.

--Clean up after ourselves

SET STATISTICS IO OFF

DROP TABLE ExistsVSCount

In short, the optimizer does indeed run them in exactly the same way.

The actual execution plans for each are attached.

The specific example above was run on 2008, but I tested and saw the same behavior on 2005, 2008 R2, 2012, and 2014 as well.

I'd still prefer the IF EXISTS, just because it's actually expressing the more efficient logic, while the COUNT(*)>0 is relying on a trick from the optimizer, but it does seem that they run in exactly the same way.

Cheers!