186 reputation
bio website
visits member for 2 years, 10 months
seen Jun 4 '14 at 17:24

comment Deadlocks From Locks on Same Temporary Tables In Different Processes
I can confirm that lock partitioning is involved here. These posts has some detail about analyzing deadlocks involving and due to lock partitions. bit.ly/Ruzmym bit.ly/W7yuRK But I don't know why both the sessions posted the same ObjectID.
comment ASPState database locking and growth problems
This is the latest supported version of the procedure. support.microsoft.com/kb/973849 The latest version is supposed to work on individual rows based on the clustered index key and intended to avoid blocking active sessions. The older version was prone to lock escalation and severe blocking. Are you actually observing blocking on this procedure?
comment Why is this key lookup required?
@brain For lookups (and nested loops) you should compare the Actual Number of rows with (Estimated Number of Rows * Number of Executions). In this case it is 10259536 = (10259536 * 1). So there is no statistics mismatch on this Index. However, the Estimated Number of Executions (derived from the Estimated Number of Rows from the Outer side of the key lookup) 1216 is significantly lower than the actual number of executions, which may indicate a problem with the statistics on the NC Index.
comment SELECT/INSERT Deadlock
The locking behavior of OPTIMIZED Nested Loops is comparable to REPEATABLE READ ( keeps the locks till the end of the statement), but it does not explicity set the isolation level of the transaction to REPEATABLE READ. I think that answers your question one too. It is not that the parallel threads are requesting more than one page at a time, but one parallel thread is holding a lock on one page and another thread is waiting for a lock on another page
comment Unexpected scans during delete operation using WHERE IN
How does this query comapred to the one with hte HASH JOIN hint alone in terms of performance. DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) OPTION (LOOP JOIN, HASH GROUP)