1

I am facing a strange issue wherein I had analyzed a stored procedure which runs every 3 minutes and was putting load on CPU. I found out that there are number of select statements which is part of this procedure and all of them were doing full table scan(reading all pages in tables). So, I tested them in Test environment and supported that with a non-clustered index. Same was confirmed with concerned vendor and they agreed for the change. I was deploying them to Production yesterday and checked the logical reads of those queries and cross-checked same after the new index, validated that it is having positive impact and logical reads came down by 1/10th.

Immediately after deploying this index, the procedure which runs every 3 minutes, started failing. Executed manually to check the issue and found out that, its causing deadlock each and every time except 1 or 2 time in like 1 hour. I was at complete loss as to how come an index could cause a deadlock? Ideally index should solve deadlock however it was opposite.

The table I am referring to is a heap and doesn't have clustered primary key rather it has non-clustered primary key. I have concurrence from all party to do the change from NC to clustered however it is linked with multiple table through PK-FK relationship and requires down-time and hence it is currently on hold.

I have captured the deadlock graph and also have sp_blitzlock in place. It seems deadlock is happening between application query and this procedure however I can't understand how this index is causing it and how come when I rolled back this index, it is working smoothly and no deadlock.

Deadlock graph is as below:

Deadlock Graph in Sentry

<deadlock>
  <victim-list>
    <victimProcess id="processef645b848" />
  </victim-list>
  <process-list>
    <process id="processef645b848" taskpriority="0" logused="0" waitresource="PAGE: 6:1:965 " waittime="3661" ownerId="303318514" transactionname="INSERT" lasttranstarted="2020-11-02T12:18:12.793" XDES="0x31fbb78e0" lockMode="S" schedulerid="1" kpid="8564" status="suspended" spid="1246" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:00.500" lastbatchcompleted="2020-11-02T12:18:00.500" lastattention="1900-01-01T00:00:00.500" clientapp="SQLAgent - TSQL JobStep (Job 0x417A2365E91D1647B2C225CA23D84860 : Step 1)" hostname="DB_Server" hostpid="3628" loginname="SQL_Agent_Login" isolationlevel="read committed (2)" xactid="303318514" currentdb="7" currentdbname="DB_Name_1" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtend="2888" sqlhandle="0x02000000c984e814ec51be0d03e3852ee0b755da518273d00000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="DB_Name_1.dbo.Procedure_Name" line="171" stmtstart="13412" stmtend="13482" sqlhandle="0x03000700151ddc58cd73c00013ac000001000000000000000000000000000000000000000000000000000000">
EXECUTE (@EXEC_IMMEDIATE_VAR)    </frame>
        <frame procname="adhoc" line="1" sqlhandle="0x01000700d070832b90a421810300000000000000000000000000000000000000000000000000000000000000">
EXEC Procedure_Name 'DB_User_Application'    </frame>
      </executionStack>
      <inputbuf>
EXEC Procedure_Name 'DB_User_Application'   </inputbuf>
    </process>
    <process id="processe1435468" taskpriority="0" logused="996" waitresource="PAGE: 6:1:88348 " waittime="3841" ownerId="303318578" transactionname="user_transaction" lasttranstarted="2020-11-02T12:18:13.007" XDES="0x255f023b0" lockMode="IX" schedulerid="1" kpid="4120" status="suspended" spid="1271" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:13.017" lastbatchcompleted="2020-11-02T12:18:13.007" lastattention="1900-01-01T00:00:00.007" clientapp="Vendor_Name" hostname="APP_Server_Name" hostpid="1296" loginname="DB_User_Application" isolationlevel="read committed (2)" xactid="303318578" currentdb="6" currentdbname="DB_Name_2" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="720" stmtend="1560" sqlhandle="0x020000001fdba70fc3e8a833920a732fe1c19b282e28ac1c0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="adhoc" line="1" stmtend="1190" sqlhandle="0x02000000f228391fab59e520bc237b6a919f53ced0b1ac290000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
