Don's questions

Eu uso o script a seguir para fazer um .bak de um banco de dados de produção e salvá-lo em um servidor de teste onde ele pode ser restaurado se necessário. O script não declara para compactar o arquivo. Executando o SQL Server 2019 Enterprise.

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT,  NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Ele .bakvai para o servidor de teste e, dependendo de onde você olha no Explorer, ele tem 162 GB ou 154 GB:

Para economizar espaço, tentei compactar .bakadicionando COMPRESSIONao script:

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT, NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Após executar com o COMMPRESSIONcomando incluído, ele caiu no mesmo tamanho no servidor de teste. Eu verifiquei os arquivos .ndfe .mdfe eles totalizam 770 GB ou 732 GB, dependendo de onde você olha. Além disso, ele inclui cerca de 23 GB de informações de índice.

Isso .bakjá está compactado, mesmo que eu não tenha dito isso?

Executando o SQL Server 2019 Enterprise. Eu tenho um grupo de arquivos PRIMÁRIO, mas também adicionei quatro arquivos ndf em um grupo de arquivos Data1 que criei. Como esperado, tive que aumentar o espaço disponível no grupo de arquivos Data1 à medida que mais dados eram adicionados, mas o grupo de arquivos PRIMARY mdf permaneceu relativamente estático em tamanho. Nos últimos meses tive que aumentar o espaço no grupo de arquivos PRIMARY. Começou com 5 GB e expandi para 10 GB há algumas semanas. Fiquei sem espaço novamente e acabei de chegar a 15 GB. O grupo de arquivos Data1 é o grupo de arquivos padrão. Por que o grupo de arquivos PRIMARY estaria crescendo repentinamente agora, quando não é o padrão? Cada um dos arquivos Data1 está configurado para 160 GB, então PRIMARY ainda é consideravelmente menor. Gostaria de saber se existe uma maneira de determinar por que ele está crescendo inesperadamente quando estava estagnado no passado.

Tenho um desenvolvedor que precisa criar trabalhos no SQL Server Agent. Eles fazem parte do grupo de segurança SQLDA e concedi esse grupo SQLAgentUserRole no msdb. Eles ainda têm acesso somente para visualização. Também preciso conceder a eles db_datawriter no msdb para que eles possam criar empregos?

Executando o SQLServer 2019. As permissões são gerenciadas por meio de grupos do Active Directory.

No final da tarde de ontem, adicionei 4 arquivos de dados tempdb aos 4 existentes, totalizando 8 (16 processadores no servidor SQL). Eu também os pré-aumentei para quase 100% do espaço disponível.

Hoje fui contatado por um de nossos desenvolvedores informando que ontem ela estava executando uma consulta que retornaria um conjunto inicial de resultados para a exibição do ssms em 3 a 5 segundos e toda a consulta seria concluída em 2 a 3 minutos. Hoje, a consulta leva 5 minutos para ser concluída e nenhum resultado é exibido em ssms por cerca de 2,5 minutos.

Não havia outras consultas em execução no momento. O uso da CPU foi baixo. Eu devolvi o servidor e não houve melhora. Agora, estou me perguntando se minha alteração no tempdb causou a diminuição no desempenho. Não consigo ver como, mas a paranóia está se instalando. Executei a consulta e monitorei o tempdb durante a execução e não parecia usar o tempdb, o que acho estranho, pois tem uma instrução GROUP BY que acredito usar tempdb. A tabela contém cerca de 22 milhões de linhas e a consulta retorna cerca de 7,8 milhões.

    select
      DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0) as Quarter
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,sum([MemberMonth]) as MemberQuarter
      ,[CurrentAge]
      ,[AgeAtTime] = CASE WHEN dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') < 0 THEN NULL ELSE dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') END
      ,AgeCategory = dbo.fn_CalculateAgeCatYrsOrdered(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)))
      ,[BirthDT]
      ,[ZipCD]
FROM [Partner].[dbo].[Membership]  
group by DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,[CurrentAge]
      ,[BirthDT]
      ,[ZipCD]

É provável que minha alteração tenha causado a diminuição do desempenho ou provavelmente é coincidência?

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

Eu tenho um procedimento armazenado que une três tabelas grandes (cerca de 20 milhões de registros cada) e carrega registros em uma tabela temporária. Os dados na tabela temporária são então mesclados em uma tabela existente de cerca de 60 milhões de registros.

O servidor ficou offline com uma mensagem de erro de:

