SQL Server delete performance

Question

I have a routine in our .NET web application that allows a user on our platform to clear their account (i.e. delete all their data). This routine runs in a stored procedure and essentially loops through the relevant data tables and clears down all the various items they have created.

The stored procedure looks something like this.

ALTER procedure [dbo].[spDeleteAccountData](
    @accountNumber varchar(30) ) 
AS
BEGIN
    SET ANSI_NULLS ON ;
    SET NOCOUNT ON;

    BEGIN TRAN  
    BEGIN TRY
        DELETE FROM myDataTable1 WHERE accountNumber = @accountNumber
        DELETE FROM myDataTable2 WHERE accountNumber = @accountNumber
        DELETE FROM myDataTable3 WHERE accountNumber = @accountNumber
        //Etc.........

    END TRY
    BEGIN CATCH
        //CATCH ERROR
    END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION; 
SET ANSI_NULLS OFF;
SET NOCOUNT OFF;
END

The problem is that in some cases we can have over 10,000 rows on a table and the procedure can take up to 3-5 minutes. During this period all the other connections on the database get throttled causing time-out errors like the one below:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Are there any general changes I can make to improve performance? I appreciate there are many unknowns related to the design of our database schema, but general best practice advice would be welcomed! I thought about scheduling this task to run during the early hours to minimise impact, but this is far from Ideal as the user wouldn't be able to regain access to their account until this task had been completed.

Additional Information:

• SQL Server 2008 R2 Standard
• All tables have a clustered index
• No triggers have been associated to any delete commands on any of the relevant tables
• Foreign key references exist on a number of tables but the deletion order accounts for this.

Edit: 16:52 GMT

The delete proc affects around 20 tables. The largest one has approx 5 million records. The others have no more the 200,000, with some containing only 1000-2000 records.

Dildo

All tables have a clustered index, great. But what column is it on? How many other indexes on these tables? How big are they? Do you have a rollback somewhere that you didn't bother showing? Is there really any reason you'd want to roll back the delete to myDataTable1 if the delete on myDataTable2 fails? Do you expect to find conditions where this will fail, given that you have constructed the deletes in proper FK dependency order? – Liverpudlian

The tables each have a bigint identity column set as the primary key. The accountNumber columns don't have an index set against them so I'm guessing this is where I'm going wrong! – Dildo

The delete proc affects around 20 tables. The largest one has approx 5 million records. The others have no more the 200,000, with some containing only 1000-2000 records. Good point regarding the rollback! If a delete statement fails it's not necessary to rollback, but the user's account would be corrupted so they'd need to get ejected and locked out of their account if this happens. Would the rollback option come with a big performance penalty? – Dildo

If you keep the transaction open for all of the deletes, then all of the tables are potentially blocked for the entire length of the transaction. So some options - I like the idea @marc_s proposed, where you flag an account number for deletion but don't actually delete it until later. The "get ejected" part is logic you could use based on the flag, not on the presence/absence of rows. You could also consider using read committed snapshot isolation for the queries that read from these tables - perf hit to tempdb but readers won't be blocked by deletes. – Liverpudlian

Answer 1

Do you have an index on accountNumber in all tables ?

Seeing that you delete using a WHERE clause by that column, this might help.

Another option (and probably even better solution) would be to schedule deletion operations at night, e.g. when user selects to delete his account, you're only setting a flag, and a delete job runs at night actually deleting those accounts flagged for deletion.

Voiceless

I agree with the index comment. I'd just restructure it as a statement rather than a question -- e.g. "if you don't have an index, you should!" 10K records is hardly big so for it to be taking so long seems like there's either a lot of unindexed data and/or a lot of constraints and FK checks that it's doing. – Schaerbeek

Thanks for the suggestion! This delete procedure will very likely get executed only once in the lifetime of an account and usually with small volumes of data. Therefore 90% of delete cases execute in 3-5 seconds. It's the big accounts that are causing issues, so we could selectively schedule these cases for off-peak deletion. I'm worried about adding an additional index on 'accountNumber' to all these tables which I think will cause much more profound performance problems. – Dildo

Answer 2

If you have an index on the accountNumber field then I guess the long time for deletion is due to locks (generated by other processes) or to foreign keys affected by the respective tables.

