Início / dba / Perguntas / 308563
Accepted
Don
Asked: 2022-03-11 10:52:38 +0800 CST

O log de transações do SQL é limpo no final de um loop?

  • 772

Estou executando o SQL Server 2019 Enterprise Edition. Todos os meses, executamos um procedimento armazenado que carrega milhões de registros com datas de serviço que remontam a cinco anos. Eu tenho 400 GB de espaço alocado em 4 arquivos de dados e 100 GB de espaço alocado para o arquivo de log. O trabalho falha com frequência porque o arquivo de log é preenchido devido à transação ativa. O banco de dados está no modelo de recuperação simples . Então, eu acredito que deve clarear ao final de cada transação. O desenvolvedor alterou o trabalho para que ele percorra e carregue os registros um ano por vez.

DROP TABLE IF EXISTS #UnpvtDx;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.DxCode 
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    DxCode FOR CodeLine
    IN
    (
        Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
        Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
        Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
    )
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;

DROP TABLE IF EXISTS #UnpvtPointer;

SELECT ClaimHeader_ID
    ,ClaimDetail_ID
    ,ClaimServiceLine
    ,Unpvt.CodeLine
    ,Unpvt.Pointer 
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT 
(
    Pointer FOR CodeLine 
    IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;

INSERT INTO PROD.Claim.ClaimDiag
(
    ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
    PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
    MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
    TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
    det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
    det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
    det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
    det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
    DiagnosisCD = dx.DxCode,
    DiagnosisDESC = diag.DiagnosisDESC,
    DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
    ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
    ON dx.ClaimDetail_ID = det.ClaimDetail_ID
        AND dx.ClaimHeader_ID = det.ClaimHeader_ID
        AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
    ON point.ClaimDetail_ID = det.ClaimDetail_ID
        AND point.ClaimHeader_ID = det.ClaimHeader_ID
        AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
    ON dx.DxCode = diag.DiagnosisCD
        AND diag.ICDVersion = 'ICD10CM'
        AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;

O procedimento armazenado é executado a partir de um trabalho do SQL Agent com este comando:

DECLARE @year INT
DECLARE cur CURSOR FOR 

SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)   
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)

OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0  
BEGIN  
    EXEC Claim.sp_UpdateClaimDiag @year

    FETCH NEXT FROM cur INTO @year      
END 

CLOSE cur
DEALLOCATE cur

O fim de um loop é considerado uma transação e, portanto, o arquivo de log deve ser esvaziado após o processamento de cada ano de registros ou o arquivo de log continua a ser preenchido até que o trabalho tenha iterado em cada loop e carregado os registros de todos os anos?

Também vou aumentar o arquivo de log para 150 GB, mas isso maximizará o espaço disponível (sem cair abaixo do buffer de 10%).

Postando todo o código do procedimento armazenado.

USE [Prod]
GO
    
SET ANSI_NULLS ON
GO
    
SET QUOTED_IDENTIFIER ON
GO
    
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
    
