Persisting Data – part 7: Concurrency and Deadlocking

note- Nov 29, 2015 – finally got a copy of test scripts on github. see https://github.com/bwunder/data-load-methods for working version of all TSQL shown in this post…
In part 6, the charts examined an information region close to an edge of the useful test window. In those tests, the test results at low row size values were overshadowed by the overhead of SSIS. Not until the row size pushed beyond 10,000 did the gem from the test scenario emerge: sending a table-valued parameter of rows to SQL Server via a .NET DataAdapter was so screaming fast that all that was left was the ash gray chart line plummeting to the lowest levels seen in this series.

I exagerate. The current test box is not a high performance box – it is a Windows 7 laptop. Everything is slowed down quite a bit, but up until now the comparative value of the test results have been valid – and hopefully interesting. I know this because I have conducted these same tests on server class hardware with comparable relative results.

Now the focus is turned toward another lens of the test visor for evaluating database throughput capabilities. In previous testing a process executes a battery of tests across an established set of row sizes from 1 to 100,000 in isolation form other database activity: contention free. Care was taken that there was no other access to the database and that there was no other intensive processing occurring on the machine. Even the system backup window was avoided for testing. In this current test, contention is the variable. It is reasonable to expect the single disc IO path to compound most of the throughput degrading side-effects of contention (i.e blocking, deadlocking, range scans, fragmentation, etc.).

In the chart below, that pristine environment is contrasted to a scenario where three processes are concurrently processing the test battery into the target hierarchy for both row-at-a-time and table-valued parameter based upsert processing. The familiar graphs for minimal concurrency test batteries are shown con coriandoli. The range graphs for three (3) concurrent loads are the solid colors:

Concurrency 

The side-effects of contention on throughput are even more significant than the chart suggests. Only the TVPUsert method in NOTABLOCK mode executed without deadlocking. The chart shows some unsettling throughput degradation but does not clearly show the corresponding effects on data – and test – quality due to deadlocking. The test scripts reported the deadlocks. A quick check in the system health extended events ring buffer will help determine the extent of the problem:

DECLARE @ring AS [XML];

SET @ring = ( SELECT CAST(xet.[target_data] AS [XML]) 
              FROM sys.dm_xe_session_targets xet
              INNER JOIN sys.dm_xe_sessions xe
              ON xe.[address] = xet.[event_session_address]
              WHERE xe.[name] = 'system_health' );

SELECT ROW_NUMBER() OVER (ORDER BY SysHealth.XEvent.value( '(@timestamp)', 'DATETIME')) AS Sequence
     , SysHealth.XEvent.value( '(@timestamp)', 'DATETIME') AS DeadlockTime
     , SysHealth.XEvent.query('.') AS [DeadlockEvent]
     --needs CU, CAST(SysHealth.XEvent.value('data[1]','NVARCHAR(MAX)') AS XML) AS DeadlockGraph
     , SysHealth.XEvent.value('data[1]','NVARCHAR(MAX)') AS DeadlockGraph
FROM ( SELECT @ring AS Ring ) AS Buffer
CROSS APPLY Ring.nodes ('//RingBufferTarget/event') AS SysHealth (XEvent) 
WHERE SysHealth.XEvent.value( '(@name)[1]', 'varchar (100)') = 'xml_deadlock_report' 
ORDER BY [DeadlockTime] DESC;

To help keep the DeadlockGraph column easy to work with, use a SQL Server Management Studio 2008 client and assure that the SSMS results pane is set to “Results to Grid”.  This provides a result set where each deadlock in the buffer can be examined in detail. Selecting a DeadlockGraph column opens the column as an XML document in a new SSMS window. XML inside the XML is a little annoying but not that difficult to work with given the XML data type. Unfortunately, the output is definitely invalid prior to SQL Server 2008 SP1 Cumulative Update 6 (CU6) or SQL Server 2008 R2 CU1. I had read that deadlock graph parsing was fixed but was not that interested until now – especially because there is no service pack, only an interim cumulative update.  Now that I had a deadlock generator, I was interested enough to apply the patch to my test box. (Whoops)

DeadlockResults

Sure enough, the XML is well formed with the Cumulative Update applied.  However, it does not produce a valid DeadlockGraph if saved with an .xdl extension. In fact, the first attempt to apply the CU ended with a corrupted master database and a SQL Server that would not start. A reinstallation of SQL Server was required.

The XML of a SQL Profiler DeadlockGraph (.xdl)  and the XML from the Extended Event ring buffer for the exact same deadlock are quite different; as is the graphical representation when loaded into SSMS. This is the DeadlockGraph from a file generated by SQL Profiler:   TVPUpsertDeadlock_1.xdl

