Perguntas[sql-server-2019](dba)

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?

Tenho usado o SQL Server Agent do SQL Server 2016 com configuração e script inferiores nos últimos 3 anos. Estava/está funcionando bem.

Mas, agora, estou migrando tudo para o SQL Server 2019, mas o mesmo comando não funciona.

Então, estou me perguntando se há algo que preciso modificar (em termos de script) porque estou usando o SQL Server 2019.

Quando uso o SQL Server Agent para executar outro script do Powershell (que executa algo dentro do Powershell), ele funciona.

Quando executo o Powershell agora, recebo esta mensagem de erro:

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 uma editora Standard 2019 que atua como distribuidora própria, com cerca de 150 assinantes em todo o mundo. São 4 publicações, das quais apenas 2 estão ativas. As alterações de dados são muito limitadas (cerca de 5 gravações por dia). Os assinantes são todos da edição SQL Express.

Meu principal problema aqui é que muitas vezes algum agente de distribuição decide que não deseja iniciar corretamente; ou seja, o trabalho fica preso na execução do agente em um loop de nova tentativa. Quando olho para o monitor de replicação, a mensagem diz para procurar no histórico de trabalhos do agente. O histórico de tarefas do agente aponta para o monitor de replicação.

Já tentei aumentar o histórico e o detalhamento da saída, e enviar informações para o arquivo, para os agentes que estavam apresentando o problema, mas parece que isso só começa quando o agente passa da fase de inicialização.

Lendo on-line, encontrei um artigo antigo sobre o esgotamento do heap da área de trabalho, mas também parece que se refere ao WS anterior a 2016 (e estou executando o WS 2019), então não tenho certeza se isso realmente se aplica.

Pesquisas adicionais no Google não produziram nada de valor. Você conhece um método para depurar/solucionar esses problemas, exceto um ticket para a MS ou algum recurso para tentar seguir?

Edite conforme solicitado, aqui está uma cópia e colagem das mensagens que posso ver:

Monitor de replicação: Mensagens de erro:

• O agente 'PublisherName-PublicationName-SubscriberName-AgentID' está tentando novamente após um erro. 74 tentativas tentadas. Consulte o histórico de trabalhos do agente na pasta Trabalhos para obter mais detalhes.

Resultados de

SELECT jh.*FROM msdb.dbo.sysjobhistory jh
INNER JOIN distribution1.dbo.MSdistribution_agents da
   ON da.job_id = jh.job_id
WHERE da.name = ''
ORDER BY jh.instance_id DESC

(captura de tela apenas da iteração mais recente)

Após a CU mais recente do SQL Server 2019, KB5037331, recebo a seguinte mensagem repetidamente em alguns dos meus logs do SqlServer Server: "Chamada de API assíncrona longa: a imparcialidade do agendamento do agendador pode ser impactada por uma invocação de API assíncrona excedendo inesperadamente [grandes 8 dígitos número] senhora."

Vi nas notas de atualização a seguinte alteração:

"Adiciona a seguinte mensagem de log quando altas latências de E/S são detectadas no Bufferpool Lazy Writer (chamada de sistema ntdll!ZwWriteFile) devido a um problema de desempenho no armazenamento subjacente:

AVISO Chamada de API assíncrona longa: a imparcialidade do agendamento do agendador pode ser afetada por uma chamada de API assíncrona que exceda inesperadamente xxx ms."

O problema é que não sei o que devo fazer com essas informações além da solução básica de problemas de E/S. O que preciso matar para fazer esta mensagem parar? E como identifico quais arquivos/objetos estão envolvidos no problema de E/S para resolver o problema subjacente?

Crie o banco de dados mais simples possível:

CREATE DATABASE DatabaseCheck

Adicione este banco de dados a um Grupo de Disponibilidade, onde o secundário não é legível

Na execução primária:

DBCC CHECKDB ('DatabaseCheck') WITH EXTENDED_LOGICAL_CHECKS

É bem-sucedido:

