Uma vez em algumas semanas ou meses, mas sempre no mesmo dia da semana, uma consulta de um procedimento armazenado usado em um trabalho em lote diário pode ficar visivelmente travada e ser executada por cerca de 200 minutos até que o aplicativo elimine sua conexão. Esta é uma consulta de livro didático muito trivial que une 5 tabelas e calcula um sum()em um campo para atribuir a uma variável, portanto, não há sentido em fornecê-la aqui. O número total de registros a serem somados é normalmente de 1 a 2 dezenas. Esta consulta não tem parâmetros, portanto, a detecção de parâmetros está (felizmente) fora de questão.
Eu olhei para várias coisas:
- Não há usuários online no banco de dados. Ele é usado apenas como um despejo de dados.
- Não há trabalho que seja executado simultaneamente. A última atividade no banco de dados é um backup tranlog que é concluído cerca de 10 minutos antes. A próxima atividade começa após cerca de 2 horas, é um backup completo que é concluído bem após alguns minutos (enquanto esta consulta ainda está enlouquecendo).
Existem os seguintes bloqueios durante a execução desta consulta, todos no status GRANT:
OBJECTbloquear em uma das tabelas solicitadas noSch-SmodoHOBT.BULK_OPERATIONbloquear em outra mesa noSmodoOBJECTlock em mais uma mesa solicitada noIXmodo
A consulta tem (NOLOCK)dicas sobre todas as tabelas unidas. Está em RUNNABLEestado e de acordo com sp_whoisactive, que roda a cada 15 min no servidor, a contagem de CPU da consulta em questão está aumentando constantemente. A CPU aumenta para cerca de 1.000 vezes a execução da consulta autônoma. O IO da consulta de sp_whoisactiveé quase o mesmo como se eu a executasse de forma autônoma, mas a contagem de leituras é enorme, 10.000 vezes mais do que a execução autônoma. Parece que a consulta está em um loop infinito. Mas quando chego ao escritório pela manhã, o trabalho é executado novamente em alguns segundos.
O plano relatado por
sp_whoisactiveé o mesmo que autônomo. Existe uma dica do otimizador para criação de um índice quando eu o executo, mas como o tempo autônomo da consulta é em torno de 200ms, não estou preocupado com essa dica. Implementá-lo parece reduzir mais 10ms desse tempo.Não há erros relatados por
DBCC CHECKTABLEem nenhuma das tabelas envolvidas ouDBCC CHECKDB.
O único ponto de suspeita que posso ter sobre o plano é que ele tem dois ícones de paralelismo. Mas a adição option (maxdop 1)apenas aumenta sua execução de 200 para 400ms.
Existem 5 tabelas envolvidas, todas de tamanhos diferentes, a maior tem cerca de 1 * 10 ^ 6 registros, não muito grande. Atualizamos estatísticas e índices diariamente às 4h e o trabalho é executado à meia-noite antes que grandes volumes de dados sejam importados. O trabalho em si adiciona apenas 1 a 10 registros.
Forçar o plano de execução armazenado para a consulta travada leva ao mesmo resultado: consulta travada, CPU e E/S enormes. Deixar o otimizador fazer sua mágica ou forçar o plano que eu obtenho autônomo (são os mesmos) é concluído em uma fração de segundo.
...
Para encurtar a história: tive permissão para executar um rastreamento noturno agendado na produção durante o tempo de execução "normal" deste trabalho para tentar solucionar isso. Não sabemos quando será a próxima ocorrência, apenas esperamos que siga o padrão que observamos - a cada poucas semanas.
Minha pergunta de um milhão de dólares é quais eventos devo rastrear? Sou apenas um desenvolvedor SQL e não tenho as habilidades típicas de DBA, então não saberia quais entranhas do servidor SQL deveriam ser expostas por meio de um rastreamento para pegar o culpado em flagrante.
Com base no seu esboço completo, acredito que você pode estar enfrentando um problema com as estatísticas da sua mesa.
As tabelas atualizam automaticamente suas estatísticas apenas quando certos limites de atualizações de linha são ultrapassados, no caso de qualquer tabela com mais de 500 linhas, é necessário que 500+20% das linhas sejam alteradas. Por exemplo, sua tabela de milhões de linhas requer 200.500 alterações de linha antes de atualizar as estatísticas.
Um índice
REBUILDatualizará as estatísticas em uma tabela (REORGANIZEnão).Acho que suas tabelas estão crescendo com o tempo e eventualmente invalidando suas estatísticas, mas não o suficiente para acionar uma atualização automática. No entanto, seu trabalho de reindexação não está acionando um real
REBUILDpara as tabelas relevantes até que sua consulta comece a ter um desempenho ruim devido às últimas alterações do início do dia. Se suas alterações forem inconsistentes e com incrementos pequenos o suficiente, isso tornaria a degradação do desempenho consistente e repentina. Seu trabalho de reindexação corrige o problema depois que a consulta foi mal executada e tudo parece bem na manhã seguinte.Coisas para verificar para confirmar:
SELECT nome AS index_name ,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated DE sys.indexes WHERE OBJECT_ID = OBJECT_ID('SchemaName.TableName')Se você estiver executando o SQL Server 2008 R2 Service Pack 1 ou posterior, terá a opção de habilitar o sinalizador de rastreamento documentado 2371 para atualizar dinamicamente as estatísticas com mais frequência:
O novo comportamento é ativado por padrão (ou seja, sem o sinalizador de rastreamento) do SQL Server 2016 em diante, para bancos de dados no nível de compatibilidade >= 130.
Você tentou executar a consulta com uma das seguintes dicas de consulta :
KEEP PLANouKEEPFIXED PLAN?Depois de ter o plano de execução correto,
KEEPFIXED PLANnunca altere o plano, a menos que o esquema subjacente seja alterado ou sp_recompile seja executado no procedimento armazenado.KEEP PLANapenas relaxa o limite de recompilação (devido a mudanças nas estatísticas).Observe que
KEEPFIXED PLANpode, depois de muito tempo, manter um plano de execução insuficiente para cardinalidades alteradas.Se as estatísticas das tabelas envolvidas na consulta não mudam muito ao longo do tempo (ou seja, as cardinalidades das tabelas e as estatísticas não mudam muito), e o problema é real, posso ver se usar um plano fixo é uma solução de longo prazo simplesmente tentando por um longo tempo. Ele quebra novamente em algum momento (o plano começa a ser ruim para os dados subjacentes) e eu começaria a procurar outra solução. Se não quebrar, então bom o suficiente para mim.