Query rodando drasticamente diferente através do driver sql sql vs Sql Management Studio

Question

Nossa aplicação usa o driver sql .net, e as consultas acabam parecendo algo assim no profiler:

sp_executesql N'query where @param = ?, and param2 = ?', param, param2, param3, etc

Ao copiar e colar a consulta do Profiler no studio de gerenciamento do SQL Server, a consulta será executada em menos de um minuto, em comparação a 15 a 20 minutos em execução no aplicativo.

Tanto quanto eu posso dizer que ambos estão usando o mesmo plano de execução, então não tenho certeza do que seria diferente.

Para aumentar a estranheza, também temos um servidor sql de teste, que é basicamente uma cópia do servidor de produção. Em nosso ambiente de teste com o mesmo código e principalmente os mesmos dados (alguns dias fora da data de produção), a consulta é executada em menos de um minuto em nosso aplicativo, bem como no sql server management studio. Mais uma vez, o profiler está capturando exatamente o mesmo plano de execução para todos eles.

A única coisa que descobri que a consulta é executada corretamente é executar sp_updatestats no banco de dados, que executamos todas as manhãs às 5:00 da manhã. O que é estranho é que às 7:00 da manhã a consulta continuará lenta novamente. Se eu executar sp_updatestats novamente, a consulta será concluída em menos de um minuto. Mais uma vez, todos os planos de execução parecem iguais.

Eu devo estar perdendo alguma coisa. Alguma idéia?

    

Answer 1

Sua consulta envolve uma tabela com uma coluna datetime ascendente ou datetime2 e um dos parâmetros é datetime ou datetime2, que normalmente está procurando por um valor recente?

Seus comentários sobre o comportamento após a atualização das estatísticas sugerem que você está enfrentando o problema com estatísticas frequentemente desatualizadas que Gail Shaw descreve aqui: link

Como Gail menciona, a resolução mais direta é uma atualização mais frequente das estatísticas. O ideal é segmentar as atualizações mais frequentes das estatísticas que precisam delas apenas - consulte Atualizar estatísticas .

No caso de tabelas muito grandes, um índice filtrado também pode ser útil, dependendo do tamanho da tabela e dos padrões de atualização e leitura.

    

Answer 2

Atualmente, no processo de tentar atualizar minhas habilidades de SQL, leve isso em consideração. Mas talvez este seja um problema Parâmetro Sniffing ?:

Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed. By “first time”, I really mean whenever SQL Server is forced to compile or recompile a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.

- link

Como eu disse no meu aviso, tenho muito a aprender aqui, mas se eu entendi isso corretamente, se esse for o caso, acho que se você passar OPTION RECOMPILE para a consulta e o problema desaparecer, isso pode ter sido sua problema.