Temos o SQL Server 2022 Enterprise (16.0.4131.2) na VM do Windows 2022 (8 núcleos de vCPU) e há um problema com o Query Store em um dos bancos de dados.
Abaixo está a sequência de etapas que posso seguir para reproduzir o problema em um banco de dados específico e as observações que fiz sobre ele:
1 - O servidor está em estado inativo com apenas uma pequena carga gerada pelo usuário e utilização insignificante da CPU como ponto de partida.
2 - O Query Store está desligado e vazio para o banco de dados problemático, conforme mostrado na consulta abaixo.
3 - Quando mudo o Query Store para o modo READ_WRITE, tudo fica perfeitamente bem por cerca de 10 a 15 minutos. No entanto, após esse período, a utilização da CPU do servidor aumenta repentinamente para 15-20%. Veja a captura de tela do Activity Monitor abaixo.
As estatísticas de espera mostram um grande aumento nas esperas SOS_SCHEDULLER_YELD e PREEMPTIVE_OS_QUERYREGISTRY - elas saltam para os principais tipos de espera do servidor nesse período.
A utilização da CPU gerada parece ser gerada por um processo interno em segundo plano (SPID < 51) relacionado a uma atividade do Service Broker no banco de dados do usuário problemático. O recurso Service Broker não está ativado no próprio banco de dados.
4 - A alta utilização da CPU, as esperas mencionadas acima e a atividade SPID mencionada acima são apresentadas continuamente até que o Query Store no banco de dados seja desligado.
Não há sinais de qualquer "auto-recuperação" potencial, se eu mantiver o Query Store ativado. Uma vez desligado, a utilização da CPU diminui, as esperas desaparecem e o SPID para de gerar a carga e fica "hibernando". Mudar o Query Store para o modo READ_ONLY não ajuda. O Query Store deve ser desativado para eliminar o problema.
Não há nenhuma quantidade significativa de dados no Repositório de Consultas quando o problema é acionado – veja abaixo (consultas feitas após o QS ser desativado).
Outras observações relacionadas
Se eu ativar (READ_WRITE) o Query Store e mantê-lo funcionando por apenas um tempo, mas não o suficiente para acionar o problema, mude para READ_ONLY, o problema não será acionado no período de 10 a 15 minutos conforme descrito acima. No entanto, ele será acionado quase instantaneamente se eu mudar o Query Store para READ_WRITE a qualquer momento posteriormente.
Quando a carga do usuário aumenta no momento em que o problema é acionado, pode-se observar que o uso de consultas no banco de dados que levam alguns milissegundos para serem processadas nas condições padrão começa a ficar mais lento e também mostra esperas PREEMPTIVE_OS_QUERYREGISTRY não desprezíveis (conforme sp_WhoIsActive mostrado abaixo). Essas consultas definitivamente não fazem consultas ao registro do Windows.
Temos o mesmo banco de dados (ou seja, a mesma estrutura) em várias outras instâncias do SQL Server com o mesmo padrão de carregamento (e volumes de dados e consultas ainda muito maiores) e o Query Store funciona perfeitamente bem lá. Parece que algo está quebrado no Query Store apenas para este banco de dados específico.
Pode ser importante que o Repositório de Consultas desse banco de dados esteja originalmente cheio e o "processo de limpeza baseado em tamanho" tenha sido acionado e o SQL Server (provavelmente) reiniciado durante esse período. Desconfio que haja algo quebrado no QS daquele banco de dados em relação a isso... Tentei também a procedure sys.sp_query_store_consistency_check , mas sem sucesso.
Depois que o problema é acionado e "suspenso" ao desligar o QS, quando o QS é ligado novamente (sem limpeza prévia), parece que o problema é acionado novamente quase instantaneamente.
O Query Store fica completamente inutilizável para o banco de dados nessas condições. Alguma idéia de como consertar isso?
ATUALIZAÇÃO 01/08/2024
Com base na dica do comentário de Paul White, fiz alguns experimentos com a opção de escopo do banco de dados CE_FEEDBACK e parece ser o verdadeiro gatilho. O comportamento é descrito abaixo:
Tenho o Query Store não limpo de tentativas anteriores (apenas algumas centenas de consultas de captura) como ponto de partida.
- Quando defino CE_FEEDBACK = OFF e coloco o QS em READ_WRITE, o problema não é acionado.
- Quando defino CE_FEEDBACK = ON e coloco o QS em READ_WRITE, o problema é acionado quase instantaneamente.
- Quando defino CE_FEEDBACK = OFF e coloco o QS em READ_WRITE, o problema não é acionado. Então (com o QS em execução) eu alterno CE_FEEDBACK = ON. O problema é novamente desencadeado instantaneamente. O interessante também é que quando eu configurei CE_FEEDBACK = OFF novamente, não é suficiente para resolver o problema. O QS também deve ser desligado para pará-lo.
TIPO DE CARGA DE TRABALHO
Como o problema parece estar relacionado também ao tipo de carga de trabalho, tentarei descrevê-lo aqui, pois o padrão é bastante direto. É quase 99% ad hoc com os seguintes padrões de consulta:
- Cerca de 85% das consultas executadas durante o experimento são uma simples junção à variável da tabela. O número de linhas passadas para a variável varia de +/- 10 a +/- 500. A tabela do banco de dados de origem varia entre as consultas, mas o padrão do plano de execução é o mesmo mostrado abaixo.
- O restante da carga de trabalho são consultas que seguem o padrão abaixo. O problema com esta parte é que devido a uma alta variabilidade dos parâmetros (o número de parâmetros muda frequentemente), então é capaz de preencher um armazenamento de consultas de 2GB em poucos dias com consultas "únicas", mas não é o caso para o cenário atual
Posso imaginar que provavelmente a primeira parte da carga de trabalho possa ser problemática para o CE_FEEDBACK.
O problema está no mecanismo de feedback do CE baseado na carga de trabalho e nas consultas desse banco de dados, juntamente com a forma como a integração do CE Feedback QDS é projetada.
Olhando para o rastreamento, a pilha quente é de fato QDS, mas na
RunCEFeedbackAnalysisqual há uma morte de 1000 cortes, dosGetStmtquais parece ser executado para obter todos os itens no QDS para esse banco de dados.Acredito que um fato pouco conhecido é que o UCS (que utiliza o corretor de serviços nos bastidores) é usado para execução de tarefas para itens QDS, e é por isso que você vê que o encadeamento se parece com um encadeamento do corretor de serviços, embora não seja. Você também pode ver isso no rastreamento acima em
CSbTask::BeginTaskStart.Como sabemos quais são as configurações do escopo do banco de dados e com as atualizações, você já confirmou que desligar o CE Feedback realmente interrompe o problema, que é o que a pilha também sugere.
Por enquanto, você pode deixar o CE Feedback desativado para não encontrar esse problema naquele banco de dados ou ir para um nível de compatibilidade que não o tenha disponível. É difícil dizer, sem uma investigação profunda da carga de trabalho do banco de dados e do QDS, se isso é algo que poderia ser concluído com mais eficiência no SQL ou se é um caso extremo para configurações de carga de trabalho/QDS do banco de dados que não se ajustam bem, o que está definitivamente fora do escopo deste site.
Se você tiver um acordo, poderá abrir um tíquete de suporte com a Microsoft, embora não haja mudanças ou resultados imediatos e, se houver, será alterado em um futuro distante.