I have a very high traffic database. In the application they will issue deletes, and frequently these deletes will deadlock with other deletes on the same table. I am researching on ways to remedy this, and one answer I saw was to ensure that the deletes have the fastest pathway to the record.
Currently, all of the deletes follow this form:
(@0 int) delete [dbo].[table] where (table_id = @0)
Each of these tables has a primary key and clustered index on table_id
.
My question is, could adding a non-clustered index on table_id
help speed these deletes up and prevent deadlocks from occurring?
Deadlock graph:
<deadlock victim="process2b53a408c8"><process-list><process id="process2b53a408c8" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" waittime="2034" ownerId="14899585071" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x50746b1c0" lockMode="S" schedulerid="7" kpid="967168" status="suspended" spid="185" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.850" lastattention="1900-01-01T00:00:00.850" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585071" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0) </inputbuf></process><process id="process218b0a5468" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (c94c2713ec0f)" waittime="2036" ownerId="14899585063" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x1fcd0c4d10" lockMode="S" schedulerid="4" kpid="962372" status="suspended" spid="384" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.847" lastattention="1900-01-01T00:00:00.847" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585063" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0) </inputbuf></process></process-list><resource-list><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock9d21dab80" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process218b0a5468" mode="X" /></owner-list><waiter-list><waiter id="process2b53a408c8" mode="S" requestType="wait" /></waiter-list></keylock><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock4de4b1800" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process2b53a408c8" mode="X" /></owner-list><waiter-list><waiter id="process218b0a5468" mode="S" requestType="wait" /></waiter-list></keylock></resource-list></deadlock>
table definition:
create table [dbo].[redacted_table](
[id] [int] identity(1,1) not for replication not null,
[loan_id] [int] not null,
[user_role_id] [int] not null,
[assigned_by_user_id] [int] not null,
[out_for_assignment] [bit] not null,
[assignment_date] [datetime] not null,
[recognize_date] [datetime] not null,
[routing_source] [varchar](50) null,
[request_guid] [uniqueidentifier] null,
constraint [PK_redacted_table] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY]
) on [PRIMARY]
go
alter table [dbo].[redacted_table] add constraint [DF_redacted_table_assignment_date] default (getdate()) for [assignment_date]
go
alter table [dbo].[redacted_table] add constraint [DF_redacted_table_recognize_date] default (getdate()) for [recognize_date]
go
alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_redacted_table3] foreign key([loan_id])
references [dbo].[redacted_table3] ([id])
go
alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table3]
go
alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_user_redacted_table4] foreign key([user_role_id])
references [dbo].[user_redacted_table4] ([id])
go
alter table [dbo].[redacted_table] check constraint [FK_redacted_table_user_redacted_table4]
go
alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_redacted_table5] foreign key([assigned_by_user_id])
references [dbo].[redacted_table5] ([id])
go
alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table5]
go
Best Answer
Since your statement is deleting rows via a
WHERE
clause on the primary key,table_id
, adding a non-clustered index ontable_id
is unlikely to help, and may well increase the number of deadlocks occurring.DELETE FROM ...
deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.Take this very simple example:
The actual execution plan for the above
DELETE
:You should probably add the
CREATE TABLE
statements to your question, along with details about the deadlock via a deadlock graph.Now, if we add an additional non-clustered index, and run another delete:
We see the following plan:
As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.
If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:
Looking at your deadlock graph, in combination with the list of non-clustered indexes in your question, it looks like the deadlock is likely caused by simultaneous deletes of rows contained on the same page. Adding a
ROWLOCK
hint to your delete may prevent this particular deadlock. You could also test usingSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
prior to the delete operation. Be aware that changes the isolation level for all statements occuring afterwards within the same batch. Ensure you reset the transaction isolation level if you execute other statements after the delete operation.The deadlock shows the wait resource is the same for both statements, except the row number:
waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)"
The row number is the number inside brackets. @Kin shows a great way to look at the details of thewaitresource
in his answer here