BEGIN
    SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
    SET IMPLICIT_TRANSACTIONS OFF
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    -- variable declaration
    DECLARE
        @transactional BIT
        , @trancount INT
        , @err INT
        , @procname SYSNAME
        , @error INT
        , @message VARCHAR(4000)
        , @xstate INT
        , @RecordCount int;
    
    SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
        -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
        -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
        , @transactional = 0 
    
        -- optionally begin transaction and begin try block
        IF @transactional = 1 SET @trancount = @@TRANCOUNT

        BEGIN TRY
            IF @trancount = 0 and @transactional = 1
                BEGIN TRANSACTION

            ELSE IF @transactional = 1
                SAVE TRANSACTION p1
    
            ----------------------------------------------------------------------------------------
            ---Unpivot columns to Rows into Temp tables
            ----------------------------------------------------------------------------------------
                
            ---ICD9CM  & ICD10CM
            DROP TABLE IF EXISTS #UnpvtDx;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.DxCode 
            INTO #UnpvtDx
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                DxCode FOR CodeLine IN
                (
                    Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
                    Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
                    Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
                    Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
                    Diagnosis13CD
                )
            ) as Unpvt ---53 secs
            WHERE YEAR(ServiceFromDT) = @year;

            --Select top 100 * from #UnpvtDx where DxCode is null
    
            DROP TABLE IF EXISTS #UnpvtPointer;

            SELECT
                ClaimHeader_ID
                ,ClaimDetail_ID
                ,ClaimServiceLine
                ,Unpvt.CodeLine
                ,Unpvt.Pointer 
            INTO #UnpvtPointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            UNPIVOT 
            (
                Pointer FOR CodeLine IN
                (
                    DiagPointer1, DiagPointer2,
                    DiagPointer3,DiagPointer4
                )
            ) as Unpvt ---40 secs
            WHERE YEAR(ServiceFromDT) = @year;
    
            --Select top 100 * from #UnpvtPointer
                
            ----------------------------------------------------------------------------------------
            --- INSERT INTO yearly records from the temp table
            ----------------------------------------------------------------------------------------
        
            INSERT INTO Prod.Claim.ClaimDiag (
                ClaimHeader_ID,
                ClaimDetail_ID,
                SourceID,
                EDWLoadDTS,
                PartnerCD,
                PartnerNM,
                ClaimID,
                ClaimServiceLine,
                ClaimStatus,
                CCOMemberID,
                MemberID,
                PlaceOfServiceCD,
                ServceFromDT,
                ServiceToDT,
                ClaimForm,
                TypeOfBillCD,
                DiagnosisCD,
                DiagnosisDESC,
                DiagPointer
            )
    
            SELECT DISTINCT
                det.ClaimHeader_ID,
                det.ClaimDetail_ID,
                det.SourceID,
                det.EDWLoadDTS,
                det.PartnerCD,
                det.PartnerNM,
                det.ClaimID,
                det.ClaimServiceLine,
                det.ClaimStatus,
                det.CCOMemberID,
                det.MemberID,
                det.PlaceOfServiceCD,
                det.ServiceFromDT,
                det.ServiceToDT,
                det.ClaimForm,
                det.TypeOfBillCD,
                DiagnosisCD = dx.DxCode,
                DiagnosisDESC = diag.DiagnosisDESC,
                DiagPointer = point.Pointer
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
                ON ch.ClaimHeader_ID = det.ClaimHeader_ID
            INNER JOIN #UnpvtDx dx
                ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                    AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                    AND dx.ClaimServiceLine = det.ClaimServiceLine
            LEFT JOIN #UnpvtPointer point
                ON point.ClaimDetail_ID = det.ClaimDetail_ID
                    AND point.ClaimHeader_ID = det.ClaimHeader_ID
                    AND point.ClaimServiceLine = det.ClaimServiceLine
            LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
                ON dx.DxCode = diag.DiagnosisCD
                    AND diag.ICDVersion = 'ICD10CM'
                    AND diag.ActiveFLG = 1
            WHERE YEAR(det.ServiceFromDT) = @year
                --AND Year(det.ServiceFromDT) = 2021--for testing
                --and det.ClaimID ='21006E06455'--for testing
    
            ----------------------------------------------------------------------------------------
            --insert into updatelog table
            SET @RecordCount = @@ROWCOUNT;
    
            DECLARE @procName1 SYSNAME
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
            INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
            SELECT GETDATE(),
                'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
                @procName1,
                'Claim.ClaimDiag'
    ----------------------------------------------------------------------------------------
            DROP TABLE IF EXISTS #UnpvtDx
            DROP TABLE IF EXISTS #UnpvtPointer
    ----------------------------------------------------------------------------------------
            SPEXIT:

            IF @transactional = 1 and @trancount = 0 COMMIT
        END TRY

        ----------------------------------------------------------------------------------------
        -- error handling with catch
        BEGIN CATCH
            SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
            IF @transactional = 1 and @xstate = -1 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
            IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
        
            DROP TABLE IF EXISTS #claims
            SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4)) 
    
            RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message) 
            RETURN @error
        END CATCH
    
        RETURN 0  
    END
    GO
sql-server transaction-log

1 respostas

  1. Best Answer

    Ao usar o SIMPLE RECOVERYmodelo, o log de transações é truncado em cada ponto de verificação. Não necessariamente no final da sua transação. Os checkpoints acontecem a cada 60 segundos.

    Olhando para a seção abaixo do seu código, você não está realmente iniciando a transação explicitamente. Você inicialmente set @transactional = 0e, em seguida, só inicia uma transação se @transactional = 1. Além disso, você está apenas definindo @trancountum valor aqui com base na IFinstrução. O BEGIN TRYbloco ainda inicia, pois você não tem um BEGIN/ENDbloco após a IFinstrução. Este não é o seu problema, porém, significa apenas que você está fazendo transações implícitas a cada vez, em vez de criar uma transação explícita.

    SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
    -- 0 =  no: will not execute batches inside a transaction; a partial success of procedure is possible
    -- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
    , @transactional = 0 
    
    -- optionally begin transaction and begin try block
    IF @transactional = 1 SET @trancount = @@TRANCOUNT

    BEGIN TRY
        IF @trancount = 0 and @transactional = 1
            BEGIN TRANSACTION

        ELSE IF @transactional = 1
            SAVE TRANSACTION p1

    Eu suspeito que, mesmo com um ano de dados de cada vez, você está gerando mais taxa de transferência de log de transações do que sua unidade pode suportar. Quanto espaço vale um ano de dados ocupando nesta tabela?

    Considere reduzir o tamanho do lote de um ano para um mês e veja se isso ajuda. Enquanto você está nisso, pare de cercar sua coluna de data com uma função YEAR(). Isso torna essas colunas não SARGABLE, o que significa que o SQL Server não pode usar nenhum índice existente ao recuperar os dados. Isso pode fazer com que sua transação seja executada por mais tempo do que o necessário e, assim, fazendo com que outras transações em andamento sejam retidas no log enquanto essa transação é executada.

    Considere as alterações abaixo em seu procedimento armazenado. Eu simplifiquei um pouco para destacar as partes importantes a serem alteradas. Você precisará mesclá-lo com o que você tem e testá-lo. Isso facilita o controle do tamanho do lote ao chamar o procedimento.

    Além disso, eu removi as NOLOCKdicas. Leia mais aqui para entender por que isso é uma má ideia. Você provavelmente pensou que precisava das NOLCOKdicas por causa das cláusulas where não SARGABLE.

    CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag]
    @Interval int = 30
