Suppression de lignes dans la table de provoquer des SERRURES

J'exécute la commande suivante pour supprimer des lignes dans des lots d'une table de grande taille (150 millions de lignes):

DECLARE @RowCount int
WHILE 1=1
    BEGIN
        DELETE TOP (10000) t1
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK
    END

Ce tableau est TRÈS utilisé. Cependant, il est la suppression d'enregistrements, mais il est aussi la cause d'un blocage sur certains dossiers, jetant ainsi les erreurs de l'utilisateur (qui n'est pas acceptable dans l'environnement, nous sommes dans).

Comment puis-je supprimer les anciens enregistrements sans causer de serrures? Dois-je réduire la taille du lot de 10000 enregistrements à 1000? Comment sera-ce l'effet de la taille des fichiers journaux (nous avons très peu d'espace sur le disque dur de la gauche pour le grand journal de croissance).

Des suggestions?

OriginalL'auteur Sean | 2012-01-25

  1. 7

    J'ai vu de semblables problèmes sporadiques dans le passé, et même dans de petits lots 0f 5000 enregistrements, verrouillage serait encore arriver. Dans notre cas, chaque suppression/mise à jour a été contenue dans son propre Begin Tran...Commit boucle. Pour corriger le problème, la logique de

    WaitFor DELAY '00:00:00:01'

    a été placé en haut de chaque boucle et à corriger le problème.

    Est-ce au début/à la fin de chaque lot ou chaque suppression de ligne?

    OriginalL'auteur UnderflowE

  2. 4

    Tout d'abord, il ressemble à SUPPRIMER l'exécution d'Analyse d'Index Cluster, je vous recommande de faire ce qui suit:

    create index [IX.IndexName] ON t1(YearProcessed, PrimaryKey)

    Deuxième - est-il besoin de joindre t2 table?

    Et ensuite utiliser la requête suivante pour supprimer les lignes, en supposant que votre PrimaryKey colonne est de type INT:

    declare @ids TABLE(PrimaryKey INT)
WHILE 1=1
    BEGIN
        INSERT @ids 
        SELECT top 10000 DISTINCT t1.PrimaryKey
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        IF @@ROWCOUNT = 0 BREAK

        DELETE  t1
        WHERE PrimaryKey in (Select PrimaryKey from @ids)

        delete from @ids

    END

    Et ne pas oublier d'enlever t2 table de jointure si il n'est pas nécessaire

    Si c'est encore à cause des verrous - puis baisser le nombre de lignes supprimées à chaque tour

    Je suis effectivement intérieure de rejoindre à partir d'une table parent une liste de clés primaires à supprimer (qui est en fait dans une variable de table). Je viens de poster deux table de simplifier l'écriture de scripts. Très similaire à ce que vous avez fait, mais vous avez pris une approche différente, que je vais essayer.

    OriginalL'auteur Oleg Dok

  3. 1

    Je pense que vous êtes sur la bonne voie.

    Coup d'oeil à ces deux articles, trop:

    et:

    http://www.dbforums.com/microsoft-sql-server/985516-deleting-without-locking.html

    Avant d'exécuter le supprimer, vérifier l'estimation de plan de requête pour voir si il
    est en train de faire un index de recherche pour le supprimer, ou encore une table entière
    analyse/l'accès.

    OriginalL'auteur paulsm4

  4. 0

    En plus des autres suggestions (qui visent à réduire le travail effectué au cours de suppression) vous pouvez également configurer SQL Server pour ne pas bloquer les autres lecteurs tout en faisant supprime sur une table.

    Cela peut être fait en utilisant "l'isolement d'instantané" qui a été introduit avec SQL Server 2005:

    http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

    Il est bon de noter que l'Isolement d'Instantané nécessite de changer le niveau d'isolation pour une seule transaction. Read committed l'Isolement pour modifier le niveau d'isolation par défaut, mais il nécessite une très brève interruption.

    OriginalL'auteur a_horse_with_no_name

  5. 0

    Si vous avez quoi que ce soit avec les suppressions en cascade assurez-vous qu'ils sont indexés.

    Mettant en évidence la requête de SUPPRESSION et en cliquant sur Display estimated execution plan montrera suggéré index - qui, dans mon cas, comprenait certains des suppressions en cascade.

    D'ajouter des index pour les supprimer beaucoup plus rapide - mais je ne essayez pas de supprimer toutes les lignes à la fois.

    OriginalL'auteur Simon_Weaver

  6. 0

    la meilleure façon que j'ai trouvé est de la forme asp.net DeleteExpiredSessions . vous faites un READUNCOMMITTED choisir et de mettre les enregistrements dans une table temporaire , de supprimer l'enregistrement à l'aide d'un CURSEUR.

     ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
    AS
        SET NOCOUNT ON
        SET DEADLOCK_PRIORITY LOW 

        DECLARE @now datetime
        SET @now = GETUTCDATE() 

        CREATE TABLE #tblExpiredSessions 
        ( 
            SessionID nvarchar(88) NOT NULL PRIMARY KEY
        )

        INSERT #tblExpiredSessions (SessionID)
            SELECT SessionID
            FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
            WHERE Expires < @now

        IF @@ROWCOUNT <> 0 
        BEGIN 
            DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT SessionID FROM #tblExpiredSessions 

            DECLARE @SessionID nvarchar(88)

            OPEN ExpiredSessionCursor

            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

            WHILE @@FETCH_STATUS = 0 
                BEGIN
                    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                END

            CLOSE ExpiredSessionCursor

            DEALLOCATE ExpiredSessionCursor

        END 

        DROP TABLE #tblExpiredSessions

    RETURN 0

    OriginalL'auteur yaniv

  7. 0

    Essayer cela,

    DECLARE @RowCount int
WHILE 1=1
    BEGIN
        BEGIN TRANSACTION 
        DELETE TOP (10000) t1
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007
         END TRANSACTION 
         COMMIT TRANSACTION 
        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK
    END

    OriginalL'auteur Vikrant More