Tenho duas tabelas que 99% do funcionamento nela é simples select. Raramente temos insert/delete e updates.
Há uma junção interna nessas tabelas e o plano de execução da consulta é index seek . 99% das vezes a consulta é executada muito rápido (cerca de 1 segundo). Mas há algumas vezes que a consulta demora 30 segundos para ser executada.
Não há nenhum padrão sobre quando isso aconteceu. Uma vez isso aconteceu em '1 de dezembro 3:17:20 am'. Uma vez em '8 de dezembro 22:13:43'. Uma vez aconteceu em '11 dez 16:50:43 pm'.
Como posso encontrar pistas de onde vem esse problema?
O SQL Server tem uma ferramenta para registrar todos os eventos?
Minha consulta tem apenas uma cláusula where simples: where id = @id.
O SQL Server 2014 não oferece muitas opções para investigar o baixo desempenho de uma consulta executada no passado. Você pode tentar verificar o log de erros para ver se algum evento catastrófico ocorreu perto de suas execuções de consulta lentas. Por exemplo, talvez tenha havido uma longa E/S que levou mais de 15 segundos. Você também pode verificar o evento estendido de integridade do sistema . Por exemplo, talvez tenha ocorrido uma longa espera de bloqueio (> 30 segundos) perto da execução lenta da consulta.
Parece que o que você realmente precisa é aumentar o log do aplicativo (você já sabe o segundo exato em que o erro ocorreu, então considere registrar as informações de desempenho quando a consulta for mais longa do que o esperado), comprar uma ferramenta de monitoramento do SQL Server, como o SentryOne, ou atualizar para o SQL Server 2017 e analisando as estatísticas de espera do repositório de consultas . Você deve saber que investigar um problema de desempenho que ocorreu há um ano será difícil mesmo com muitas dessas ferramentas.
Se for realmente apenas um simples
SELECT ... FROM tbl where tbl.id = @id(sem junções etc.), eu suponho que este é um problema de bloqueio. Muitas vezes, isso é o resultado de um aplicativo mal escrito:begin transactionCOMMITou ele recusa e o aplicativo faz umROLLBACKNormalmente o usuário clica rapidamente no botão e está tudo bem. Mas às vezes ele vai almoçar ou recebe uma ligação ou qualquer outra coisa e às vezes até o front-end / navegador do aplicativo trava, então agora você tem um longo bloqueio nesta linha. Quando o próprio usuário ou outro cara agora quer selecionar o mesmo ID, ele tem que esperar...
A resposta acima de Erik Darling é provavelmente o que você está procurando. Então não vou entrar em muitos detalhes aqui.
Você sabe que a consulta está usando o mesmo plano de execução todas as vezes? Já vi inúmeras vezes em que um aplicativo usa SQL dinâmico, poluindo o cache do plano. 95% das vezes o parâmetro usado retorna um punhado de linhas. O plano típico para isso é geralmente uma busca de índice. Em seguida, nos outros 5% do tempo, o parâmetro corresponde a muito mais linhas e o SQL faz uma verificação de índice.