update Deadlock_Table set Error_Code = '000',TIMEOUT_NETWORK_ID=NULL , var32_32='XXXX', var32_15='000', var32_22='1', var32_04='IB', var32_05='XXX', var256_01='Processed OK', var32_09='XXX', var32_14='048', var64_01='XXXXX', var32_06='02112020121802', var32_03='XXX', var32_10='XXXX', var32_07='XXXX', var32_02='XXX', var32_01='XXX', Node_Id='APP_Server_Name', Message_Id='XXXX', End_Point_Id='XXXXX' where Log_Id=XXXXX and Receive_Time='XXXXX'   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="965" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4d151f900" mode="IX" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processe1435468" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="processef645b848" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="88348" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4e1da7d00" mode="S" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processef645b848" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="processe1435468" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

Below is DDL of table:

CREATE TABLE [dbo].[Deadlock_Table](
    [Log_id] [int] IDENTITY(1,1) NOT NULL,
    [Receive_time] [varchar](15) NOT NULL,
    [A] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    [VAR32_03] [varchar](32) NULL,
    [VAR32_04] [varchar](32) NULL,
    [VAR32_05] [varchar](32) NULL,
    [VAR32_06] [varchar](32) NULL,
    [VAR32_07] [varchar](32) NULL,
    [VAR32_08] [varchar](32) NULL,
    [VAR32_09] [varchar](32) NULL,
    [VAR32_10] [varchar](32) NULL,
    [VAR32_11] [varchar](32) NULL,
    [VAR32_12] [varchar](32) NULL,
    [VAR32_13] [varchar](32) NULL,
    [VAR32_14] [varchar](32) NULL,
    [VAR32_15] [varchar](32) NULL,
    [VAR32_16] [varchar](32) NULL,
    [VAR32_17] [varchar](32) NULL,
    [VAR32_18] [varchar](32) NULL,
    [VAR32_19] [varchar](32) NULL,
    [VAR32_20] [varchar](32) NULL,
    [VAR32_21] [varchar](32) NULL,
    [VAR32_22] [varchar](32) NULL,
    [VAR32_23] [varchar](32) NULL,
    [VAR32_24] [varchar](32) NULL,
    [VAR32_25] [varchar](32) NULL,
    [VAR32_26] [varchar](32) NULL,
    [VAR32_27] [varchar](32) NULL,
    [VAR32_28] [varchar](32) NULL,
    [VAR32_29] [varchar](32) NULL,
    [VAR32_30] [varchar](32) NULL,
    [VAR32_31] [varchar](32) NULL,
    [VAR32_32] [varchar](32) NULL,
    [VAR32_33] [varchar](32) NULL,
    [VAR32_34] [varchar](32) NULL,
    [VAR32_35] [varchar](32) NULL,
    [VAR32_36] [varchar](32) NULL,
    [VAR32_37] [varchar](32) NULL,
    [VAR32_38] [varchar](32) NULL,
    [VAR32_39] [varchar](32) NULL,
    [VAR32_40] [varchar](32) NULL,
    [VAR32_41] [varchar](32) NULL,
    [VAR32_42] [varchar](32) NULL,
    [VAR32_43] [varchar](32) NULL,
    [VAR32_44] [varchar](32) NULL,
    [VAR32_45] [varchar](32) NULL,
    [VAR32_46] [varchar](32) NULL,
    [VAR32_47] [varchar](32) NULL,
    [VAR32_48] [varchar](32) NULL,
    [VAR32_49] [varchar](32) NULL,
    [VAR32_50] [varchar](32) NULL,
    [VAR32_51] [varchar](32) NULL,
    [VAR32_52] [varchar](32) NULL,
    [VAR32_53] [varchar](32) NULL,
    [VAR32_54] [varchar](32) NULL,
    [VAR32_55] [varchar](32) NULL,
    [VAR32_56] [varchar](32) NULL,
    [VAR32_57] [varchar](32) NULL,
    [VAR32_58] [varchar](32) NULL,
    [VAR32_59] [varchar](32) NULL,
    [VAR32_60] [varchar](32) NULL,
    [VAR32_61] [varchar](32) NULL,
    [VAR32_62] [varchar](32) NULL,
    [VAR32_63] [varchar](32) NULL,
    [VAR32_64] [varchar](32) NULL,
    [VAR64_01] [varchar](64) NULL,
    [VAR64_02] [varchar](64) NULL,
    [VAR64_03] [varchar](64) NULL,
    [VAR64_04] [varchar](64) NULL,
    [VAR64_05] [varchar](64) NULL,
    [VAR64_06] [varchar](64) NULL,
    [VAR64_07] [varchar](64) NULL,
    [VAR64_08] [varchar](64) NULL,
    [VAR64_09] [varchar](64) NULL,
    [VAR64_10] [varchar](64) NULL,
    [VAR64_11] [varchar](64) NULL,
    [VAR64_12] [varchar](64) NULL,
    [VAR64_13] [varchar](64) NULL,
    [VAR64_14] [varchar](64) NULL,
    [VAR64_15] [varchar](64) NULL,
    [VAR64_16] [varchar](64) NULL,
    [VAR64_17] [varchar](64) NULL,
    [VAR64_18] [varchar](64) NULL,
    [VAR64_19] [varchar](64) NULL,
    [VAR64_20] [varchar](64) NULL,
    [VAR64_21] [varchar](64) NULL,
    [VAR64_22] [varchar](64) NULL,
    [VAR64_23] [varchar](64) NULL,
    [VAR64_24] [varchar](64) NULL,
    [VAR64_25] [varchar](64) NULL,
    [VAR64_26] [varchar](64) NULL,
    [VAR64_27] [varchar](64) NULL,
    [VAR64_28] [varchar](64) NULL,
    [VAR64_29] [varchar](64) NULL,
    [VAR64_30] [varchar](64) NULL,
    [VAR64_31] [varchar](64) NULL,
    [VAR64_32] [varchar](64) NULL,
    [VAR128_01] [varchar](128) NULL,
    [VAR128_02] [varchar](128) NULL,
    [VAR128_03] [varchar](128) NULL,
    [VAR128_04] [varchar](128) NULL,
    [VAR128_05] [varchar](128) NULL,
    [VAR128_06] [varchar](128) NULL,
    [VAR128_07] [varchar](128) NULL,
    [VAR128_08] [varchar](128) NULL,
    [VAR128_09] [varchar](128) NULL,
    [VAR128_10] [varchar](128) NULL,
    [VAR128_11] [varchar](128) NULL,
    [VAR128_12] [varchar](128) NULL,
    [VAR128_13] [varchar](128) NULL,
    [VAR128_14] [varchar](128) NULL,
    [VAR128_15] [varchar](128) NULL,
    [VAR128_16] [varchar](128) NULL,
    [VAR256_01] [varchar](256) NULL,
    [VAR256_02] [varchar](256) NULL,
    [VAR256_03] [varchar](256) NULL,
    [VAR256_04] [varchar](256) NULL,
    [VAR256_05] [varchar](256) NULL,
    [VAR256_06] [varchar](256) NULL,
    [VAR256_07] [varchar](256) NULL,
    [VAR256_08] [varchar](256) NULL,
    [VAR512_01] [varchar](512) NULL,
    [VAR512_02] [varchar](512) NULL,
    [VAR512_03] [varchar](512) NULL,
    [VAR512_04] [varchar](512) NULL,
    [VAR1024_01] [varchar](1024) NULL,
    [VAR1024_02] [varchar](1024) NULL,
    [E] [varchar](20) NULL,
    [M] [varchar](40) NULL,
    [E] [varchar](50) NULL,
    [N] [varchar](40) NULL,
    [TN] [int] NULL,
    [T] [numeric](1, 0) NULL,
    [D] [numeric](1, 0) NULL,
 CONSTRAINT [XPKtable_name] PRIMARY KEY NONCLUSTERED 
(
    [Log_id] ASC,
    [Receive_time] ASC
)

Currently it has only one index other than primary key on 3rd column(say A).

Index I had proposed was below:

CREATE NONCLUSTERED INDEX [IX_Deadlock_Table] ON [dbo].[Deadlock_Table]
(
 var32_02 ,
 D,
 T
 ) 
GO

Thanks for reading such a lengthy question and really appreciate your input in solving this deadlock. In case, I have missed some vital details, kindly put them in the comment section and I would add them.

Application query plan for update statement --> https://www.brentozar.com/pastetheplan/?id=r14x3TCOD

Query which runs inside cursor are multiple insert statements which are basically select statements involving this deadlock_table joined with other table and they are as below:

INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_07,null,var32_02,var32_01,var32_20,Coalesce(null,var32_10,var32_11,var32_21),var32_21,null,null,Coalesce(var32_31,var32_12,var32_13),null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,var32_17,var32_19,var32_18,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =4 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 4) AND var32_02 NOT IN ('0044')
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,convert(varchar(2),N),var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,NULL,NULL,NULL,NULL,var64_22,var32_30,var64_23,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =10 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 10)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =11 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 11)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,      case when var64_19 = '64' then var64_10 else var64_12 end as TO_ACCOUNT_NUM, var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,var64_17,var64_21,var64_20,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =12 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1))       AND (var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12)      or  var64_19 in (SELECT B.CODE_NAME FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12))
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_44,null,var32_02,var32_46,null,null,null,null,null,null,null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =13 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 13)