AppDomain 2 (SSISDB.dbo.[runtime].1] está marcado para descarregamento devido à pressão da memória.

Depois de colocar o servidor online novamente, reiniciei os serviços SQL para limpar todos os processos que podem estar demorando. Começou o trabalho novamente e foi concluído sem problemas.

Estou executando o SQL Server 2019 com 128 GB de RAM. A memória máxima do servidor é 117964 MB em um servidor virtual de 64 bits. Alguém me disse no Gerenciador de Tarefas que o uso de memória estava em 94% e esse poderia ser o problema. Mas o SQL não pega toda a memória disponível e a mantém? Então parece que está funcionando como pretendido.

sp_WhoIsActiverevelou algumas consultas com status 'Suspended' e 'Awaiting_Command', mas não acredito que tenham tido muito impacto. 128 GB de ram parecem adequados, mas acho que é relativo ao trabalho que está sendo solicitado a fazer. Alguma idéia de como solucionar problemas ou evitar que aconteça novamente?

A unidade de dados para o servidor é de cerca de 1,6 TB. Os dois bancos de dados maiores na junção são 10 GB com 19 milhões de linhas e 13 GB com 20 milhões de linhas. Esses vão para uma tabela temporária e depois MERGEpara uma tabela de 26 GB com 53 milhões de linhas.

A concessão de memória solicitada é de 45 GB e a real é de 30 GB. O trabalho foi executado fora do horário comercial regular, portanto, não deveria haver consultas concorrentes, mas não posso confirmar 100% que alguém não estava trabalhando até tarde.

Eu notei que ele está tendo que fazer um CONVERT_IMPLICITbem. Isso tem um impacto significativo na memória necessária?

Link para o plano de consulta: https://www.brentozar.com/pastetheplan/?id=SyXaty7xK

Eu tenho um monte de tabelas sem chave primária. Alguns já possuem um índice não clusterizado composto pelo que seria a Chave Primária (ClaimID, LineNBR). As colunas usadas estão atualmente definidas para aceitar NULLs, então eu precisaria alterar isso. Existe valor em criar uma chave primária para isso e isso tornaria supérfluo o índice não clusterizado nas mesmas colunas? É melhor deixá-los apenas com o índice não clusterizado, excluir o índice não clusterizado e torná-lo uma chave primária ou adicionar a chave primária E manter o índice não clusterizado?

Eu tenho um banco de dados de produção e um banco de dados de desenvolvimento em dois servidores diferentes. Ambos os bancos de dados estão sendo alimentados a partir do mesmo data warehouse em um servidor de teste. Há uma série de trabalhos SQL automatizados que executam e preenchem tabelas em um banco de dados. Os trabalhos foram copiados do Dev para o Prod, então eles também devem ser os mesmos. No entanto, Prod está crescendo muito mais rápido que Dev.

Há aproximadamente 1 bilhão de linhas de dados no banco de dados. O total de arquivos de dados no Prod é cerca de 123 GB maior. O total de arquivos de índice é cerca de 31 GB maior. Eu sou novo nisso, mas eu esperaria que os dois bancos de dados fossem bastante semelhantes em tamanho. De qualquer forma, eu esperava que o Dev tivesse algum 'lixo' extra e potencialmente fosse o banco de dados maior.

Alguma idéia de como encontrar a fonte dessa diferença de tamanho? Posso aumentar o espaço em disco no Prod, se necessário, mas me indica que pode haver um problema que precisa ser resolvido. Eu gostaria de recuperar os 153 GB, se possível.

Eu sou reconhecidamente um novato quando se trata disso, mas eu verifiquei a pasta Index em cada ambiente e ambos parecem ter um Index. Eu verifiquei as propriedades e elas parecem as mesmas também. Mais Índice também causaria um aumento no tamanho dos arquivos de dados?

Estou executando o script IndexOptimize de Ola Hallengren em Prod e Dev e esperando que esteja lidando adequadamente com qualquer fragmentação significativa. Na verdade, não migrei nenhum dado de Dev para Prod. Temos um servidor de teste que hospeda os dados. Um conjunto de SSIS e procedimentos armazenados move os dados de teste para bancos de dados no Dev. Mais tarefas e procedimentos de armazenamento do SSIS preencheram as tabelas no Dev. O SSIS e os procedimentos armazenados são promovidos de Dev para Prod e são executados no Prod de forma independente. Os trabalhos de produção acessam o mesmo servidor de teste que o Dev.