1. If is due to locks then you should see if you can reduce them using nolock where you can actually do that.
2. if there is a problem of foreign keys .. well you have to wait .. If you do not want to wait though and your application logic does not rely on enforcing the FKs (like sending errors to the application for FK violations, and testing against them) or you feel your application is perfect and then for a short period of time you do not need FKs, then you can disable related FKs prior to deletions with ALTER TABLE xxx NOCHECK CONSTRAINT all and then re enable it.

Off course purists will blame me for the latter but I had been using this a lot of times when need arises.

Cletis

Thanks for this. I had an awfully slow delete (deleting 100 rows out of 15k took almost 2 minutes), despite having an index on the WHERE clause of my delete. Disabling/Reenabling the FKs made it run in less than a second. – Scabby

Answer 3

One way you might want to try is this:

1. Create a SP.
2. For each table, delete rows in small batches of some size that works for you (say 10 rows per batch).
3. Put each batch deletion inside a transaction and add a custom delay between each transaction.

Example:

    DECLARE @DeletedRowsCount INT = 1, @BatchSize INT = 300;
    WHILE (@DeletedRowsCount> 0) BEGIN
        BEGIN TRANSACTION  
            DELETE TOP (@BatchSize) dbo.Table
            FROM dbo.Table
            WHERE Id = @PortalId;
            SET @DeletedRowsCount = @@ROWCOUNT;
        COMMIT;

        WAITFOR DELAY '00:00:05';
    END

I guess you can do the same without a SP as well. In fact, it might be better like that.

Nettienetting

Answer 4

SqlCommand.CommandTimeout is the short answer. Increase its value.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Note, the Connection Timeout is not the same thing as the CommandTimeout.

...

Do you have an index on "accountNumber" on each table?

You could have a clustered key on the surrogate-key of the table, but not the "accountNumber".

...

Basically, you're gonna have to look at the execution plan (or post the execution plan) here.

But here is some "starter code" for trying an index on that column(s).

if exists (select * from dbo.sysindexes where name = N'IX_myDataTable1_accountNumber' and id = object_id(N'[dbo].[myDataTable1]'))
    DROP INDEX [dbo].[myDataTable1].[IX_myDataTable1_accountNumber]
GO

CREATE INDEX [IX_myDataTable1_accountNumber] ON [dbo].[myDataTable1]([accountNumber]) 
GO

Gargoyle

You expect users to wait 3-5 minutes before they can use the application? – Schaerbeek

This will prevent the error but the real problem is that the deletes are taking long enough to hit the timeout threshold in the first place. Increasing the timeout to 5 or 10 minutes still means users will be blocked for 5 or 10 minutes or until the query completes. – Liverpudlian

No, users should not have to wait 3-5 minutes on a web application. But that's my subjective opinion, not necessarily his. – Gargoyle

No user is going to wait 3-5 minutes for anything web-based. Something takes 5 seconds and I'm already extremely frustrated. – Liverpudlian

You added that question after your initial post. Is it possible the down-vote pre-dated your edit? Also, just because you ask about an index (which should be a comment!), that does not make your initial advice to just increase the timeout any better, sorry. – Liverpudlian

So if he tweaks the indexes, and gets it down to 33 seconds (which is acceptable to him and his clients)......then what does he do? "The time in seconds to wait for the command to execute. The default is 30 seconds." – Gargoyle

I have a suggestion. Increase the SqlCommand.CommandTimeout to 33 (or slightly more) seconds. So there may be a place where my original answer could be helpful. – Gargoyle

33 seconds is still too long. Increasing the timeout for a query that should never take this long in the first place is like putting a tuxedo on a turd. YMMV. – Liverpudlian

Answer 5

It could be worth switching the database into Read Committed Snapshot mode. This will have a performance impact, how much depends on your application.

In Read Committed Snapshot mode, writers and readers no longer block each other, although writers still block writers. You don't say what sort of activity on the table is getting prevented by the delete, so it's a little hard to say if this will help?

http://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx

Having said that, 3-5 minutes for a deletion on tables with ~10k rows seems absurdly slow. You mention foreign keys, are the foreign keys indexed? If not, deletion can cause table scans on the other end to make sure you're not breaking RI, so maybe check that first? What does SQL Server Profiler say for reads/writes for these deletion queries?

Zorn

with a lot of FKs / table the deletion can be VERY slow even with the keys indexed .. – Cletis