Query plan for 1st select statement without my index(in its current form) - https://www.brentozar.com/pastetheplan/?id=BkDnpbktw

Query plan for select statement after index created - https://www.brentozar.com/pastetheplan/?id=r1SCeGktD

Below are logical reads before and after index creation:

Table 'deadlock_table'. Scan count 5, logical reads 326203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'deadlock_table'. Scan count 58, logical reads 12055, physical reads 0, read-ahead reads 23, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Version

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

1
5

It's difficult to answer your question in detail with the anonymized plan and deadlock, and not a ton of information about what's in the agent job involved in the deadlock. But in general terms, the answer to this question might be useful in helping you track down the problem:

I was at complete loss as to how come an index could cause a deadlock?

Adding indexes can cause queries to access data in a different order than it did without those indexes.

In your case, the application query has this pattern:

  1. Update exactly 1 row in the heap dbo.deadlock_table, taking an IX lock on the page where this row is located (and of course an X lock on the actual row)
  2. A trigger fires, which updates potentially multiple rows in the same heap, taking an IX lock on the page(s) where these rows are located

After adding your new NC index, it appears that the agent job query is requesting S locks on these heap pages in the opposite order.

The problem is that the heap has no defined order. Assuming you have RID lookups in the agent job query (i.e. the new NC index is not a covering index), then it's essentially issuing S locks "randomly" throughout the heap. The rows being updated by the application query, while right next to each other in the NC PK, can be essentially randomly distributed throughout the heap as well.

