DELETE performance in SQL Server on clustered index, large table

sql-server performance clustered-index
Erik Sundström picture Erik Sundström · Jun 20, 2011 · Viewed 14.6k times · Source

I have a table with more than 20 million rows, and when i do:

DELETE [Table] WHERE ID = ?

It takes over 40 seconds. The ID column is clustered.

Is this what you could expect? or is it possible to optimize this?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Jun 20, 2011

In addition to the fine points JNK included in their answer, one particular killer I've seen is when you're deleting rows from the referenced table for one or more foreign key constraints, and the referencing column(s) in the referencing table(s) aren't indexed - you're forcing a table scan on each of those tables to occur before the delete can be accepted.

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?

Comments to question "How to decrease response time of a simple select query?" tell: "What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG …

Read More
LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE '%test%'; or SELECT * FROM table WHERE Contains(Column, "test");

Read More