dalewilbanks.net

SQLServer

Every day is a good day to learn something new about Sql Server.   Some things I picked up in the last few days:

  • As we know, deadlocks are damn annoying, and a reality of life. We had a frequent deadlock in the WINS load testing, and the cause ended up being an index we added from the MS recommendations.  It did improve performance, but it wasn’t worth the drastic increase in deadlocks.  The sql itself was optimized as much as sql server would allow, given the following…
  • @TableVars are actual #temp tables under the covers, not in memory tables.  So now we know why they are much slower than what they should be.  They can’t be used to speed up a typical single SELECT statement with sub-Selects.
  • CTEs can’t improve SELECT performance, because they are treated as virtual views by sql server when creating the execution plan. They do make for some clean code however when you have a complex query.

But the Gem, is this info on non-clustered indexes causing deadlocks.

http://support.microsoft.com/kb/169960

Example 6: Nonclustered Indexes

In some cases, non-clustered secondary indexes may introduce deadlocks. In this example, the maintenance of the secondary index introduces deadlock. 

The following is the statement used to create the secondary index used in this example: 

   create index ex1ind2 on example1 (column3) with fill factor = 90,

   PAD_INDEX

   Connection1 > BEGIN TRANSACTION

   Connection2 > BEGIN TRANSACTION

   Connection1 > INSERT INTO example1 VALUES (100, ‘AAAA’, ‘CCBA’, ‘ ‘, ‘

   ‘, ‘ ‘, ‘ ‘)

   Connection2 > INSERT INTO example1 VALUES (300, ‘AAAB’, ‘CCCZ’, ‘ ‘, ‘

   ‘, ‘ ‘, ‘ ‘)

   Connection2 > UPDATE example1 SET column3 = ‘CCBA’ where column1 = 105

At this point, Connection2 may be blocked by Connection1 because Connection1 may be holding a lock on the secondary non-clustered index page where Connection2 needs to update. 

   Connection1 > UPDATE example1 SET column3 = ‘CCCZ’ where column1 = 305

At this point, Connection1 may be blocked by Connection2, resulting in a deadlock. This situation can happen when Connection1 is waiting for a lock to update the non-clustered secondary index where Connection2 has already inserted and holds a lock on that page. The following is the deadlock trace for this deadlock example: 

   97/04/20 19:05:38.75 spid11   *** DEADLOCK DETECTED with spid 12 ***

   spid 11 requesting EX_PAGE (waittype 0x8005), blocked by:

     EX_PAGE: spid 12, dbid 6, page 0x112f, table example1, indid 0x2

     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =

   ‘CCCZ’ where column1 = 305

   spid 12 waiting for EX_PAGE (waittype 0x8005), blocked by:

     EX_PAGE: spid 11, dbid 6, page 0x1108, table example1, indid 0x2

     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =

   ‘CCBA’ where column1 = 105

   VICTIM: spid 11, pstat 0x0000 , cputime 50

This deadlock can be avoided by dropping the secondary index. It is not possible to pad the index to contain one row per page, so this situation can be avoided only by eliminating the non-clustered secondary index or by modifying the application. 

Deadlocks may occur with more than two connections, in which case the deadlock trace lists the spids involved in the deadlock and also the conflicting locks. Deadlocks may occur with RLOCK and XRLOCK locks, which are acquired during index traversing. Deadlocks may also occur because of extent locks (PR_EXT, NX_EXT, UPD_EXT & EX_EXT). 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s