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.