CHECKDB encontrou 0 erros de alocação e 0 erros de consistência no banco de dados 'DatabaseCheck'. Execução do DBCC concluída. Se o DBCC imprimir mensagens de erro, entre em contato com o administrador do sistema.

Execute o mesmo comando DBCC no secundário. Ele falha mesmo que nenhum erro tenha sido encontrado:

CHECKDB encontrou 0 erros de alocação e 0 erros de consistência no banco de dados 'DatabaseCheck'. Msg 0, Nível 11, Estado 0, Linha 1 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

• Remover COM EXTENDED_LOGICAL_CHECKSfaz com que o erro desapareça
• Tornar a réplica secundária legível faz com que o erro desapareça
• Fazer um failover e verificar o novo primário faz com que o erro desapareça. Verificar o novo secundário mostra o erro novamente.

Alguma ideia do que se trata? A verificação da documentação DBCC CHECKDB não diz nada sobre EXTENDED_LOGICAL_CHECKS não ser compatível com réplica secundária não legível. Parece um bug no próprio comando para mim.

Uma coisa que notei na saída do comando é que ele verifica estatísticas como aqui:

Integridade verificada das estatísticas 'sys.sysrscols.clst'. Integridade verificada das estatísticas 'sys.sysrowsets.clust'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000002_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000003_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000006_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000004_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000005_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000008_00000005'.

Eles estão ausentes quando executados no secundário

Reproduzido em:

Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64) 21 de setembro de 2023 17:28:44 Copyright (C) 2019 Microsoft Corporation Developer Edition (64 bits) no Windows Server 2019 Standard 10.0 (Build 17763 :)

Editar:

A única mensagem no log de erros é esta:

DBCC CHECKDB (DatabaseCheck) executado por Domain\user encontrou 0 erros e reparou 0 erros. Tempo decorrido: 0 horas 0 minutos 0 segundos. O instantâneo do banco de dados interno tem ponto de divisão LSN = 00000034:000001b1:0001 e primeiro LSN = 00000034:000001af:0002.

Não há dumps recentes em sys.dm_server_memory_dumps

Tenho que criar o DACPAC a partir do banco de dados de produção. Gostaria de fazer isso por meio do Visual Studio 2022 (SQL Server Object Explorer e "Extract - Data-tier application"). É possível bloquear algum processo/consulta ou utilizar mais CPU/memória para fazer isso?

Tenho os seguintes JSONs que gostaria de combinar. No meu JSON tenho a parte 'kenmerken' onde gostaria de adicionar mais alguns itens. Achei que isso poderia ser feito usando JSON_MODIFY. Porém quando eu uso o comando abaixo, ele irá substituir os itens 1, 2 e 3. E quando eu incluir 'append' antes de $.kenmerken, ele só mostrará os novos. Como posso conseguir ter ambos no objeto kenmerken?

DECLARE @FirstJSON NVARCHAR(MAX) = N'
{
    "message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
    "volgnummer": 61000233530024,
    "debug": null,
    "kenmerken": {
        "item 1": 1,
        "item 2": 1,
        "item 3": 1
    }
}
';

DECLARE @KenmerkenToAdd NVARCHAR(MAX) = N'
{
    "Item 4": false,
    "Item 5": false
}
';

SET @FirstJSON = JSON_MODIFY(
                               @FirstJSON,
                               ' $.kenmerken',
                               JSON_QUERY(@KenmerkenToAdd)
                            );

SELECT @FirstJSON;

Darei:

{
"message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
"volgnummer": 61000233530024,
"debug": null,
"kenmerken": {
   "Item 4": false,
   "Item 5": false
   }
}

Onde eu gostaria de ter:

{
    "message id": "B673A8E4-3652-4544-A02D-BA9726BD71ED",
    "volgnummer": 61000233530024,
    "debug": null,
    "kenmerken": {
       "Item 1": 1,
       "Item 2": 1,
       "Item 3": 1,
       "Item 4": false,
       "Item 5": false
       }
    }

