Delete only one record takes 20 seconds and have to delete 11000

Question

Delete only one record takes 20 seconds and have to delete 11000

paratras

SSC Enthusiast

Points: 175
More actions
August 11, 2020 at 4:25 pm

#3779013

Hi, i need some help.
Server 2016, 96gb of ram, 24 cpus, and so.
One table with about 20 columns, no special columns at all.
36 millions records and a clustered index.
Deleting one record by id in the clustered index takes 20 seconds, 2 records take 40 seconds and so.
The plan shows 98% of "delete cluster index"
Any ideas? thanks
Diego

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I've been through this one before... How many Foreign Keys are pointing at that table? You may have to add indexes to the columns in the other tables on the columns that have the FKs that point to the table you're trying to delete from. In my case (really bad wide table), I had to add indexes to about 25 other tables. It was taking close to 8 minutes to delete just one row and that dropped down to sub-second after adding the indexes to the other tables.

Also, any triggers on the table you're trying to delete from? How about any indexed views?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 2

How many non-clustered indexes do you have on the table?

The more indexes you have, the more stuff it needs to look at to delete. If you can disable all of the non-clustered indexes and rebuild them after the delete you should get a performance boost.

Alternately, if you can delete a range of rows rather than 1 record at a time, you should get a performance boost. What I mean is if you delete records where the ID is > 100 and the ID is less than < 110, you may see a performance boost with the delete as it doesn't need to seek on the index each time.

It also may be slow if others are using this table. If other users are writing to the table, your delete may get blocked.

When you say "by ID in the clustered index", you mean that the clustered index is on the ID column and the ID column is the ONLY thing in your WHERE clause of the delete, right?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

I think Jeff is correct. There will be one or more tables that have foreign keys referencing the able you are trying to delete from. These tables won't have indexes to the column that references the PK (or unique constraint) on the table you are trying to delete from. You can use this query to find which foreign keys are referencing the table:

DECLARE @Tablename sysname = 'myTableName' 

SELECT 'Table  ' + OBJECT_NAME(fk.parent_object_id) + ' Foreign Key ' + QUOTENAME(fk.NAME) as fkQuery
  FROM sys.foreign_keys AS fk
 INNER JOIN sys.foreign_key_columns AS fc
         ON fk.OBJECT_ID = fc.constraint_object_id
 INNER JOIN sys.tables t
         ON t.OBJECT_ID = fc.referenced_object_id
  WHERE OBJECT_NAME (fk.referenced_object_id) = @Tablename

When you've found the foreign keys you can either index the column that references the constraint on the table you are trying to delete from or temporarily disable the foreign key if you are sure there are no values referencing the data you are deleting.