Sample Header Ad - 728x90

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