Direi desde o início que minha pergunta/problema é semelhante a esta anterior, mas como não tenho certeza se a causa ou a informação inicial é a mesma, decidi postar minha pergunta com mais alguns detalhes.
Questão em mãos:
- em uma hora estranha (perto do final do dia útil) uma instância de produção começa a se comportar de forma irregular:
- CPU alta para a instância (de uma linha de base de ~ 30%, foi quase o dobro e ainda estava crescendo)
- aumento do número de transações/s (embora o carregamento do aplicativo não tenha sofrido nenhuma alteração)
- aumento do número de sessões inativas
- eventos de bloqueio estranhos entre sessões que nunca exibiram esse comportamento (mesmo as sessões não confirmadas de leitura estavam causando bloqueio)
- as principais esperas pelo intervalo foram sem travamento de página em 1º lugar, com bloqueios em 2º lugar
Investigação inicial:
- usando sp_whoIsActive vimos que uma consulta executada por nossa ferramenta de monitoramento resolve ficar extremamente lenta e consumir muita CPU, algo que não acontecia antes;
- seu nível de isolamento foi lido como não confirmado;
- analisamos o plano e vimos números malucos: StatementEstRows="3.86846e+010" com cerca de 150 TB de dados estimados a serem retornados
- suspeitamos que um recurso de monitoramento de consultas da ferramenta de monitoramento fosse a causa, então desativamos o recurso (também abrimos um ticket com nosso provedor para verificar se eles estão cientes de algum problema)
- a partir desse primeiro evento, aconteceu mais algumas vezes, a cada vez que matamos a sessão, tudo volta ao normal;
- percebemos que a consulta é extremamente semelhante a uma das consultas usadas pelo MS em BOL para monitoramento do Query Store - Consultas que recentemente regrediram no desempenho (comparando diferentes pontos no tempo)
- executamos a mesma consulta manualmente e vemos o mesmo comportamento (CPU usado cada vez maior, aumentando as esperas de trava, bloqueios inesperados etc.)
Pergunta culpada:
Select qt.query_sql_text,
q.query_id,
qt.query_text_id,
rs1.runtime_stats_id AS runtime_stats_id_1,
interval_1 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi1.start_time),
p1.plan_id AS plan_1,
rs1.avg_duration AS avg_duration_1,
rs2.avg_duration AS avg_duration_2,
p2.plan_id AS plan_2,
interval_2 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi2.start_time),
rs2.runtime_stats_id AS runtime_stats_id_2
From sys.query_store_query_text AS qt
Inner Join sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
Inner Join sys.query_store_plan AS p1
ON q.query_id = p1.query_id
Inner Join sys.query_store_runtime_stats AS rs1
ON p1.plan_id = rs1.plan_id
Inner Join sys.query_store_runtime_stats_interval AS rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
Inner Join sys.query_store_plan AS p2
ON q.query_id = p2.query_id
Inner Join sys.query_store_runtime_stats AS rs2
ON p2.plan_id = rs2.plan_id
Inner Join sys.query_store_runtime_stats_interval AS rsi2
ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
Where rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
AND rsi2.start_time > rsi1.start_time
AND p1.plan_id <> p2.plan_id
AND rs2.avg_duration > rs1.avg_duration * 2
Order By q.query_id, rsi1.start_time, rsi2.start_time
Configurações e informações:
- SQL Server 2016 SP1 CU4 Enterprise em um cluster do Windows Server 2012R2
- Repositório de consultas habilitado e configurado como padrão (nenhuma configuração alterada)
- banco de dados importado de uma instância do SQL 2005 (e ainda no nível de compatibilidade 100)
Observação empírica:
- devido a estatísticas extremamente malucas, pegamos todos os objetos *plan_persist** usados no plano mal estimado (nenhum plano real ainda, porque a consulta nunca foi concluída) e verificamos as estatísticas, alguns dos índices usados no plano não tinham estatísticas (DBCC SHOWSTATISTICS não retornou nada, select from sys.stats mostrou a função NULL stats_date() para alguns índices
Solução rápida e suja:
- criar manualmente estatísticas ausentes em objetos do sistema relacionados ao Query Store ou
- forçar a execução da consulta usando o novo CE (traceflag) - que também criará/atualizará as estatísticas necessárias ou
- altere o nível de compatibilidade do banco de dados para 130 (para que, por padrão, use o novo CE)
Então, minha verdadeira pergunta seria:
Por que uma consulta no Query Store causaria problemas de desempenho em toda a instância? Estamos em um território de bugs com o Query Store?
PS: Vou fazer upload de alguns arquivos (print screens, IO stats e planos) em breve.
Arquivos adicionados no Dropbox .
Plano 1 - plano inicial estimado maluco em produção
Plano 2 - plano real, CE antigo, em um ambiente de teste (mesmo comportamento, mesmas estatísticas malucas)
Plano 3 - plano real, novo CE, em um ambiente de teste