Estou coçando a cabeça, relendo a ajuda da MSFT e ainda não consigo entender a diferença entre forwarded_fetch_count e forwarded_record_count em sys.dm_db_index_operative_stats e sys.dm_db_index_physical_stats. Deixe-me ilustrar meu problema de compreensão dos pontos de vista com o exemplo abaixo.

Eu executei as seguintes consultas:

;with heaps as ( 
select 
    DB_NAME(DB_ID()) dbname, object_name ( p.object_id ) objname, sum(row_count) row_count,
    DB_ID() database_id, p.object_id objectid
from 
    sys.dm_db_partition_stats p
    join sys.objects o on o.object_id = p.object_id 
WHERE 
    index_id = 0 and o.is_ms_shipped = 0 --and row_count > 0
    group by p.object_id ) 
select 
    h.*, 
    forwarded_fetch_count
from heaps h
    cross apply sys.dm_db_index_operational_stats(database_id, objectid, 0, null) ps
    WHERE forwarded_fetch_count > 0 ORDER BY forwarded_fetch_count DESC¨

E

SELECT page_count, OBJECT_NAME(ps.object_id)
,avg_record_size_in_bytes
,avg_page_space_used_in_percent
,forwarded_record_count 
FROM sys.dm_db_index_physical_stats(db_id('your_db_name'), NULL,NULL, NULL, 'DETAILED') AS ps
WHERE forwarded_record_count IS NOT NULL AND forwarded_record_count > 0
GO

Ambas as consultas retornaram listas diferentes de tabelas em um único banco de dados que estou ajustando. A primeira consulta retornou tabelas variando de 1.000 a 100.000 buscas encaminhadas por tabela, a segunda consulta retornou qualquer coisa entre 10 e 60.000 contagens de registros encaminhados para outro conjunto de tabelas.

Para consertar a fita adesiva, reconstruí as tabelas em questão. No entanto, no monitor de desempenho do Windows, ainda vejo muitos registros encaminhados/s (o gráfico geralmente chega a 100). Ao executar sp_blitzfirst @seconds = 30, sou alertado sobre muitas buscas encaminhadas/s. Uma vez que o alerta é geral (ou seja, não relacionado a nenhum banco de dados), outros 9 alertas da mesma instrução mencionam: "Forwarded Fetches/Sec High: TempDB Object". E de acordo com a contagem de buscas encaminhadas, há dez ou cem vezes mais buscas encaminhadas no TempDB do que fora do TempDB.

Por último, mas não menos importante, o ISV implementou fortemente gatilhos (que relaciono com as buscas encaminhadas do TempDB).

Minhas perguntas:

• Qual é a diferença entre as duas colunas em duas visualizações?
• Ao lidar com banco de dados lento devido a registros encaminhados em um heap, qual devo usar? (Eu sei que a ajuda da MSFT menciona forwarded_record_count em dm_db_index_physical_stats relacionado especificamente a heaps, ainda não deixando isso mais claro para mim).
• Posso identificar o que no TempDB está causando essas buscas encaminhadas?

Eu tenho um problema que se manifesta da seguinte maneira, meu conhecimento do TempDB não cobre isso (ainda):

• Uma consulta analítica, que roda no SSMS por cerca de 200ms, continua rodando no servidor SQL, quando iniciada a partir do aplicativo, por mais de 60 segundos - isso acontece apenas ocasionalmente, na maioria das vezes o problema não está presente
• A fila de consultas executáveis/suspensas pode crescer até dezenas de consultas do mesmo texto de consulta, sendo uma delas consultas SELECT um bloqueador de cabeça bloqueando outros SELECTs idênticos
• as esperas mais dominantes são SOS_SCHEDULER_YIELD e PAGELATCH_UP para as consultas específicas do mesmo texto e valores de parâmetro, quando as filas executáveis/suspensas começam a crescer significativamente em comparação com a linha de base
• quando o problema ocorre, as dezenas de consultas na fila têm os mesmos valores literais de parâmetros (timestamp de início de turno, id de funcionário e área de produção)
• as queries que estão sendo suspensas (durante o tempo do snapshot do nosso monitoramento - DBA Dash) têm a espera PAGELATCH_UP como a mais dominante e estão esperando a página GAM em tempdb
• a consulta não está vazando para o tempdb quando verifico o plano de execução no SSMS, usando os parâmetros da consulta que continua se acumulando nas filas executáveis/suspensas

