Would a nonclustered index on the primary key speed up deletes and prevent deadlocks?
3
votes
2
answers
968
views
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:
unknown
unknown
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)
unknown
unknown
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)
----------
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
Asked by DForck42
(3068 rep)
Mar 7, 2018, 03:36 PM
Last activity: Aug 21, 2024, 08:05 PM
Last activity: Aug 21, 2024, 08:05 PM