Query lenta por mudança no plano de execução Query plan regression (stats, parameter sniffing)
ID da causa db-plan-flip · Responsável principal Infraestrutura de banco de dados (Equipe de infraestrutura) · Também envolvidos Desenvolvimento do servidor (Equipe de desenvolvimento)
O código continua igual, mas, se o BD muda a forma de executar a mesma query (plano de execução), uma query que levava 2 ms ontem passa a levar centenas de ms hoje.
Por quê Atualização automática de estatísticas, reinício do BD ou mudança na distribuição dos dados levam o BD a montar um novo plano de execução → Efeito É escolhido um plano que não usa índice, a mesma query fica de dezenas a centenas de vezes mais lenta e as conexões ficam presas → Na tela Sem nenhum deploy, o carregamento de um recurso específico fica lento de repente, e outras requisições também esperam
Responsável principal Infraestrutura de banco de dados (Equipe de infraestrutura) · Também envolvidos Desenvolvimento do servidor (Equipe de desenvolvimento)
O que fazer (Equipe de desenvolvimento)
Separar as queries cujo número de resultados varia muito conforme o valor ou avaliar hints de plano, projetar queries que com certeza usem índice.
O que fazer (Equipe de infraestrutura)
Monitorar queries lentas e o histórico de planos de execução, fixar os planos bons (Query Store do SQL Server etc.), controlar o horário de atualização das estatísticas.
No gráfico
Degrau a partir de um momento · Tempo médio de execução por query
Onde olhar
Coletar periodicamente o tempo médio de queries de mesmo formato e acompanhar a tendência: AVG_TIMER_WAIT do events_statements_summary_by_digest no MySQL, mean_exec_time do pg_stat_statements no PostgreSQL (mean_time na 12 e anteriores). Comparar os planos de execução de antes e depois da lentidão com EXPLAIN ou auto_explain do PostgreSQL; no SQL Server, com a tela de consultas regredidas (Regressed Queries) do Query Store
Confirma se
Sem nenhum deploy, o tempo médio de uma query sobe em degrau, dezenas de vezes, num momento que coincide com uma atualização de estatísticas ou um reinício do BD, e o plano de execução mudou
Descarta se
Plano de execução igual, mas mais lento: crescimento dos dados, espera por lock (db-hot-row) ou disco
Como verificar
Ferramentas de infra (sem precisar do código do jogo)
Saiba mais
O SQL Server reutiliza o plano montado para o primeiro valor recebido (parameter sniffing). Um plano montado para um personagem novo com poucos itens fica muito lento quando é usado para um personagem antigo com dezenas de milhares de itens, e o caso contrário também é comum. Às vezes o reinício apaga o plano, tudo volta ao normal e depois piora de novo.
Fontes
Query Processing Architecture GuideMicrosoft SQL Server Parameter sniffing: o plano de execução é montado de acordo com os valores de parâmetro recebidos na compilação ou recompilação
Parameter Sensitive Plan OptimizationMicrosoft SQL Server Com distribuição de dados desigual, um único plano em cache não serve para todos os valores de parâmetro
Monitor performance by using the Query StoreMicrosoft SQL Server Mudanças em estatísticas, schema ou índices alteram o plano, e o cache de planos guarda só o mais recente; forçar um plano no Query Store fixa o plano bom, e a tela de consultas regredidas (Regressed Queries) compara as queries que ficaram lentas e seus planos
Statement Summary TablesMySQL events_statements_summary_by_digest: COUNT_STAR e AVG_TIMER_WAIT (tempo médio) por formato de query