Why sp_BlitzLock Can’t Show Complete Columnstore Deadlock Information

Last Updated 3 years ago

People seem to care about deadlocks

That’s why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock?

Well, I had this song stuck in my head. But enough about that.

While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it.

Of course, that makes things different.

So we go digging

The deadlocks I generated were on a single object. Just a couple inserts and deletes in two separate transactions. Nothing special.

This is the information that I pull out with sp_BlitzLock

But what’s missing from the column store deadlock information is really weird. Namely, everything.

One may be tempted to try to use the associated object id to get the object name, and one would be left wanting.

SELECT OBJECT_NAME(72057594059948032)

Msg 8115, Level 16, State 2, Line 12
Arithmetic overflow error converting expression to data type int.

1

2

3

4

SELECT OBJECT_NAME(72057594059948032)

Msg 8115, Level 16, State 2, Line 12

Arithmetic overflow error converting expression to data type int.

It’s a bug in Microsoft SQL Server.

I am currently unable to give you as much information about column store deadlocks as I’d like to.

Unless Microsoft responds to my ~~Connect~~ Feedback Item.

Brent says: this is such a great example of why niche features run into interoperability issues. They work just fine by themselves, but they’re not tested in combination with other things – even when those things might be mainstream, like deadlock graphs. I bet this bug gets fixed quickly though – with columnstore indexes gaining popularity, more folks will be running into this issue fast.

Update June 2021: Microsoft still hasn’t responded to the bug report.

6 Comments. Leave new

Michael J Swart
April 6, 2018 8:52 am

“objectids” that are 17 digits long and start with 720575940 look like partition ids.
This might give you something:

Transact-SQL

select object_name(object_id) from sys.dm_db_partition_stats where partition_id = 72057594059948032

1
2
3

select object_name(object_id)
from sys.dm_db_partition_stats
where partition_id = 72057594059948032

Reply
- Erik Darling
  April 6, 2018 9:17 am
  
  Hi Michael!
  
  Yeah, I wanna say you can find them under allocation_units too, but that makes my life hard with more dynamic SQL. Information in the deadlock XML should be standardized and documented.
  
  Thanks!
  
  Reply
  - Michael J Swart
    April 6, 2018 10:47 am
    
    For sure, It should totally be standardized and documented. (I’ve already upvoted)
    As a blitz_lock author, you definitely need this fixed.
    I wrote the comment I guess for the onetime deadlock inspector who needs a workaround.
    
    Reply
    - Erik Darling
      April 7, 2018 9:15 pm
      
      Ah, cool, thanks. Hopefully it does get worked out.
      
      Reply
David
April 7, 2018 8:32 am

I’ve up voted this because we’re using column store quite heavily and it would be nice to see it have feature parity with row store tables for things like this.

Reply
- Erik Darling
  April 7, 2018 9:15 pm
  
  Thanks David!
  
  Reply