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>
<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)
Best Answer
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:
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:
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)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: