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
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.
O cerne do seu problema é coberto pela documentação em Recomendações para reduzir a contenção de alocação no banco de dados tempdb do SQL Server . Você está executando o SQL Server 2019, o que significa que verá a contenção nas páginas GAM em vez de SGAM.
Suas operações de classificação e hash podem não estar derramando (embora várias consultas em sua captura de tela mostrem gravações ), mas o SQL Server ainda precisa alocar estruturas (arquivos de trabalho e tabelas de trabalho) para oferecer suporte à classificação e hash, algumas das quais são necessárias caso haja um derrame em tempo de execução. Se você fizer o suficiente dessas operações com rapidez suficiente, poderá ver a contenção de trava de página GAM.
As esperas de trinco geralmente são curtas, mas é possível encontrar comboios de trinco , resultando em esperas (muito) mais longas ocasionalmente.
Conforme observado na documentação vinculada acima, a solução principal é adicionar arquivos ao tempdb até que a contenção desapareça ou seja reduzida a um nível aceitável.
Provavelmente não relacionado ao problema específico aqui, você também tem a opção de habilitar metadados com otimização de memória para tempdb no SQL Server 2019. Essa opção tem suas desvantagens e foi objeto de alguns bugs.
O SQL Server 2022 apresenta atualizações simultâneas de GAM e SGAM , que substituem a necessidade de uma
UPtrava (atualização) por umaSHtrava (compartilhada). Isso reduz consideravelmente o escopo para problemas dessa natureza.As consultas de seleção geralmente não bloqueiam outras seleções. Eu suspeito que eles podem estar esperando por bloqueios de compilação.
Se você estiver obtendo muitas consultas em execução ao mesmo tempo com os mesmos valores de parâmetro, pode ser um problema que pode ser corrigido no lado do aplicativo. Use cache. Verifique se não há problemas com o código que fazem com que as consultas sejam executadas com mais frequência do que o necessário. Por exemplo, chamando dentro de um loop em vez de fora.
Como você está vendo a contenção do tempdb GAM, minha primeira resposta seria verificar se estou em um nível de patch recente. Houve melhorias significativas para ajudar na contenção de tempdb em várias CUs. Em seguida, procuraria dobrar os arquivos temporários do banco de dados. Se você tiver 6 núcleos com 6 arquivos, dobre para 12. Pode estar fora das diretrizes padrão para o número de arquivos tempdb, mas se você estiver vendo contenção de GAM de tempdb, mais arquivos ajudarão.
Eu criei o DBA Dash. Espero que você esteja achando útil ?