Geek asks and answers

SQL Server uninstalls performance

I have a regular program in our .NET web application that allows a user on our platform to clear their account (i.e. delete all their data). This procedure runs in a stored procedure and essentially goes through the corresponding data tables and clears all the different elements that they 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 more than 10,000 lines on the table, and the procedure can take up to 3-5 minutes. During this period, all other connections in the database receive throttling, causing timeout errors, such as below:

System.Data.SqlClient.SqlException (0x80131904): Timed out. The wait period expires before the operation is completed or the server is not responding.

- , ? , , , ! , , , , .

:

  • SQL Server 2008 R2
  • , .

: 16:52 GMT

20 . 5 . 200 000, 1000-2000 .

+5
4

accountNumber ?

, WHERE, .

(, , ) - , . , , , , .

+4

accountNumber, , - ( ) , .

  • , , , nolock, .
  • ... , .. , FK (, FK, ), , , FK, FK ALTER TABLE xxx NOCHECK CONSTRAINT all .

, , .

+1

SqlCommand.CommandTimeout - . .

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

, , CommandTimeout.

...

"accountNumber" ?

, "accountNumber".

...

, ( ) .

- " " .

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
0

, Read Committed Snapshot. , .

Read Committed Snapshot, , . , , , ?

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

, 3-5 ~ 10k . , ? , , , RI, , , ? SQL Server Profiler / ?

0

More articles:


All Articles