Configuração do servidor, do banco de dados e do tráfego do banco de dados:

• 4 a 6 núcleos (o problema ocorreu independentemente em dois servidores diferentes com contagem de núcleo diferente)
• 4 - 6 arquivos TempDB de tamanho uniforme
• 40 GB de RAM atribuídos à instância
• no banco de dados específico, o RCSI + Snapshot está ativado (portanto, o TempDB está sendo atingido)
• nenhuma exclusão está ocorrendo nas duas tabelas nas quais a consulta é executada, apenas INSERTs e SELECTs - apenas 1 linha por vez é inserida
• SELECTs estão atingindo geralmente os registros mais novos (aqueles inseridos recentemente)
• o servidor está fazendo geralmente 400 - 700 solicitações em lote/s; Quando ocorre o problema, atinge um pico de até 1500, criando alta carga de E/S + CPU em comparação com a operação normal
• os dados na tabela têm distribuição mais ou menos uniforme, ou seja, nenhum funcionário em nenhum turno para determinada área de produção tem significativamente mais registros do que os outros
• a captura de tela em anexo é uma tabela que mostra o instantâneo do DBA Dash em um momento em que o problema se manifesta totalmente
• tempDB, apesar do snapshot RCSI+ não crescer muito, são alguns GBs em um drive de 60GB (status estável por vários meses e depois de alguns episódios do que descrevo aqui)
• todas as consultas SELECT na captura de tela têm espera PAGELATCH_UP, aproximadamente metade delas tem as seguintes informações adicionais:
  • tipo de recurso de espera: PAGE
  • recurso de espera: 2:3:2 (acho que o significado é: banco de dados tempdb, 3º arquivo em tempdb, 2ª página que estamos esperando)
  • arquivo de espera: PRIMÁRIO | temp2
  • tipo de página: GAM
  • Esperar para compilar: Falso

Além do esforço óbvio para chegar à causa raiz e corrigi-la permanentemente, minhas principais perguntas seriam:

• como é que as consultas SELECT podem acessar páginas GAM pesadamente em tempdb?
• se eu admitir que SELECT pode tocar na página GAM com frequência, o que pode impedir o progresso da consulta (bloqueador de cabeça) (ou seja, ler a página GAM e disponibilizá-la imediatamente para outras pessoas)?

Apontar para qualquer recurso / conselho de estudo relevante seria muito apreciado

plano de consulta aqui

EDIT: Para responder ao comentário de John, gostaria de acrescentar o seguinte:

• CTFP: 30
• MAXDOP: 4 (definido para ambos os servidores SQL, embora um tenha 6 núcleos, o outro tenha 4)
• existem 2 instâncias por SQL VM - esta instância em que ocorreu o problema, depois outra instância de "escritório", que coloca uma carga insignificante na VM do servidor SQL
• Tabelas

ManualPanelEntries : 6933089 registros, 1221 MB de dados, 2591 MB de índices - de fato, os índices consomem mais espaço do que os próprios dados

Pesos : 3108486 registros, 178 MB de dados, 170 MB de índices

• Todo o banco de dados: 125 GB

============

O que descobri depois de escrever a postagem é que também o bloqueador de cabeça (um dos muitos SELECTs paralelos idênticos) espera pelo GAM, segunda página de um arquivo tempdb específico. Como se algo mais (armazenamento de versão?) Bloqueasse uma página GAM específica, impedindo que outras consultas usando o TempDB continuassem.