Without the NC index, access to the heap by the agent job query was likely either more predictable (an allocation ordered scan) or aligned with the PK (scan of NC PK + RID lookups), so you were able to avoid these deadlocks.

In my view, based on the information available, some viable options are:

  • remove the NC index (go back to the way things were), or
  • make the NC index "covering" (by adding columns to the includes, or changing the query to select less columns) so it doesn't need to acquire locks in the heap at all
5
  • Thanks a lot for going through lengthy question and answering in detail. May I derive from your answer that if I change this heap to a table(meaning defining clustering key on primary key), chances of deadlock could be avoided. Covering index wouldn't be possible because there are many different queries which runs in the loop, I have selected the most common fields and have created index on that. – Learning_DBAdmin Nov 3 '20 at 16:10
  • 2
    @Learning_DBAdmin Absolutely, clustering on Log_id, Receive_time would reduce the likelihood of deadlocks tremendously. – Josh Darnell Nov 3 '20 at 16:15
  • I have added query which is part of cursor in the question. Also will be adding query plan before and after adding index. Appreciate if you could please take a look at it once you get a chance and provide your valuable input. – Learning_DBAdmin Nov 3 '20 at 16:42
  • 3
    @Learning_DBAdmin I just looked through the execution plans, and it's as I guessed. Before the index, you had DOP (4 in this case) full scans of the deadlock_table heap (predictable order). After the index, you have index seeks and "random" RID lookups into the heap. – Josh Darnell Nov 3 '20 at 17:43
  • Changed primary key from non-clustered to clustered, Added that NC index again and deadlock still persists. Not sure, what to do. Number of logical reads reduces with NC index compared to Clustered index scan but it still takes longer. With high reads, it takes lesser time and lesser reads it takes longer, And deadlock is not disappearing. I am sure, I am missing some important thing; don't know what. Rolled-back the NC index for now. – Learning_DBAdmin Nov 26 '20 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.