DeadlockGraph

This is from a file generated by saving the  XML after opening it using the query above: XE_TVPUpsertDeadlock.xdl,

 XEDeadlockGraph

Both fascinating in some way but both completely useless to understand the deadlock. That finding confirms that applying the CU to a production box just to get a valid XML document is not even worth considering: wait for the (hopefully) well tested Service Pack.  If the SQL Server is not running a cumulative update level that generates well formed XML it is only a bit more difficult to work with the output, but certainly still much better than scraping deadlock info out of the ErrorLog – or the worst case scenario where all you can do is delete the ErrorLog because the deadlock info it contains makes the log file too big to bother opening. Note that lots-o-deadlocks in the extended event ring buffer can make for similarly slow queries.

I am able to learn quite a lot more about the deadlock by looking into the XML document – whether from SQL Profiler or from the Extended Event DMV. Similar useful information is there whether the DeadlockGraph is well formed or not. Viewing the one-level of nested XML as character data as required when triaging a deadlocking issues without the CU applied is absolutely a capability the DBA should possess.

System Health deadlock XML
<deadlock>
  <victim-list>
    <victimProcess id="process80a722c8" />
  </victim-list>
  <process-list>
    <process id="process80a722c8" taskpriority="0" logused="0" waitresource="OBJECT: 5:405576483:0 " waittime="302" ownerId="574792" transactionname="user_transaction" lasttranstarted="2011-01-07T20:23:52.673" XDES="0x82f983b0" lockMode="S" schedulerid="1" kpid="5204" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-01-07T20:22:21.557" lastbatchcompleted="2011-01-07T20:22:21.553" clientapp="Microsoft SQL Server Management Studio – Query" hostname="BILL764" hostpid="7448" loginname="BILL764\bwunder" isolationlevel="read committed (2)" xactid="574792" currentdb="5" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <executionStack>
        <frame procname="" line="83" stmtstart="4248" stmtend="4758" sqlhandle="0x0300050098b5d43320bb4f01649e00000100000000000000" />
        <frame procname="" line="42" stmtstart="2804" stmtend="2852" sqlhandle="0x02000000dfdd6b07a843a1eddd1f03ec3faaf4ed92f7ea33" />
      </executionStack>
      <inputbuf>

IF 'True' = 'True'

  BEGIN

    EXEC dbo.pGetIndexDetails 'SAMPLED'

    UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 1;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 5;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 10;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 50;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 100;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 500;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 1000;
    EXEC dbo.TVPUpsert;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferParameters SET RowsToPush = 5000;
    EXEC dbo.TVPUpsert;
    UPDATE dbo.tTransferP   </inputbuf>
    </process>
    <process id="process13fdc8" taskpriority="0" logused="83624" waitresource="OBJECT: 5:533576939:0 " waittime="271" ownerId="574004" transactionname="user_transaction" lasttranstarted="2011-01-07T20:23:52.413" XDES="0x8673f950" lockMode="IX" schedulerid="1" kpid="3648" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2011-01-07T20:22:24.823" lastbatchcompleted="2011-01-07T20:22:24.820" clientapp="Microsoft SQL Server Management Studio – Query" hostname="BILL764" hostpid="7448" loginname="BILL764\bwunder" isolationlevel="read committed (2)" xactid="574004" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="" line="119" stmtstart="6394" stmtend="7564" sqlhandle="0x03000500d093332c8224e800649e00000100000000000000" />
        <frame procname="" line="90" stmtstart="4760" stmtend="5514" sqlhandle="0x0300050098b5d43320bb4f01649e00000100000000000000" />
        <frame procname="" line="43" stmtstart="2784" stmtend="2824" sqlhandle="0x02000000042f17239161fcbcfe43ff51a99908ac5ef7caa9" />
      </executionStack>
      <inputbuf>
USE target;

UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 1;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 5;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 10;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 50;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 100;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 500;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 1000;
EXEC dbo.TVPUpsert;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 5000;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET RowsToPush = 50000;
EXEC dbo.TVPUpsert;
UPDATE dbo.tTransferParameters SET   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <objectlock lockPartition="0" objid="405576483" subresource="FULL" dbid="5" objectname="" id="locka517fe00" mode="SIX" associatedObjectId="405576483">
      <owner-list>
        <owner id="process13fdc8" mode="SIX" />
      </owner-list>
      <waiter-list>
        <waiter id="process80a722c8" mode="S" requestType="convert" />
      </waiter-list>
    </objectlock>
    <objectlock lockPartition="0" objid="533576939" subresource="FULL" dbid="5" objectname="" id="lock8b2ffc80" mode="S" associatedObjectId="533576939">
      <owner-list>
        <owner id="process80a722c8" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process13fdc8" mode="IX" requestType="convert" />
      </waiter-list>
    </objectlock>
  </resource-list>
