Grandes backups de log devido à ativação do querystore

A inspeção em um depurador revela que o Query Store executa a reorganização de índice em suas tabelas internas após qualquer limpeza, seja ela acionada por tamanho ou idade.

Isso faz algum sentido, já que o propósito da limpeza é liberar espaço. Usuários não podem executar manutenção nas tabelas QDS internas. Também não podemos desabilitar bloqueios de página, o que seria uma maneira de evitar a execução de reorganização.

Então, não há nada que você possa fazer para desabilitar esse comportamento de reorganização em 2019; é apenas o que o Query Store faz. Isso explica o crescimento do log.

O SQL Server verifica se a limpeza baseada em tamanho ou tempo é necessária sempre que ele persiste os dados do Query Store no disco. O limite de 90% para limpeza baseada em tamanho é armazenado como um valor variável em vez de uma constante codificada. Isso sugere que o servidor pode variar a porcentagem sob algumas condições.

A limpeza baseada em tempo pode ser impedida de ser disparada com o sinalizador de rastreamento global não documentado 7748. Não parece haver um recurso semelhante para limpeza baseada em tamanho.

O SQL Server parece sempre tentar a limpeza baseada em tempo primeiro (se habilitada, e desde que o sinalizador de rastreamento não esteja definido) independentemente da condição de disparo. Isso pode ocorrer porque a limpeza baseada em tempo envolve muito menos sobrecarga (sem decidir quais consultas remover com base em consultas relativamente caras executadas nas tabelas internas).

Curiosamente, o SQL Server executa uma limpeza baseada em tempo a cada INTERVAL_LENGTH_MINUTES. Mesmo que não encontre nada para fazer, a reorganização do índice ainda é realizada depois.

Há um sinalizador de rastreamento não documentado para impedir a reorganização do índice, mas ele está presente apenas no SQL Server 2022, não no SQL Server 2019. Mesmo se você estivesse executando o SQL Server 2022, seria necessário entrar em contato com o Suporte da Microsoft para obter autorização para usar o novo sinalizador.

Enquanto isso, você pode tentar desabilitar a limpeza baseada em tempo com o sinalizador de rastreamento não documentado 7748 (ou defini-lo STALE_QUERY_THRESHOLD_DAYScomo zero) para operações normais, permitindo que ela seja executada durante um período em que você possa tolerar a reorganização do índice.

2 GB são suficientes para armazenar 45 dias de dados do Query Store?

O ideal é evitar a limpeza baseada em tamanho, conforme esta postagem do blog de Kendra Little . A limpeza baseada em tamanho do Query Store causa problemas de desempenho - como evitá-la.

Você pode tentar dobrar o tamanho máximo de armazenamento e ver se a frequência de reindexação cai?

Você também pode monitorá-lo com query_store_size_retention_cleanup_startedo XE e verificar se ele se correlaciona com oquery_store_index_rebuild_started

Depois de algumas pesquisas de tentativa e erro, encontrei uma solução alternativa para o problema.
Se definido Stale_Query_Threshold_Dayscomo 30, o processo de reconstrução é acionado apenas algumas vezes por dia. Assim que o coloco de volta em 45, ele é executado quase a cada 20 minutos.

Não consegui encontrar uma explicação para esse problema, mas a solução alternativa funciona.