Add Indexes to Improve SQL DELETE Performance (2024)

By: Jared Westover |Updated: 2024-06-26 |Comments | Related: > Indexing

Problem

Youadd indexes to speed up queries, but what about deleting data? Has someonetold you that indexes slow down deletes? The rationale is that the more copies ofthe data exist via indexes, the more data SQL Server must remove when cleaning up.But does this claim hold up when testing?

Solution

In this article, we'll examine the importance of indexes in improvingDELETEstatements. Also, many developers assume that adding aforeign key creates an index,which means they're typically missing. How can you identify which foreignkeys are missing indexes? Don't worry. A handy DMV helps find them. Startingtoday, you'll have the skills to make your DELETE statements faster.

Deleting Data

Over the years, I've come to value the mindset of creating a table withthe certainty of deleting data. When you build a fresh new table, it's easyto forget at some point; you'll need to delete records. Your company may havea record retention policy in place. For example, you only keep client data for 10years. At that point, you must purge it from the system or face the wrath of yourlegal team. Also, end users will likely delete data from the application by clickinga button. No matter the reason for deleting, you want your DELETE statements toperform optimally, just likeSELECT statements.

What's the most common way to improve the lookup performance ofSELECTstatements? But all things being equal, adding proper indexes.

Adding Indexes

You've likely heard that adding anon-clustered index to a table forcesSQL to delete the index's data. The same idea applies toupdates andinserts.It's true. Since an index is a copy of the table, ideally with fewer columns,SQL cleans up the index when deleting data from the base table or clustered indexif the column lives in the index. Yet, similar to how non-clustered indexes makeSELECT statements faster, they can make deleting data faster.

Demo Dataset

Let's build a dataset to demonstrate the effect indexes have on deletingdata. Using the script below, I'll create three tables:

• Table1 contains 1,000 rows.
• Table2 contains 10,000 rows.
• Table3 contains 10,000,000 rows.

Notice I'm adding foreign key relationships between each table. The scriptbelow takes about 20 seconds to run on my server.

-- mssqltips.comUSE master;IF DATABASEPROPERTYEX('DeleteDemo', 'Version') IS NOT NULLBEGIN ALTER DATABASE DeleteDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DeleteDemo;END;GOCREATE DATABASE DeleteDemoALTER DATABASE DeleteDemo SET RECOVERY SIMPLE;GOUSE DeleteDemo;GOCREATE TABLE dbo.Table1 ( Id INT IDENTITY(1, 1), String1 VARCHAR(200), CONSTRAINT PK_Table1_Id PRIMARY KEY CLUSTERED (Id));CREATE TABLE dbo.Table2 ( Id INT IDENTITY(1, 1), Id2 INT NOT NULL, CONSTRAINT PK_Table2_Id PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_Table1_Id2 FOREIGN KEY (Id2) REFERENCES dbo.Table1 (Id));CREATE TABLE dbo.Table3 ( Id INT IDENTITY(1, 1), Id3 INT NOT NULL CONSTRAINT PK_Table3_Id PRIMARY KEY CLUSTERED (Id), CONSTRAINT FK_Table3_Id3 FOREIGN KEY (Id3) REFERENCES dbo.Table2 (Id));GO;WITH cteN (Number) AS (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns AS s1 CROSS JOIN sys.all_columns AS s2)INSERT INTO dbo.Table1 (String1)SELECT TOP 1000 SUBSTRING( REPLICATE('abcdefghijklmnopqrstuvwxyz', 4), (ABS(CHECKSUM(NEWID())) % 26) + 1, (ABS(CHECKSUM(NEWID()) % (90 - 50 + 1)) + 50)) AS SomeString FROM cteN n;;WITH cteN (Number) AS (SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns AS s1 CROSS JOIN sys.all_columns AS s2)INSERT INTO dbo.Table2 (Id2)SELECT (ABS(CHECKSUM(NEWID()) % (1000 - 1 + 1)) + 1) AS Id2 FROM cteN n;;WITH cteN (Number) AS (SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns AS s1 CROSS JOIN sys.all_columns AS s2)INSERT INTO dbo.Table3 (Id3)SELECT (ABS(CHECKSUM(NEWID()) % (10000 - 1 + 1)) + 1) AS Id2 FROM cteN n;GO

Foreign Keys

Foreign keys help ensure the database'sreferential integrity. If you'renew to SQL Server, you might assume SQL creates a non-clustered index when you adda foreign key relationship. I've got some bad news: SQL does not. We createdtwo foreign key relationships above and can run the script below to check whichindexes exist for our tables.

-- mssqltips.comSELECT DB_NAME(DATABASE_ID) AS DATABASENAME, SCHEMA_NAME(C.SCHEMA_id) AS SCHEMANAME, OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, INDEX_NAME = ( SELECT NAME FROM SYS.INDEXES A WHERE A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID) FROM SYS.DM_DB_INDEX_USAGE_STATS B INNER JOIN SYS.OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID WHERE DATABASE_ID = DB_ID(DB_NAME()) AND C.TYPE = 'U';

