Tive degradação de desempenho em nosso servidor de banco de dados de produção que é o SQL Server 2016 e salvei a consulta de longa duração usando o profiler como você pode ver na imagem a seguir:
Tempo de CPU = 65141, Leituras = 36474959, Duração = 114546
Salvei o arquivo de rastreamento e abri mais tarde, então não tenho certeza se a duração é milissegundo ou microssegundo.
Mas, agora, quando executo a mesma consulta no prod fora do horário comercial, a consulta é executada rapidamente e "definir IO de estatísticas em" mostra apenas algumas páginas de dados sendo lidas, minha suposição era o tempo de CPU e as leituras de uma consulta devem ser constantes em relação a tempo de execução de uma consulta, apesar da duração, alguma idéia? Devo considerar esta consulta como ineficiente com base nos números?
@DanGuzman apontou corretamente para o excelente artigo de Sommarskog , que você deve ler.
Você está comparando SET STATISTICS (qualquer coisa) ao Profiler. Não faça isso. Não funciona. O Profiler e o Extended Events capturam números relativamente precisos. SET STATISTICS (qualquer coisa) NÃO - como um único exemplo, não captura o custo da função!
Você está tentando combinar planos feitos em momentos diferentes, provavelmente com estatísticas diferentes. Pare com isso.
Vá em frente e atualize as estatísticas de TODAS as tabelas envolvidas na consulta suspeita, de preferência com FULLSCAN, se preocupe em ver se isso muda o comportamento, e antes de seus testes "executar eu mesmo".
Veja a consulta em si.