Conhecendo um pouco do Query Store

Tempo de leitura: 5 minutos

Fala Pessoal,

Tudo beleza? ?

No post de hoje falaremos sobre uma feature muito interessante que está presente desde a versão 2016 do SQL Server o Query Store.

Mas o que é o Query Store?

Traduzindo para o Português (Repositório de Consultas). Em um breve resumo, esse “armazém de consultas” por assim dizer simplifica a identificação de problemas de performance no SQL Server. Este recurso proporciona o gerenciamento e mapeamento das consultas que passaram por “regressão de planos de execução” além de possuir uma interface gráfica muito útil na detecção de problemas.

Sabe aquela consulta que ficou lenta do nada? Esse recurso é um grande aliado para te ajudar a resolver e a descobrir o motivo.

Como habilitar o Query Store?

O Query Store vem por padrão desabilitado.

Para habilitar você tem duas formas, via interface gráfica no Object Explorer do seu Management Studio (SSMS), ou via T-SQL. Vamos exemplificar as duas formas.

1ª Habilitando via interface gráfica:

No Object Explorer clique com o botão direito sobre o seu banco de dados e depois em Propriedades.

Ao abrir as propriedades selecione a opção Query Store.

Dentro do menu de opções do Query Store clique em Operation Mode selecione a opção Read-Write.

Configure as opções de Monitoramento e Retenção de acordo com a sua preferência e Clique em OK.

O Fabricio Lima, tem uma série bem legal sobre o Query Store e também tem um post bem interessante sobre boas práticas na hora de habilitar esse recurso.

Recomendo a leitura de toda a série.

https://www.fabriciolima.net/blog/2019/02/26/query-store-04-melhores-praticas-para-habilitar-o-query-store/

2ª Habilitando o recurso via T-SQL

--Habilitando o Query Store:

USE master




ALTER DATABASE [StackOverflow2013]




SET QUERY_STORE = ON (

             OPERATION_MODE = READ_WRITE

             ,--Pode ser READ_WRITE ou READ_ONLY.

             CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 5)

             ,--informe o número de dias para reter dados no repositório de consultas.

             DATA_FLUSH_INTERVAL_SECONDS = 2000

             ,--Determina a frequência na qual os dados gravados no repositório de consultas serão mantidos no disco.

             MAX_STORAGE_SIZE_MB = 10

             ,INTERVAL_LENGTH_MINUTES = 10 --Você pode substituir o intervalo para agregar o tempo de execução da consulta (o padrão é 60 minutos).

             --Observe que valores arbitrários não são permitidos - você deve usar um dos seguintes: 1, 5, 10, 15, 30, 60 e 1440 minutos.

             );

--Habilitando o Query Store:

USE master

ALTER DATABASE [StackOverflow2013]

SET QUERY_STORE = ON (

OPERATION_MODE = READ_WRITE

,--Pode ser READ_WRITE ou READ_ONLY.

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 5)

,--informe o número de dias para reter dados no repositório de consultas.

DATA_FLUSH_INTERVAL_SECONDS = 2000

,--Determina a frequência na qual os dados gravados no repositório de consultas serão mantidos no disco.

MAX_STORAGE_SIZE_MB = 10

,INTERVAL_LENGTH_MINUTES = 10 --Você pode substituir o intervalo para agregar o tempo de execução da consulta (o padrão é 60 minutos).

--Observe que valores arbitrários não são permitidos - você deve usar um dos seguintes: 1, 5, 10, 15, 30, 60 e 1440 minutos.

);

Observação: Se o espaço máximo utilizado no Query Store estiver esgotado a propriedade Operation Mode será alterada automaticamente para READ_ONLY impossibilitando novas gravações.

Componentes do Query Store:

O Query Store possui basicamente dois repositórios:

um repositório de planos que armazena os planos de execução;
um repositório de estatísticas de tempo de execução que mantém informações relacionadas à execução da consulta, como CPU, IO, Memória entre outras métricas.

Observação.: Para reduzir o impacto no desempenho, o SQL Server grava por padrão as informações em cada um desses repositórios de forma assíncrona.

Visualizando informações do Query Store:

Usando o SSMS após habilitar o Query Store para a sua base de dados, você terá acesso a sete painéis:

Regressed Queries (Consultas regredidas): Este painel mostra as consultas que podem ter regredido devido a alterações no plano de execução. O painel permite visualizar as consultas, seus planos e há botões para forçar e desforçar um plano de consulta.
Overall Resource Consumption (Consumo geral de recursos): Este painel permite visualizar o consumo geral de recursos em quatro gráficos: duração, contagem de execução, tempo de CPU e leituras lógicas.
Top Resource Consuming Queries (Principais consultas de consumo de recursos): Este painel permite visualizar consultas no conjunto dos principais N consumidores de recursos (o padrão é 25) durante um período (o padrão é a última hora).
Queries With Forced Plans (Consultas com planos forçados): Este painel permite visualizar as consultas que tiveram o plano de execução forçado pelo Query Store.
Queries With High Variation (Consultas com alta variação): Este painel pode ser utilizado para identificar consultas com desempenho amplamente variável.
Query Wait Statistics (Estatísticas de Espera da Consulta): Este painel permite visualizar as principais consultas que podem estar gerando estatísticas de espera.
Tracked Queries (Consultas rastreadas): Este painel pode ser usado para monitorar uma consulta específica.

Para maiores informações acesse o link abaixo:

https://docs.microsoft.com/pt-br/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15

Algumas DMVs (Dynamic Management Views) e Procedures úteis no dia a dia do Query Store:

DMVS :

database_query_store_options: Retorna as opções do Repositório de Consultas para este banco de dados.
query_store_query: contém informações sobre a consulta e suas estatísticas gerais de execução de tempo de execução.
query_store_query_text: Armazena o texto T-SQL e o identificador SQL (Hash) da consulta.
query_context_settings: contém informações sobre a semântica que afeta as configurações de contexto associadas a uma consulta.
query_store_plan: contém informações do plano de execução associado a uma consulta.
query_store_runtime_stats: Retorna informações sobre as estatísticas de execução de tempo de execução para uma consulta.
query_store_runtime_stats_interval: Possui informações do horário de início e término de cada intervalo durante o tempo de execução de uma consulta que foi coletada.

PROCEDURES :

sp_query_store_force_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id : permite forçar um plano específico para uma consulta específica.
sp_query_store_unforce_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id : permite “desforçar” um plano específico para uma consulta específica.
sp_query_store_remove_plan [ @plan_id = ] plan_id : remove um único plano do Query Store.
sp_query_store_remove_query [ @query_id = ] query_id: remove a consulta, bem como todos os planos associados e estatísticas de tempo de execução do Query Store.
sp_query_store_reset_exec_stats [ @plan_id = ] plan_id : limpa as estatísticas de tempo de execução de um plano de consulta específico do Query Store.
sp_query_store_flush_db: libera a parte na memória dos dados do Query Store para o disco.