December 16, 2015 at 4:14 pm
How to find the name of the wait resource when you know id from sys.sysprocesses.
The below is wait resource from sys.sysprocesses but how to find the name of key ? I think this first nine indicates the database no for me
KEY: 9:720575944236072(407acb26904e)
December 16, 2015 at 4:33 pm
Yes, 9 is the database_id. The second number is the partition_id, so if test_db were the database with database_id of 9, you could run this:
USE test_db;
SELECT object_name=o.name,
index_name=i.name
FROM sys.partitions p
INNER JOIN sys.indexes i
ON i.index_id=p.index_id AND i.object_id=p.object_id
INNER JOIN sys.objects o ON o.object_id=p.object_id
WHERE partition_id=720575944236072
Cheers!
December 17, 2015 at 9:41 am
Thank you.
That is one of the index on session lock table in report server temp db.
How can we reduce this waiting several sessions for that particular resource?
December 18, 2015 at 10:02 am
Instead of key if the wait resource is tab, how could you find that resource name?
TAB: 5:836198029:106
what is that 106? I can see there are different no . The db no and the middle no is same but the last no is changing
December 18, 2015 at 12:10 pm
For that, the format is db_id:object_id:lock_partition.
https://technet.microsoft.com/en-us/library/ms187504(v=sql.105).aspx explains lock partitioning.
106 is a very large number for that. Out of curiosity, how many cores do you have on that machine?
Cheers!
December 18, 2015 at 12:38 pm
From this below query there are 200 cpu_ids
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
December 18, 2015 at 12:46 pm
Yeah, since you had a lock partition of 106, that indicated at least 107 (it starts at 0). I was curious how many more there were. That's a very nice machine 🙂
At any rate, you can just use the object_id from that resource description to find the table.
Cheers!
December 18, 2015 at 1:22 pm
Thank you.
One more question what is the number next to the when you get key resource wait instead of tab.
KEY: 9:72057623741923328(8f6267ec32ff)
What is the no in the parenthesis ?
Thanks,
December 18, 2015 at 1:47 pm
That's the hashed key value.
If you are really digging into the locking behavior of your queries, you can use that to find the actual key value locked by using %%lockres%%.
On a heap, that will show a row ID instead of a hashed key value. Be careful when searching for rows with a particular value for %%lockres%%, as that forces a table scan, which could be very expensive on a large table.
Cheers!
December 18, 2015 at 1:57 pm
Thank you Jacob
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply