Delete Large Number of Rows Is Very Slow - SQL Server

Question

I have database table contains around 3 millions records. When I delete large number of rows, around 400,000 records, the transaction takes forever to finish.

The table is not partitioned and the database is running on Sql Server 2012 Standard Edition. I'm using Nhibernate as ORM.

How do I make the transaction faster?

Here the creation script of the table

/****** Object:  Table [dbo].[ES_DirectorDataParameters]    Script Date: 03/10/2016 4:10:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ES_DirectorDataParameters](
    [DDP_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [DP_Name] [varchar](255) NOT NULL,
    [D_ID] [numeric](18, 0) NOT NULL,
    [DDP_DisplayName] [varchar](255) NULL,
    [DDP_Visibility] [varchar](50) NULL,
    [DDP_Replicable] [numeric](18, 0) NOT NULL CONSTRAINT [DF_ES_DirectorDataParameters_DD_Replicable]  DEFAULT ((1)),
 CONSTRAINT [PK_ES_DirectorDataParameters] PRIMARY KEY CLUSTERED 
(
    [DP_Name] ASC,
    [D_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters]  WITH CHECK ADD  CONSTRAINT [FK_ES_DirectorDataParameters_ES_DataParameters] FOREIGN KEY([DP_Name])
REFERENCES [dbo].[ES_DataParameters] ([DP_Name])
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters] CHECK CONSTRAINT [FK_ES_DirectorDataParameters_ES_DataParameters]
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters]  WITH CHECK ADD  CONSTRAINT [FK_ES_DirectorDataParameters_ES_Directors] FOREIGN KEY([D_ID])
REFERENCES [dbo].[ES_Directors] ([D_ID])
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters] CHECK CONSTRAINT [FK_ES_DirectorDataParameters_ES_Directors]
GO

here is the delete statement I found in the Activity Monitor when executing the delete (deleting around 200000 rows)

DELETE FROM ES_DirectorDataParameters WHERE DDP_ID = @p0

Note: Creating Cluster Index for the column DDP_ID helped the deletion performance slightly

Thanks

Answer 1

Let me start: 400.000 records is small. I happen to regularly delete batches of 64 million records.

How do I make the transaction faster?

The same way you make SQL Server faster in general: you provide a lot more IO capabilities than I assume you have.

• Split database, tempdb and log into separate hard discs. Oh, and make this an SSD or at least something with a backed up buffer.

SQL lives and dies by IO capabilities, and somehoe in the last 15 years or so everyone complaining about the performance of "large" data operations (Which in reality are tiny) that I ever talked to always ran SQL Server on a hardware layout that was totally inadequate for any real database work. We talk of a comical levels like "I want to win formula one with a truck" type of discrepancies.

To give you an idea of my layout (for the 64 million row operations): 6 SSD in 2x Raid 5 for data, 4 SSD in a Raid 10 for tempdb and 2 SSD mirrored for logs.

Also make sure you have enough memory - generally you should keep the active set of your data in memory, to avoid hitting discs.

And obviously check whether the proper indices exist.

Answer 2

The delete statement you found in the Activity Monitor

DELETE FROM ES_DirectorDataParameters WHERE DDP_ID = @p0

and one of your comments mentioning you use NHibernate indicate that NHibernate is actually sending 200000 separate delete statements.

So your problem is not the performance of a SQL query, but simply the volume of individual queries.

You need to rewrite the batch delete logic in pure SQL so that the deletion can be expressed as one statement.

If you need help with the SQL, please describe the logic about which rows are to be deleted and maybe I can help.

Answer 3

If your hardware is just too slow then see TomTom's answer.

Otherwise....

If the size of each row is large, then transaction logs may be the problem. Particularly if your rows are 10KB or bigger then deleting 100,000 rows can be a multi GB logging operation.

Check whether the auto growth size of your Transaction Log file is reasonable (perhaps 100MB) so that it doesn't need to auto grow too frequently.

Check your database Recovery Model. If it is not "simple" then the log will be storing all deletes until your next backup. If it is "simple" then it only has to store deletes until you commit the transaction.

If you don't mind splitting the deletes into several transactions this can have two benefits:

• You can interrupt (and restart) the deletion operation without the whole thing being rolled back.
• If Recovery Model is simple then it keeps the size of the transaction log down.

This script splits the task into chunks of 1000 deletes. It assumes that your table has a single column primary key.

DECLARE @RC INT;
SET @RC = 1;

WHILE (@RC > 0)
BEGIN
    BEGIN TRAN
        DELETE FROM YOUR_TABLE
        WHERE ID IN (
            SELECT TOP(1000) ID 
            FROM YOUR_TABLE AS T
            WHERE {your criteria for deletion using alias T}
        );
        SET @RC = @@ROWCOUNT;
    COMMIT
END

I used a similar technique when I had to delete many rows that contained blobs of uploaded files.