AS
BEGIN
    DECLARE
        @MinServiceFromDT DATETIME
        @MaxServiceFromDT DATETIME,
        @NextServiceFromDT DATETIME;

    SELECT
        @MinServiceFromDT = MIN(hdr.MinServiceFromDT),
        @MaxServiceFromDT = MAX(hdr.MinServiceFromDT)
    FROM PROD.Claim.ClaimHeader;

    WHILE @MinServiceFromDT <= @MaxServiceFromDT
    BEGIN
        BEGIN TRY
            @NextServiceFromDT = DATEADD(day,@Interval,@MinServiceFromDT);

            ---ICD9CM  & ICD10CM
            DROP TABLE IF EXISTS #UnpvtDx;

            DROP TABLE IF EXISTS #UnpvtPointer;
    
            --------------------------------------------------------------
            ---Unpivot columns to Rows into Temp tables
            --------------------------------------------------------------

            BEGIN TRANSACTION

            SELECT ClaimHeader_ID, ...
            INTO #UnpvtDx
            FROM Prod.Claim.ClaimDetail det
            UNPIVOT 
            (
                DxCode FOR CodeLine IN
                (
                    Diagnosis1CD,...
                )
            ) as Unpvt
            WHERE ServiceFromDT >= @MinServiceFromDT
                AND ServiceFromDT < @NextServiceFromDT;

            SELECT ClaimHeader_ID, ...
            INTO #UnpvtPointer
            FROM Prod.Claim.ClaimDetail det
            UNPIVOT 
            (
                Pointer FOR CodeLine IN
                (
                    DiagPointer1, DiagPointer2,
                    DiagPointer3,DiagPointer4
                )
            ) as Unpvt
            WHERE ServiceFromDT >= @MinServiceFromDT
                AND ServiceFromDT < @NextServiceFromDT;
            
            --------------------------------------------------------------
            --- INSERT INTO yearly records from the temp table
            --------------------------------------------------------------
        
            INSERT INTO Prod.Claim.ClaimDiag
                (ClaimHeader_ID, ....)
            SELECT DISTINCT det.ClaimHeader_ID, ....
            FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
            INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
                ON ch.ClaimHeader_ID = det.ClaimHeader_ID
            INNER JOIN #UnpvtDx dx
                ON dx.ClaimDetail_ID = det.ClaimDetail_ID
                    AND dx.ClaimHeader_ID = det.ClaimHeader_ID
                    AND dx.ClaimServiceLine = det.ClaimServiceLine
            LEFT JOIN #UnpvtPointer point
                ON point.ClaimDetail_ID = det.ClaimDetail_ID
                    AND point.ClaimHeader_ID = det.ClaimHeader_ID
                    AND point.ClaimServiceLine = det.ClaimServiceLine
            LEFT OUTER JOIN Reference.Reference.Diagnosis diag
                ON dx.DxCode = diag.DiagnosisCD
                    AND diag.ICDVersion = 'ICD10CM'
                    AND diag.ActiveFLG = 1
            WHERE det.ServiceFromDT >= @MinServiceFromDT
                AND det.ServiceFromDT < @NextServiceFromDT;

            --------------------------------------------------------------
            --insert into updatelog table

            COMMIT TRANSACTION

            SET @ MinServiceFromDT = @NextServiceFromDT
        END TRY

        ------------------------------------------------------------------
        -- error handling with catch
        BEGIN CATCH
            <do error handling stuff>
        END CATCH
    END
END
GO
    • 1

relate perguntas