Estou executando o Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) em uma VM de 4 vCPU com max degree of parallelismdefinido como 2e cost threshold for parallelismdefinido como 50.
De manhã, ao tentar exibir um Plano de Execução Estimado para uma consulta SELECT TOP 100, tenho grandes esperas e a operação para renderizar o plano estimado leva minutos, geralmente na faixa de 5 a 7 minutos. Novamente, esta não é a execução real da consulta, este é apenas o processo para exibir um Plano de Execução Estimado .
sp_WhoIsActivemostrará PAGEIOLATCH_SHesperas ou LATCH_EX [ACCESS_METHODS_DATASET_PARENT]esperas e quando executo o script WaitingTasks.sql de Paul Randal durante a operação, ele mostra CXPACKETesperas com os threads de trabalho mostrando PAGEIOLATCH_SHesperas:
*campo de descrição do recurso =exchangeEvent id=Port5f6069e600 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=1 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen
Os threads de trabalho parecem estar trazendo a statstabela inteira para a memória (como os números de página, bem como os números de página subsequentes mostrados do ponto de consulta de Paul Randal de volta à chave agrupada da statstabela). Uma vez que o plano volta, é basicamente instantâneo pelo resto do dia, mesmo depois de ver a maior parte do statsatrito da tabela do cache com apenas vários registros restantes (que suponho que foram puxados devido a operações de busca de consultas semelhantes).
Eu esperaria esse comportamento inicial se a consulta estivesse realmente sendo executada com um plano que usasse operadores SCAN, mas por que está fazendo isso ao avaliar planos de execução apenas para chegar a um operador SEEK, conforme mostrado no plano vinculado acima? O que posso fazer (além de executar esta declaração antes do horário comercial para que meus dados sejam armazenados em cache adequadamente) para ajudar a melhorar o desempenho aqui? Estou assumindo que um par de índices de cobertura seria benéfico, mas eles realmente garantiriam alguma mudança no comportamento? Eu tenho que trabalhar com algumas limitações da janela de armazenamento e manutenção aqui, e a consulta em si é gerada a partir de uma solução de fornecedor, portanto, quaisquer outras sugestões (além de uma melhor indexação) seriam bem-vindas neste momento.
Parece que sua solicitação de um plano de execução real acionou atualizações de estatísticas. Como você mencionou que isso acontece de manhã, imagino que haja um processo noturno que faça muitas modificações nas tabelas envolvidas?
Assim, o SQL Server usa as estatísticas para criar o plano, atingiu o limite de modificação e executa atualizações automáticas de estatísticas como parte da operação.
No XML do plano estimado que você compartilhou, vejo essas datas de atualização próximas das estatísticas desta manhã:
Se estas são tabelas muito grandes e ocupadas (parece provável com base nas porcentagens de amostragem), não é muito surpreendente que as atualizações de estatísticas estejam consumindo muita potência.
Quando vejo longos tempos de planejamento estimados no SSMS, é um dos seguintes em ordem de probabilidade:
Para sua situação, a resposta é quase certamente que o SQL Server está atualizando ou criando estatísticas. Existem algumas pistas: o tamanho do plano de consulta é pequeno, o plano de consulta é relativamente simples com baixo custo e a CPU de compilação é significativamente menor que o tempo de compilação:
O novo colaborador Josh Darnell também apontou uma boa pista com a última hora atualizada das estatísticas no XML.
O SQL Server 2019 apresenta um novo tipo de espera, WAIT_ON_SYNC_STATISTICS_REFRESH , para quando as consultas estiverem aguardando atualizações de estatísticas. É muito mais fácil diagnosticar esse problema nessa versão. Até lá, você só terá que confiar em técnicas indiretas.
As soluções alternativas incluem atualizar as estatísticas durante um período de manutenção ou habilitar a atualização automática de estatísticas assíncronas para o banco de dados. Por favor, entenda todas as ramificações dessa opção antes de alterá-la. Os planos de consulta serão criados antes das estatísticas serem atualizadas em vez de após as atualizações das estatísticas. Para algumas cargas de trabalho, isso pode ser uma grande vitória. Para outros, pode fazer mais mal do que bem.