SQL Server 2005 Waiting and Blocking Issues. Beyond the common tools covered previously, much of the details of determining the cause of blocking and resolving it are specific to each wait type.
Next, we’ll cover some of the more common wait types. Latches Latches are short-term synchronization objects. Although originally used mainly for synchronization of physical access to database pages, their use in SQL Server as a general synchronization primitive has become widespread. SQL Server 2005 has more than 120 distinct usages of latches. Latch waits are divided into two main groups: page latch waits and nonpage latch waits. In addition to these groups, different wait types exist for the latch mode being requested. Blocking Information Blocking task information for latch waits is provided under certain circumstances. Undocumented Virtual Column: %%lockres% One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don’t ask).
I’d never tried doing that before. Obviously if you hit the DMV sys.dm_os_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented “virtual” column called %%lockres%%. In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own.
First, a simple query: SELECTa.City –,%%lockres%% FROM Person.Address AS a WHERE a.AddressID = 432 If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If we want to see %%lockres%% in action, it’s not too difficult: Performance and Wait Types. Hi Hmmm waittypes and queue's very important to understand and if you do then it is a great performance tuning factor ..
Looking at your list of waittypes it is relatively unclear what is happening...to look at waittype issues we need to see more info ..but what I have seens is LCK_M_S 0x03 LCK_M_U 0x04 LCK_M_X 0x05 Lck_MS_S is a shared lock Lck_M_U is a update lockLck_M_X is a exclusive lock You will need to look at the waittimes for these waittypes and waitresorces to point out problems ....looking at the other waittypes it seems that an update statement is causing your issue what I suggest you do is use PSSDIAG and enable the blocker stuff , download a tool by the name of sherlock and analyse it .. For sql 2005 you can enable the blocker process in sp_configuresp_configure 'show advanced options', 1 ;GORECONFIGURE ;GOsp_configure 'blocked process threshold', 20 ;GORECONFIGURE ;GOset the threshold to 5 seconds ...will give you enough info.
%%lockres%% collision probability magic marker: 16,777,215. May 29th, 2009 @jrowlandjones blogged about a dubious deadlock case.
I recommend this article as is correct and presents a somewhat esoteric case of deadlock: the resource hash collision. The lock manager in SQL Server doesn’t know what it locks, it just locks ‘resources’ (basically strings). It is the job of higher level components like the the access methods of the storage engine to present the ‘resource’ to the lock manager and ask for the desired lock. When locking rows in a heap or a b-tree the storage engine will synthesize a ‘resource’ from the record identifier. But if you have a table with a key of length 50 bytes and its reduced to 6 bytes, you may hit a collision. On 6 bytes there are 281,474,976,710,656 distinct possible values. What if I change my question to ‘I bet there are two people in this room that share the birthday!’? Claypole's World - The SQL Server Side : The Curious Case of the Dubious Deadlock and the Not So Logical Lock. One of the many reasons I love consulting is that you get the opportunity to work in diverse businesses, review and inspect various implementations and experience unique challenges that come with each client visit.
My latest engagement has involved working with one of the largest Teradata installations in the world, which acts as the primary data source to our application. To give you an idea of the scale; this system has over 40 Billion rows in just one of the fact tables… 40. Billion. That's a lot of rows. During this engagement we encountered a rather bizarre issue that doesn't seem to be well understood so I thought I'd blog about it.
We had been pulling data from this monster into our System. Before proceeding it's probably worth sharing a bit about the implementation (without actually divulging any industry details of course) so that you can understand the context of these deadlocks and why they were so strange. The Table The Data Data Loaded by Parameters 2009-05-19 & CountryID 2.