Nic Neufeld
October 17, 2016 9:32 am
That’s awesome! I’ve used physloc for other stuff but never to flesh out a deadlock to that level of granularity. Have never interpreted those key locks, now I’m going to have to try that sometime…
You could also look in your maintenance history (if you log index maintenance, like Ola’s solution does) to check for a potential reorg or rebuild between the deadlock and when you are actually troubleshooting…you could end up landing on a page in the wrong object if that index got rebuilt in the intervening time, or an unallocated page. Deadlocks are best troubleshooted (troubleshot?) when fresh…
Loading...
Reply
Nirav Gajjar
October 24, 2016 3:05 am
Super cool!
Thanks Kendra
Loading...
Reply
manishkumar1980
November 7, 2016 9:59 am
Thanks Kendra.
There’s also Object:dbid:lock partitionid wait resource.
Please also describe that.
Loading...
Reply
Kendra Little
November 7, 2016 2:07 pm
Ah! I didn’t think of this one because it’s a bit more rare– it will only happen if you’ve changed lock escalation to ‘auto’ to allow a row or page lock to escalate to a partition lock (instead of a table lock).
Loading...
Reply
Sujai Karnam
August 9, 2019 1:01 am
From my tests, this waitresource = “OBJECT: dbid:object_id:resource_lock_partition id” can occur even when the lock escalation is set to ‘TABLE’ and the blocking-process acquires lock on “multiple lock partitioning”.
Loading...
Reply
JRStern
January 3, 2017 2:11 pm
Any idea why I’d get
Msg 468, Level 16, State 9, Line 190
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in the equal to operation.
when trying the %%lockres%% thing on SQL2016, my local database has the SQL_Latin1 collation.
When I try the same code and technique on an Azure database, it works as advertised.
Thanks,
Loading...
Reply
JRStern
January 3, 2017 2:39 pm
Yup, that worked, thanks!
SELECT
%%lockres%% as lockres, *
FROM dbo.MyTable
WHERE %%lockres%% COLLATE DATABASE_DEFAULT in
(
‘(7f64538e4796)’,
‘(603892c641ac)’,
‘(90f8380a3262)’,
‘(b1c6f78ff5f5)’,
‘(adb51b894a10)’,
‘(64805b87cc18)’,
‘(bef3cb421500)’,
‘(d2b8d18a95c8)’,
‘(cecb3d8c2a2d)’,
‘(4a8ba8cfeb7a)’,
‘(21a1a1106529)’,
‘(d5d9c29d9b53)’,
‘(0faa5258424b)’,
‘(0b129b19cfff)’,
‘(fbd231d5bc31)’
);
Loading...
Reply
Collecting the Blocked Process Report (XEvents and Server Side Trace) - by Kendra Little
January 16, 2017 11:23 am
[…] you’re looking at individual reports, my post “Decoding Key and Page WaitResource for Deadlocks and Blocking” will help you dig into the nitty gritty […]
Loading...
Reply
Darko Martinovic
February 27, 2017 1:22 am
In my opinion, the resource name is very important information when analyzing blocking/locking problems. As well as resource content.
Usually, I’d like to see this information as a part of custom blocked(deadlock) process report
That’s the reason, why I developed two scripts, using t-sql & sqlclr
Loading...
Reply
Marcelo Moraes
July 20, 2017 10:04 am
Amazing!
Loading...
Reply
Martin Guth
August 4, 2017 12:54 am
Hi Kendra,
I am puzzled that I am not able to decode a key lock wait. I could gess the table it has been on by looking at the sqltext. This table had some indexes rebuilded since the blocking occurred. Is that the reason why the hobt id is not there any more?
Thanks in advance
Martin
Loading...
Reply
soumyas
September 6, 2017 4:08 am
Very good article on clearly understanding where exactly the deadlock occured in a volatile OLTP environment/
One of my deadlock reports shows it in the waitresource=“KEY: … format and I see that the only the magic hash value is different between the victim and the winner. Now the object itself(using the hobt_id) is a non clustered index on the table and when I use the %%lockres%% function with and without the index(non clustered hint), I get no rows. Why do you think this is happening ?
Also, this index itself has columns in this order – TestNonClustIx – (colA, colB, colC)
Now both the winner and victim sessions have same values of colA and colB. They differ only with colC. Do you think I can avoid the deadlock if I change the index order to TestNonClustIx – (colC, colA, colB)
Thanks!
Loading...
Reply
Kendra Little
August 20, 2018 12:37 pm
It could be that the exact rows are not there anymore in the index.
If you change the index order as described, it sounds like possibly the one of the queries might not use the new index at all– because it doesn’t care about ColC, which is now the leading column. While that might avoid the deadlock, it might also slow that query down a lot, which could cause a different problem. So I’d be hesitant of that change without lots more testing.
Loading...
Reply
Kenneth Igiri
September 23, 2018 11:22 pm
Nnaa Mhen!!! Some people are so hot with SQL Server
Loading...
Reply
Troubleshooting Deadlocks with RID as the wait resource – SQLJosh
October 23, 2018 4:42 am
[…] resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no […]
Loading...
Reply
Beautiful Deadlock Graphs And Tying RIDs Back To Object Names – Curated SQL
October 24, 2018 5:04 am
[…] wait resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no […]
Loading...
Reply
dd
December 19, 2018 2:07 am
I have a blocked process report with waitresources which included waitresource=Page:…, waitresource=Object:… etc. but also a waitresource which isn’t prefixed by a word e.g. waitresource=”9:1:123456789″ – If I treat it as a Page wait – I get a valid object – so are “no-prefix waits” the same as Page waits or is this a coincidence?
Loading...
Reply
John L
May 8, 2019 7:48 am
Late to this party and this is a really esoteric question..I am in the Azure SQL Database world, looking at deadlock detail presented via Azure SQL Analytics. When I see Wait resource: KEY: 12:72057594113490944 (9755ee930a2a) and this were SQL Server I would expect 12 to reference the database_id in sys.databases. In Azure SQL Database that is not the case. What am I missing?
Loading...
Reply
Kendra Little
May 8, 2019 7:53 am
Does it actually matter? In this case you know which database it is, so the slot where the DB ID would be seems ignorable to me.
Loading...
Reply
Karl
September 24, 2019 5:52 am
Thank you so much for this post, this really helped me when trying to identify which table was locked (for “KEY”) after exporting my blocked-process trace to a CSV.
However when trying to identify the table and the object is “PAGE” and I run;
1.3) Get the name of the object from DBCC PAGE
That is not possible when running AWS RDS MS-SQL, is there another option?;
User ‘masterRDSlogin’ does not have permission to run DBCC TRACEON.
User ‘masterRDSlogin’ does not have permission to run DBCC PAGE.
Also I saw that you mentioned it in your reply to another comment but can you possibly consider updating this post on how to get the table when the object is of the type “OBJECT” as opposed to “KEY” or “PAGE”?
comment history migrated from wordpressNic Neufeld October 17, 2016 9:32 am That’s awesome! I’ve used physloc for other stuff but never to flesh out a deadlock to that level of granularity. Have never interpreted those key locks, now I’m going to have to try that sometime…
You could also look in your maintenance history (if you log index maintenance, like Ola’s solution does) to check for a potential reorg or rebuild between the deadlock and when you are actually troubleshooting…you could end up landing on a page in the wrong object if that index got rebuilt in the intervening time, or an unallocated page. Deadlocks are best troubleshooted (troubleshot?) when fresh…
Loading...
Reply Nirav Gajjar October 24, 2016 3:05 am Super cool! Thanks Kendra
Loading...
Reply manishkumar1980 November 7, 2016 9:59 am Thanks Kendra.
There’s also Object:dbid:lock partitionid wait resource.
Please also describe that.
Loading...
Reply Kendra Little November 7, 2016 2:07 pm Ah! I didn’t think of this one because it’s a bit more rare– it will only happen if you’ve changed lock escalation to ‘auto’ to allow a row or page lock to escalate to a partition lock (instead of a table lock).
Paul Randal has a great post here which covers how this can happen in detail, I think it may be easiest to link to it: http://www.sqlskills.com/blogs/paul/sql-server-2008-partition-level-lock-escalation-details-and-examples/
Loading... Reply Sujai Karnam August 9, 2019 1:01 am From my tests, this waitresource = “OBJECT: dbid:object_id:resource_lock_partition id” can occur even when the lock escalation is set to ‘TABLE’ and the blocking-process acquires lock on “multiple lock partitioning”.
Here is an article I came across to better understand the resource lock partition id in the waitresource = “OBJECT: dbid:object_id:resource_lock_partition id” https://www.microsoftpressstore.com/articles/article.aspx?p=2233327&seqNum=5
Loading... Reply JRStern January 3, 2017 2:11 pm Any idea why I’d get Msg 468, Level 16, State 9, Line 190 Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in the equal to operation. when trying the %%lockres%% thing on SQL2016, my local database has the SQL_Latin1 collation.
When I try the same code and technique on an Azure database, it works as advertised. Thanks,
Loading... Reply Kendra Little January 3, 2017 2:23 pm That error usually means that tempdb has a different collation than your user database, and it’s using tempdb behind the scenes. Sometimes you can work around it with a hint, like they mention in this stack overflow answer (the one with the most votes) – http://stackoverflow.com/questions/21332105/temp-table-collation-conflict-error-cannot-resolve-the-collation-conflict-be
Loading... Reply JRStern January 3, 2017 2:39 pm Yup, that worked, thanks!
SELECT %%lockres%% as lockres, * FROM dbo.MyTable WHERE %%lockres%% COLLATE DATABASE_DEFAULT in ( ‘(7f64538e4796)’, ‘(603892c641ac)’, ‘(90f8380a3262)’, ‘(b1c6f78ff5f5)’, ‘(adb51b894a10)’, ‘(64805b87cc18)’, ‘(bef3cb421500)’, ‘(d2b8d18a95c8)’, ‘(cecb3d8c2a2d)’, ‘(4a8ba8cfeb7a)’, ‘(21a1a1106529)’, ‘(d5d9c29d9b53)’, ‘(0faa5258424b)’, ‘(0b129b19cfff)’, ‘(fbd231d5bc31)’ );
Loading... Reply Collecting the Blocked Process Report (XEvents and Server Side Trace) - by Kendra Little January 16, 2017 11:23 am […] you’re looking at individual reports, my post “Decoding Key and Page WaitResource for Deadlocks and Blocking” will help you dig into the nitty gritty […]
Loading... Reply Darko Martinovic February 27, 2017 1:22 am In my opinion, the resource name is very important information when analyzing blocking/locking problems. As well as resource content.
Usually, I’d like to see this information as a part of custom blocked(deadlock) process report
That’s the reason, why I developed two scripts, using t-sql & sqlclr
I published both script on
http://www.sqlservercentral.com/scripts/T-SQL/153452/
http://www.sqlservercentral.com/scripts/T-SQL/153467/
Loading... Reply Marcelo Moraes July 20, 2017 10:04 am Amazing!
Loading... Reply Martin Guth August 4, 2017 12:54 am Hi Kendra,
I am puzzled that I am not able to decode a key lock wait. I could gess the table it has been on by looking at the sqltext. This table had some indexes rebuilded since the blocking occurred. Is that the reason why the hobt id is not there any more?
Thanks in advance
Martin
Loading... Reply soumyas September 6, 2017 4:08 am Very good article on clearly understanding where exactly the deadlock occured in a volatile OLTP environment/
Loading... Reply How to identify the waitresource during blocking and deadlocks – Database knowledge (MS SQL Server…) September 7, 2017 1:10 am […] https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/ […]
Loading... Reply INDRAJEET TRIGUNAYAT March 14, 2018 11:42 pm Good Post 🙂
Loading... Reply that_me August 17, 2018 1:19 pm Hey Kendra –
One of my deadlock reports shows it in the waitresource=“KEY: … format and I see that the only the magic hash value is different between the victim and the winner. Now the object itself(using the hobt_id) is a non clustered index on the table and when I use the %%lockres%% function with and without the index(non clustered hint), I get no rows. Why do you think this is happening ?
Also, this index itself has columns in this order – TestNonClustIx – (colA, colB, colC)
Now both the winner and victim sessions have same values of colA and colB. They differ only with colC. Do you think I can avoid the deadlock if I change the index order to TestNonClustIx – (colC, colA, colB)
Thanks!
Loading... Reply Kendra Little August 20, 2018 12:37 pm It could be that the exact rows are not there anymore in the index.
If you change the index order as described, it sounds like possibly the one of the queries might not use the new index at all– because it doesn’t care about ColC, which is now the leading column. While that might avoid the deadlock, it might also slow that query down a lot, which could cause a different problem. So I’d be hesitant of that change without lots more testing.
Loading... Reply Kenneth Igiri September 23, 2018 11:22 pm Nnaa Mhen!!! Some people are so hot with SQL Server
Loading... Reply Troubleshooting Deadlocks with RID as the wait resource – SQLJosh October 23, 2018 4:42 am […] resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no […]
Loading... Reply Beautiful Deadlock Graphs And Tying RIDs Back To Object Names – Curated SQL October 24, 2018 5:04 am […] wait resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no […]
Loading... Reply dd December 19, 2018 2:07 am I have a blocked process report with waitresources which included waitresource=Page:…, waitresource=Object:… etc. but also a waitresource which isn’t prefixed by a word e.g. waitresource=”9:1:123456789″ – If I treat it as a Page wait – I get a valid object – so are “no-prefix waits” the same as Page waits or is this a coincidence?
Loading... Reply John L May 8, 2019 7:48 am Late to this party and this is a really esoteric question..I am in the Azure SQL Database world, looking at deadlock detail presented via Azure SQL Analytics. When I see Wait resource: KEY: 12:72057594113490944 (9755ee930a2a) and this were SQL Server I would expect 12 to reference the database_id in sys.databases. In Azure SQL Database that is not the case. What am I missing?
Loading... Reply Kendra Little May 8, 2019 7:53 am Does it actually matter? In this case you know which database it is, so the slot where the DB ID would be seems ignorable to me.
Loading... Reply Karl September 24, 2019 5:52 am Thank you so much for this post, this really helped me when trying to identify which table was locked (for “KEY”) after exporting my blocked-process trace to a CSV.
However when trying to identify the table and the object is “PAGE” and I run; 1.3) Get the name of the object from DBCC PAGE
That is not possible when running AWS RDS MS-SQL, is there another option?; User ‘masterRDSlogin’ does not have permission to run DBCC TRACEON. User ‘masterRDSlogin’ does not have permission to run DBCC PAGE.
Also I saw that you mentioned it in your reply to another comment but can you possibly consider updating this post on how to get the table when the object is of the type “OBJECT” as opposed to “KEY” or “PAGE”?
Thanks again – Karl