</deadlock>

Cross referencing the resource-list lock modes with the SQL Server Books Online Lock Compatibility Matrix the S, IX, and SIX locks are not compatible resulting in a deadly embrace between two upsert operations.

  • SPID 55 has a SIX lock on the child table (ObjectId=405576483″)
  • SPID 54 and SPID 56 are waiting for the SIX lock to clear to place S locks
  • SPID 54 has an S lock on the parent table (ObjectId=533576939“)
  • SPID 55 is waiting for the S lock to clear to place an IX lock on the parent table.
SQL Profiler DeadlockGraph.xdl
<deadlock-list>
  <deadlock victim="process13fdc8">
    <process-list>
      <process id="process13fdc8" taskpriority="0" logused="0" waitresource="OBJECT: 5:405576483:0 " waittime="4449" ownerId="300525" transactionname="user_transaction" lasttranstarted="2011-01-07T20:22:25.223" XDES="0x809a03b0" lockMode="S" schedulerid="1" kpid="3648" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-01-07T20:22:24.823" lastbatchcompleted="2011-01-07T20:22:24.820" clientapp="Microsoft SQL Server Management Studio – Query" hostname="BILL764" hostpid="7448" loginname="BILL764\bwunder" isolationlevel="read committed (2)" xactid="300525" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="target.dbo.TVPUpsert" line="83" stmtstart="4248" stmtend="4758" sqlhandle="0x0300050098b5d43320bb4f01649e00000100000000000000">
            SELECT TOP(1) p.Id
            INTO #junk
            FROM dbo.tParent p WITH(TABLOCK,HOLDLOCK)
            JOIN dbo.tChild c WITH(TABLOCK,HOLDLOCK)
            ON p.Id = c.ParentId;

            — both pTVPMerge and pTVPUpsert are transactional
          </frame>
          <frame procname="adhoc" line="4" stmtstart="142" stmtend="182" sqlhandle="0x02000000042f17239161fcbcfe43ff51a99908ac5ef7caa9">
            EXEC dbo.TVPUpsert;
          </frame>
        </executionStack>
        <inputbuf>
          USE target;

          UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 10;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 50;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 100;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 500;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1000;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 50000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET
        </inputbuf>
      </process>
      <process id="process80a722c8" taskpriority="0" logused="0" waitresource="OBJECT: 5:405576483:0 " waittime="5088" ownerId="299085" transactionname="user_transaction" lasttranstarted="2011-01-07T20:22:24.587" XDES="0x894bf620" lockMode="S" schedulerid="1" kpid="5204" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-01-07T20:22:21.557" lastbatchcompleted="2011-01-07T20:22:21.553" clientapp="Microsoft SQL Server Management Studio – Query" hostname="BILL764" hostpid="7448" loginname="BILL764\bwunder" isolationlevel="read committed (2)" xactid="299085" currentdb="5" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
        <executionStack>
          <frame procname="target.dbo.TVPUpsert" line="83" stmtstart="4248" stmtend="4758" sqlhandle="0x0300050098b5d43320bb4f01649e00000100000000000000">
            SELECT TOP(1) p.Id
            INTO #junk
            FROM dbo.tParent p WITH(TABLOCK,HOLDLOCK)
            JOIN dbo.tChild c WITH(TABLOCK,HOLDLOCK)
            ON p.Id = c.ParentId;

            — both pTVPMerge and pTVPUpsert are transactional
          </frame>
          <frame procname="adhoc" line="11" stmtstart="446" stmtend="494" sqlhandle="0x02000000dfdd6b07a843a1eddd1f03ec3faaf4ed92f7ea33">
            EXEC dbo.TVPUpsert;
          </frame>
        </executionStack>
        <inputbuf>

          IF &apos;True&apos; = &apos;True&apos;

          BEGIN

          EXEC dbo.pGetIndexDetails &apos;SAMPLED&apos;

          UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 10;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 50;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 100;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 500;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1000;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferP
        </inputbuf>
      </process>
      <process id="process3e59dc8" taskpriority="0" logused="381168" waitresource="OBJECT: 5:533576939:0 " waittime="4862" ownerId="296768" transactionname="user_transaction" lasttranstarted="2011-01-07T20:22:23.593" XDES="0x894b7950" lockMode="IX" schedulerid="2" kpid="7968" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2011-01-07T20:22:23.080" lastbatchcompleted="2011-01-07T20:22:23.080" clientapp="Microsoft SQL Server Management Studio – Query" hostname="BILL764" hostpid="7448" loginname="BILL764\bwunder" isolationlevel="read committed (2)" xactid="296768" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="target.dbo.pTVPUpsert" line="119" stmtstart="6394" stmtend="7564" sqlhandle="0x03000500d093332c8224e800649e00000100000000000000">
            INSERT dbo.tChild
            ( ParentId
            , GMTDateTime
            , Value
            , Host
            , FloatingPoint
            , Boolean
            , Bytes )
            SELECT
            map.Id
            , tvp.GMTDateTime
            , tvp.Value
            , tvp.Host
            , tvp.FloatingPoint
            , tvp.Boolean
            , tvp.Bytes
            FROM #UpsertMapper AS map
            JOIN @TVP AS tvp
            ON map.[Name] = tvp.[Name]
            AND map.[LOBCheckSum] = tvp.[LOBCheckSum]
            AND map.[GMTDate] = CAST(tvp.GMTDateTime AS DATE)
            AND map.[GMTHour] = DATEPART(hour, tvp.GMTDateTime);
          </frame>
          <frame procname="target.dbo.TVPUpsert" line="90" stmtstart="4760" stmtend="5514" sqlhandle="0x0300050098b5d43320bb4f01649e00000100000000000000">
            EXEC dbo.pTVPUpsert @TableVariable
            , @LoggingLevel
            , @LoadMethod
            , @MergeParentRows OUTPUT
            , @MergeParent_ms OUTPUT
            , @InsertChildRows OUTPUT
            , @InsertChild_ms OUTPUT;
          </frame>
          <frame procname="adhoc" line="4" stmtstart="142" stmtend="182" sqlhandle="0x02000000042f17239161fcbcfe43ff51a99908ac5ef7caa9">
            EXEC dbo.TVPUpsert;
          </frame>
        </executionStack>
        <inputbuf>
          USE target;

          UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 10;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 50;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 100;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 500;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 1000;
          EXEC dbo.TVPUpsert;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 5000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 10000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET RowsToPush = 50000;
          EXEC dbo.TVPUpsert;
          UPDATE dbo.tTransferParameters SET
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <objectlock lockPartition="0" objid="405576483" subresource="FULL" dbid="5" objectname="target.dbo.tParent" id="lockab002a80" mode="SIX" associatedObjectId="405576483">
        <owner-list>
          <owner id="process3e59dc8" mode="SIX"/>
        </owner-list>
        <waiter-list>
          <waiter id="process13fdc8" mode="S" requestType="convert"/>
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="0" objid="405576483" subresource="FULL" dbid="5" objectname="target.dbo.tParent" id="lockab002a80" mode="SIX" associatedObjectId="405576483">
        <owner-list>
          <owner id="process3e59dc8" mode="SIX"/>
        </owner-list>
        <waiter-list>
          <waiter id="process80a722c8" mode="S" requestType="convert"/>
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="0" objid="533576939" subresource="FULL" dbid="5" objectname="target.dbo.tChild" id="locka42a8680" mode="S" associatedObjectId="533576939">
        <owner-list>
          <owner id="process80a722c8" mode="S"/>
        </owner-list>
        <waiter-list>
          <waiter id="process3e59dc8" mode="IX" requestType="convert"/>
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>

Is it just me or did Microsoft reinvent it’s own wheel and code two – or more – XML code generators to do the same task that don’t do the same task – instead making nonsense of the task?

It is easy enough to query the query cache using the sql_handle and offset values to see exactly which statement in a multi-statement batch or stored procedure was involved in the deadlock. That is what you must do if you have only the extended event deadlock info. It is much more convenient to get the statements in the XML document like you can do with the SQL Profiler .xdl DeadlockGraph. With the SQL Profiler originated data the strange thing is you cannot toggle between the XML and the graphical window once the file is opened in SSMS. All you get is the graph. In order to see the details in the XML an alternative viewing method to SSMS default associations must be used. It is definitely worth the effort.

The artifacts from this little romp with deadlocking are slowing down the editing experience. Windows Live Writer seems more interested in staying connected than it does helping me write. For example, backspacing 6 or 8 characters to correct a mistake can take several seconds and is very unpredictable. Because of this technical PITA I will close part 7 here and carry on in part 8 with a resolution to the deadlocking problems.

Advertisements
This entry was posted in Data Loading, Testing. Bookmark the permalink.

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