The demo database contains zero non-clustered indexes for Table2 and Table3.

Deleting Without Indexes

Now, it's time to delete some data. We'll start with zero non-clusteredindexes on the foreign keys. After looking at a few performance markers, we'llroll back the transaction to prevent rebuilding our dataset. Also, I'll turnon my actual execution plan.

-- mssqltips.comBEGIN TRANSACTION;DELETE t3 FROM dbo.Table3 t3 INNER JOIN dbo.Table2 t2 ON t2.Id = t3.Id3 WHERE t2.Id2 = 101;DELETE t2 FROM dbo.Table2 t2 WHERE Id2 = 101;DELETE t1 FROM dbo.Table1 t1 WHERE t1.Id = 101;

We could look at the messages tab and add up the elapsed and CPU times, but I'lllook at the QueryTimeStats under each of the three execution plans.

The table below represents the CPU and elapsed time from each execution plan.

The higher CPU times for Table3 indicate that the plan went parallel with a costof about 20. The cost threshold on my server is set to 10, and MAX DOP is set to4.

Locking

Allow me to highlight something else. If I run Adam Machanic'ssp_whoisactive in another session with the get_locks parameter turned on, wetake an exclusive lock on Table3. When you delete ~5000 rows in SQL Server, SQLissues an exclusive lock on the table or object. Also, the request_count=1 is agood indicator.

EXECUTE sp_whoisactive @get_locks = 1;

SQL holds the exclusive lock until the transaction finishes. Imagine if we performedmultiple statements after the deletes; the exclusive lock remains. Depending onyour isolation level and if you enableREAD_COMMITTED_SNAPSHOT, even reads might be blocked the entire time.

Run the code below to roll back the transaction to avoid recreating the entiredataset.

-- mssqltips.comROLLBACK TRANSACTION;

Deleting With Indexes

Let's add two non-clustered indexes on Table2 and Table3 with the codebelow.

-- mssqltips.comCREATE NONCLUSTERED INDEX IX_Table2_Id2 ON dbo.Table2 (Id2);CREATE NONCLUSTERED INDEX IX_Table3_Id3 ON dbo.Table3 (Id3);GO

With our indexes in place, let's execute the original DELETE statementswithout committing the transaction.

-- mssqltips.comBEGIN TRANSACTION;DELETE t3 FROM dbo.Table3 t3 INNER JOIN dbo.Table2 t2 ON t2.Id = t3.Id3 WHERE t2.Id2 = 101;DELETE t2 FROM dbo.Table2 t2 WHERE Id2 = 101;DELETE t1 FROM dbo.Table1 t1 WHERE t1.Id = 101;

Below are the captured execution times.

Even with added indexes, SQL takes an exclusive lock on Table3. Yet, it'sheld for a fraction of the time. The statements went from 2136ms to 32ms, a massivedifference in my book. The indexes' benefits for looking up data outweighedthe cost of removing records from them.

In this article, I'm not saying you should add the maximum number of indexesand expect deletes to be faster. Indexes cost when it comes to deleting data. Inthis instance, the lookup benefits outweighed the cost.

Clean Up

Since we're done, let's roll back the transaction and then drop thedatabase.

-- mssqltips.comROLLBACK TRANSACTION;GOUSE master;IF DATABASEPROPERTYEX('DeleteDemo', 'Version') IS NOT NULLBEGIN ALTER DATABASE DeleteDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DeleteDemo;END;GO

Key Points

• When you add a foreign key, SQL doesn't automatically create a non-clusteredindex. To find all foreign keys without an index, check out Eli Leiba'sarticle,Script to identify all non-indexed foreign keys in a SQL Server database.
• The longer transactions take, the longer SQL holds the locks. Strive tomake transactions tiny. Also, consider whether you need all those SELECT statementsat the end.
• When updating or deleting 5,000 or more rows, SQL issues an exclusive lockon the object. Since reads won't require a shared lock, you can lessenthe impact on other sessions using READ_COMMITED_SNAPSHOT.

Next Steps

• One of the tools I used in this article was Adam Machanic'ssp_whoisactive. If you're interested in learning how to collect thedata results, Jeffrey Yao wroteSP_WhoIsActive Data Collection and Analysis to get you started.
• Do you have a big table with lots of rows to delete and want to reduce theimpact on a production server? Aaron Bertrand's article,Fastest way to Delete Large Number of Records in SQL Server, will put youon the right path.
• Are you thinking about enabling READ_COMMITED_SNAPSHOT but need some moreanswers? Check outREAD_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server by SergeyGigoyan to learn more.

About the author

Article Last Updated: 2024-06-26