Query Store (#04) – Melhores práticas para habilitar o Query Store – Fabricio Lima

Query Store (#04) – Melhores práticas para habilitar o Query Store

26 de fevereiro de 201926 de fevereiro de 2019Fabrício LimaAdministração de Banco de Dados, Azure SQL Database, SQL Azure, Virtual PASS BR

Fala Pessoal,

Continuando a série sobre o Query Store, hoje vou dar algumas dicas para você que deseja habilitar o Query Store em produção.

Quando habilitamos o Query Store, ele vem com essas configurações por default:

Vamos falar um pouco sobre essas 4 opções de retenção do query store destacadas acima.

Max Size(MB)

Essa opção não tem a ver com impacto de performance do Query Store, mas sim com o volume de dados que você vai armazenar.

Na minha experiência de uso, 100 MB não dura muita coisa.

Normalmente utilizo 1 ou 2 GB nessa configuração para conseguir ter mais informações no Query Store.

A Erin Stellato cita em um de seus posts que já viu um Query Store de produção com 50 GB de informações.

Conseguimos monitorar o espaço utilizado pelo Query Store com a query abaixo:

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;

Depois vou fazer um post com um alerta para monitorar esse tamanho e enviar um e-mail.

No Azure SQL Database, hoje (26/02/2019), o default para uma base Basic é de 10 MB, para uma base Standard é de 100 MB e para uma base Premium é de 1 GB.

Query Store Capture Mode

Essa é a opção mais importante para se configurar, caso você tenha medo do Query Store impactar a performance do seu ambiente.

Se ela tiver configurada como ALL, o Query Store vai armazenar todas as queries que são executadas.

Em um ambiente grande ou com muita query Ad Hoc, o número de queries e planos que o Query Store vai armazenar será muito grande e isso pode gerar uma sobrecarga maior do que o comum no ambiente.

Mudando essa configuração para AUTO, o query store vai ignorar queries mais simples que não precisaríamos monitorar.

Quando utilizamos o modo AUTO, as regras exatas que definem se um plano vai ser gravado ou não pelo Query Store não são documentadas. Dessa forma, a microsoft pode alterar essa regra quando quiser.

Contudo, um caso dessa regra já consegui pegar em teste (dica de um MVP da Rússia) e mostrarei para vocês em um próximo post que falaremos só sobre esse modo de captura.

Opinião do Fabrício: Essa opção deveria vir como default configurada como AUTO e, se eu quisesse logar todas as execuções de queries, incluindo as mais simples, eu colocaria como ALL. A configuração default devia ser a menos impactante possível.

No Azure SQL Database, hoje (26/02/2019), o default já está vindo como AUTO.

Size Based Cleanup Mode

Essa opção vem por default como AUTO e devemos manter assim.

Com essa configuração o Query Store vai limpar automaticamente seus dados mais antigos e menos custosos quando chegar a 90% de consumo de espaço.

Vale alguns testes no futuro para explorar detalhes de como funciona essa opção. Existem alguns XEvents que nos mostram alguns detalhes de como isso funciona.

Stale Query Threshold (Days):

Essa configuração determina quantos dias você vai manter os dados no Query Store.

O Default é de 30 dias de armazenamento.

Minha sugestão é que se você não for ficar olhando e comparando 30 dias de informações do Query Store, que reduza esse valor para uma ou duas semanas.

Em ambientes com um número grande de Queries, muitas vezes nem conseguimos ver as informações do Query Store via gráficos, pois demora bastante:

Nesse caso, terá que pegar as informações para analisar via Query. Vamos ver mais detalhes de como fazer isso em outros posts.

Reduzindo o volume de informações armazenadas pode ajudar na velocidade para visualizar esses dados.

No Azure SQL Database, hoje (26/02/2019), o default também é 30 dias, exceto em uma database Basic, que vem configurada como 7 dias.

Data Flush Interval (Minutes)

Para reduzir o impacto de implantação, os dados do Query Store são armazenados em memória e posteriormente é realizado um flush assíncrono para persistir os dados no disco.

Esse flush ocorre a cada 15 minutos por default.

Essa configuração eu não altero.

Com ele você pode perder até 15 minutos de informações do Query Store em caso de problema, mas para quem hoje vive do Plan Cache que não tem dado persistido, estamos no lucro com o Query Store.

Statistics Collection Interval

O padrão desse cara é de 1 hora e não costumo alterar.

Essa configuração diz que o Query Store vai agrupar as informações de estatísticas a cada uma hora para cada plano e vai somar o consumo de CPU, Disco, Memória e etc…

Com esse agrupamento, a quantidade de dados persistida no Query Store é reduzida.

Você pode mudar o padrão de 60 minutos para 1, 5, 10, 15, 30, 60 ou 1440 minutos.

Reduzindo terá informações mais granulares. Contudo, se reduzir esse tempo para 30 minutos, vai armazenar o dobro de espaço que utilizaria com a configuração de 60 minutos, pois a cada 30 minutos um registro será gerado para cada plano na view sys.query_store_runtime_stats.

Repare no exemplo abaixo onde temos informações do plano 44 armazenadas 2 vezes na view sys.query_store_runtime_stats, 1 vez para o intervalo de 18:00 às 19:00 e a outra para o intervalo de 19:00 às 20:00.

Mudando para 30 minutos, se essa query for executada o tempo todo, teríamos 4 linhas ao invés de 2 linhas nessa view.

O Query Store já vem habilitado por default no Azure SQL Database e está ligado hoje para milhares de databases.

Logo, a microsoft está assumindo que essa feature é muito válida e que ela ajuda muito mais do que impacta os ambientes com algum overhead, concordam?

Segue abaixo as configurações default do Query Store para uma base Basic no Azure SQL Database:

Além das configurações de retenção do Query Store, seguem outras melhores práticas ao habilita-lo:

Utilize a versão mais recente do Management Studio

Com uma versão antiga do SSMS pode não visualizar todos os gráficos que foram desenvolvidos para a utilização do Query Store.

Então instale a mais recente para usar #ficaadica

Atualize o seu SQL Server para a última versão

Conforme mostrei nesse post, já tivemos alguns problemas identificados e corrigidos para o Query Store:

https://www.fabriciolima.net/blog/2019/02/13/query-store-3-lock-grande-ao-tentar-limpar-o-query-store/

Logo, se habilitar o Query Store em uma versão do SQL que ainda tem bug, estará correndo um risco maior de ser impactado ao habilitar a feature.

Trace Flags 7745 e 7752

Se seu banco é critico e o Query Store vai armazenar muita informação, vale a pena habilitar os 2 Trace Flags abaixo.

Esses 2 trace flags são suportados pela microsoft conforme pode ser verificado nesse link.

O que eles fazem?

Trace Flag 7745 – O Query Store por default faz um flush de informações da memória para o disco a cada 15 minutos. Caso você inicie uma operação de shutdown no SQL Server, por default, ele vai esperar o Query Store salvar as informações de memória no disco antes de realizar o Shutdown. Isso pode atrasar o shutdown dependendo do ambiente.

Imagina um failover do AlwaysOn ter que esperar o Query Store salvar as informações no disco?

Me ajuda ai SQL Server, não ligo de perder 15 minutos de informações do Query Store. Pode jogar fora essa informação.

Habilitando o Trace Flag 7745, pulamos essa etapa de salvar os dados em disco antes de um Shutdown.

Trace Flag 7752 – Esse Trace Flag faz o trabalho inverso do outro.

Quando o SQL Server inicia, ele carrega algumas informações internas do Query Store na memória. Em alguns casos isso pode demorar um pouco e as queries não conseguem ser executadas até a finalização desse processo.

Para que o Query Store faça essa subida de informações para a memória de forma assíncrona, sem impactar na execução das queries, temos que habilitar o Trace Flag 7752.

Nesse tempo, o Query Store fica em Read Only, mas para mim isso não tem problema. Posso deixar de ter informações no Query Store, o importante é impactar o mínimo possível o ambiente.

Espero que agora, com essas informações, você possa habilitar o Query Store com mais segurança e impactando menos o ambiente.

É isso ai pessoal, até o próximo post da série sobre o Query Store.

Algumas referências desse post:

Caso não tenha lido os posts anterioriores:

Gostou da dica?

Curta, comente, compartilhe com os coleguinhas…

Siga-nos no Linkedin, Youtube, Facebook e Instagram para receber dicas de leitura e eventos sobre SQL Server.

Abraços,

Fabrício Lima.

Microsoft Data Platform MVP

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006