I often hear from people wondering why their seemingly simple DELETE statement is taking so long to execute, especially when only a small number of rows are involved.
Unfortunately, there isn’t a quick answer to this, as several factors could be contributing to the delay. Let’s explore two of the most common causes I’ve encountered.
For this example, I’m using Brent Ozar’s version of the Stack Overflow 2010 10GB database.
Is something blocking my delete?
SQL Server has a feature called blocking. When I mention blocking, people often groan, thinking it’s a problem. However, blocking is actually a crucial feature that helps prevent concurrent queries from causing chaos in our data. Blocking only becomes an issue when queries are blocked for too long, and what constitutes “too long” depends on the specific scenario and whether users are experiencing noticeable delays.
If another process is blocking your DELETE query, you might be waiting for a while. I’ve seen situations where someone runs a script that modifies data in a table within a transaction, then leaves the transaction open without committing or rolling back before heading off to lunch. This can potentially block other data modifications until the transaction is committed or someone with access intervenes to kill the process.
EXEC sp_whoisactive;
Foreign Key checks
It’s very likely that your tables will have foreign keys, depending on your database design. While foreign keys aren’t always necessary, they are commonly used to enforce referential integrity. I added foreign keys to the Stack Overflow database for this demo.
If your table is referenced by a foreign key from another table, SQL Server will need to check the other table to ensure that the rows you’re trying to delete aren’t being referenced. The performance impact of this process depends on how many foreign keys reference your table and how efficiently SQL Server can check the data in the dependent tables.
You can see which tables depend on your table by using one of the following methods:
- Right-click on the table in SSMS and select ‘View Dependencies‘
- Highlight the table in a query window and press Alt + F1
- Run ‘sp_help YourTableName‘
For example, if you want to delete a row from your Users table, which is referenced by other tables through foreign keys, SQL Server must search all those other tables to determine if the row you intend to delete is still needed. You can think of this process as similar to running a query to locate the user in each of those tables.
If there are no indexes on the dependent tables, SQL Server will have to scan the entire table each time to check for matching values, which can significantly impact performance.
In the execution plan above, you can see four Clustered Index Scans, which means SQL Server had to read through the rows in the Comments and Badges tables once each, and the Posts table twice. This added up to around 13GB of data read, which, on my SQL instance, took over 20 seconds. That’s a lot of work just to delete one row!
We can significantly improve performance by ensuring that the foreign key columns are indexed, which helps avoid the lengthy table scans.
The new execution plan below shows four Index Seeks, which read only 0.5MB of data and took less than a second to complete on my instance. That’s a massive improvement.