Este post tem o objetivo de mostrar ao DBA a importância da nova feature do SQL Server 2016: “Query Store”, de uma forma objetiva.
O Query Store é um repositório de consultas, onde registra em memória informações sobre o(s) plano(s) de execução de uma query, para depois persistir em disco.
Existem várias formas de análise de planos de execução para se obter uma melhor performance e desempenho na consulta de uma query.
Não vou entrar nos detalhes das outras features que auxiliam na análise dos planos de execução anterior ao Query Store. Porém, vale mencionar que devem ser avaliados e comparados os prós e os contras de cada uma delas, deixando claro que o Query Store não elimina o uso das outras features.
Uma query pode ter vários planos de execução por vários motivos, ex: um índice deletado, upgrade de versão, aumento nos registros de uma tabela, estatísticas desatualizadas, entre outros.
Exemplo clássico: uma consulta que normalmente gasta segundos para ser executada passa a demorar horas. Houve alguma mudança, certo? O que pode ser feito para se descobrir o motivo que levou a isso? É nessa hora que entra o Query Store: ele salva os planos de execução de uma query (que podem ser vários) no filegroup primário do banco de dados, no qual foi habilitado. (É habilitado por banco).
O Query Store possibilita fazer uma análise nos planos de execução gerados, permitindo ao DBA a opção de escolher qual o melhor plano para a execução de uma query, obtendo uma melhor performance, podendo, inclusive, fazer uma regressão de algum plano, caso seja necessário, ou seja, podemos forçar um plano de execução se necessário.
Lembrando que forçar um plano de execução não quer dizer que é a melhor solução, mas que em um determinado momento pode ser preciso, para tornar o processo mais ágil para a solução do problema, para depois analisar com mais calma a situação e corrigi-lá.
“Ao forçar um plano de execução, você está tratando o sintoma do problema e não a causa.”
Vantagens em utilizar o Query Store
- Auxilia no processo de migração de uma versão
- Mantém o histórico dos planos de execução, mesmo após um reinício do SQL Server
- Consome somente os recursos configurados
- Possui 19 extend events específicos para o Query Store
Como funciona a Arquitetura
Ao enviar uma consulta ao banco de dados, o Query Store utiliza-se duas tabelas em memória: A Plan Store que grava dados dos planos de execução da query após ela ser compilada e a Runtime Stats Store, que grava as informações do tempo de consulta após a execução da query.
Os dados que estão nessas duas tabelas são gravados em disco de uma forma assíncrona. O tempo que esses dados ficam em memória, para posteriormente persistir em disco, é configurado no Query Store. Por meio do Query Store Views, os dados são liberados para consulta, hora pelas tabelas em memória, hora pelo disco, por isso é assíncrono.
Contadores Perfmon
O desempenho do Query Store pode ser monitorado por 4 contadores adicionados no monitor do perfmon.
Habilitando o Query Store
· Acesse o SSMS, selecione o banco desejado -> clique em propriedades -> Query Store
· Selecione a opção Read Write.
· A opção Read Only, deixa o Query Store somente como leitura (Essa opção será detalhada mais abaixo).
· Pode ser habilitado pelo Transact-SQL:
ALTER DATABASE Sat580 SET QUERY_ STORE = ON
Após habilitado, atualize o banco ->Expanda a aba do mesmo, e o Query Store já aparece disponível.
Possuí 4 relatórios padrão:
Regressed Queries,
Overall Resource Consumption,
Top Resource Consuming Queries
Tracked Queries.
Ao habilitar o Query Store, aparecem várias opções para configurações, tais como:
· Data Flush Interval (Minute) – Tem como default o intervalo de 15 minutos.
Significa que a cada quinze minutos os planos de execução e as estatísicas de tempo de execução que estão nas tabelas Plan Store e Plan Runtime Store (que estão em memória), são gravados no disco. Esse intervalo pode ser alterado, para mais ou para menos, de acordo com a necessidade do ambiente.
· Statistics Colletion Interval – Tem como default o intervalo de 1 hora, está relacionado com a configuração do nível de granularidade das estatísticas de tempo de execução das consultas capturadas, que deseja obter.
· Max Size (MB) – Tamanho máximo do Query Store no Disco, por padrão vem habilitado com 100 MB. Ao atingir esse limite, o Query Store sai do modo read write e passa para o estado read only, ou seja, ele deixa de gravar novos planos de execução. Esse tamanho pode ser alterado de acordo com a necessidade do ambiente, chegando ao limite máximo de 1.0 TB, dependendo da versão do SQL Server 2016.
· Query Store Capture Mode – É a forma como o Query Store captura os planos de execução.
- Possui 3 opções: All(1), None(2) e Auto(2):
Size Based Cleanup Mode – O Query Store limpa os dados automaticamente, quando eles atingirem um limite de 90% do tamanho máximo configurado, apagando os dados mais antigos.
Opções: Auto e Off, por padrão vem habilitada como Off.
Análise (Analise) bem essa opção.
· Stale Query Treshold(Days) – Esse modo permite manter armazenados os dados no Query Store pelo período configurado(dias).
· Purg Query Data – Remove todos os dados do Query Store.
As opções de configurações acima citadas, podem ser configuradas pelo Transact-SQL .
- (1) vem habilitado como All, ou seja, captura todos os planos de execução para as querys.
- (2) Auto o Query Store captura somente consultas com planos de execução relevantes.
- (3) None – O Query store para de capturar novos planos de execução.
Essas configurações acima, podem ser feitas pelo comando Transact T-SQL abaixo:
ALTER DATABASE <Meetup>
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY =
(STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 3000,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 15,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 1000
);
Testando o Query Store:
Este exemplo mostra a opção de forçar um plano de execução, por meio do comando Force Plan.
Após habilitado o Query Store, vamos usar os seguintes comandos Transact-SQL:
Use Meetup
Go
UPDATE Ciclista
SET Nome = ‘Sulamita’,
Sobrenome = ‘Dantas’,
Email = ‘sdantas01@gmail.com’,
Sexo = ‘F’
WHERE Codigo = 3
SELECT *
FROM Ciclista
WHERE Codigo = 3
Depois de executada a consulta, clique do lado esquerdo na opção abaixo do Query Store e selecione um dos relatórios disponíveis. Ao selecionar o Top Resource Consuming Queries, do lado direito já aparece o plano de execução da consulta, com o plan id 1. O Query Store mostra de uma forma bem simples, o plano de execução gerado para essa consulta, conforme figura abaixo:
A execução dessa consulta gerou um plano de execução, com um table scan.
Agora vamos fazer com que a query tenha um plano de execução diferente do plano gerado acima.
Será criado um índice cluster:
CREATE CLUSTERED INDEX [IDX_Codigo] ON [dbo].[Ciclista]CREATE CLUSTERED INDEX [IDX_Codigo] ON [dbo].[Ciclista] ( [Codigo] ASC
)
Após a criação do índice, será feita uma nova consulta:
SELECT *
FROM Ciclista
WHERE Codigo = 3
Em seguida, execute o relatório Top Resource Consuming Queries novamente. Assim, o Query Store apresentará dois planos de execução para a query 1, o plan id 2 e o plan id 12.
Com a inclusão do índice, a query ganhou um novo plano de execução, gerando um Seek.
O que aconteceu?
A query 1 tinha um plano de execução que gerava um table scan, com a criação de um índice, ela ganhou um novo plano de execução, passando a gerar um seek, ou seja, houve uma uma mudança no plano de execução.
A query 1 agora possui dois planos de execução, e passou a usar o plano de execução id 12.
Por meio do Query Store, de uma forma simples, podemos fazer com que a query 1, volte a usar o plano de execução id 2, usando o botão force plan.
Obs: Lembrando que esse é um exemplo bem hipotético, somente para mostrar como forçar um plano de execução.
Pronto, agora a query já está usando o plan id 2, realizando um table scan, voltando ao seu plano de execução inicial.
Esse foi um exemplo bem simples, para que vocês possam se familiarizar com o Query Store e fazer análises mais complexas.
Possui 19 eventos Extend Events para o Query Store.
No site abaixo, temos as seguintes DMV’s de sistema, que podem ser utilizadas para uma maior análise de desempenho.
· Verificar o status atual do Query Store:
SELECT actual_state, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
· Últimas consultas executadas no banco de dados:
SELECT TOP 10 qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;
· Número de execuções para cada consulta:
SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;
· O número de consultas com o tempo de execução médio mais longo na última hora:
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
· Consultas com vários planos de execução:
WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(distinct plan_id) > 1
)
SELECT q.query_id, object_name (object_id) AS ContainingObject,
query_sql_text, plan_id, p.query_plan AS plan_xml,
p.last_compile_start_time, p.last_execution_time
FROM Query_MultPlans AS qm
JOIN sys.query_store_query AS q
ON qm.query_id = q.query_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id;
Conclusão:
Assim, o dba, por meio da interface gráfica ou DMV’s, pode acessar os planos de execução gerados pelo Query Store, descobrindo as querys lentas e pode fazer uma análise da melhor performance para determinada(s) query(s).
Explore essa feature 😉