Recentemente, eu estava solucionando um problema de desempenho estranho que afetava o ambiente de produção de um aplicativo, mas não nenhum dos ambientes inferiores. Consegui replicar o problema em sua forma mais simples com esta consulta:
SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products
TranslateStatusToActiveé uma UDF escalar muito simples, que basicamente apenas junta o valor dado a outra tabela e retorna 1 ou 0 com base em uma casedeclaração. Eu postaria o código, mas é uma função escrita pelo fornecedor e não estou particularmente interessado em ser processado hoje. (Sim, a lógica pode ser incorporada. Sim, corrige o problema de desempenho. Sim, convencemos o fornecedor a implementar a alteração. Essa não é minha pergunta.)
Ao executar em produção, a consulta levaria entre 10 e 20 segundos para retornar os resultados. Em desenvolvimento, a mesma consulta retorna em menos de 3 segundos. Os planos de execução são quase idênticos, exceto por mostrar que o tempo de CPU foi de cerca de 15.000 ms em produção e 3.000 ms em outros lugares.
Suspeitei que houvesse algumas diferenças ambientais, então configurei outro servidor que replicava as condições de produção o mais próximo possível: verifiquei o número de CPUs, a quantidade de memória fornecida ao SQL Server e o nível de patch específico (13.0.0.1). 4451) foram os mesmos.
Eu restaurei uma cópia do banco de dados de produção para este novo servidor sandbox e, para minha surpresa, a consulta foi executada tão rapidamente quanto no desenvolvimento. Mais uma vez, o plano e os dados eram idênticos, exceto pelo tempo extra de CPU. As esperas listadas no plano de execução eram do mesmo tipo e com poucos ms de distância entre si em todos os ambientes.
Sem saber o que fazer a seguir, habilitei optimize for ad hoc workloadsno servidor de produção. Isso corrigiu o problema de desempenho! Uma coisa, porém: nenhum dos outros ambientes tinha essa configuração habilitada. Eu estava limpando regularmente o procedimento e os caches do sistema em cada ambiente durante o teste, então não acho que foi o resultado de alterar uma configuração causando uma recompilação.
Perguntas
- O que poderia fazer com que a UDF fosse executada de forma tão diferente em cada ambiente, apesar do plano idêntico e dos sistemas quase idênticos?
- Por que o ambiente de produção precisava estar
optimize for ad hoc workloadshabilitado para funcionar tão bem quanto os outros ambientes, que não o habilitavam? - Existe alguma configuração que eu não pensei em verificar que pode causar uma diferença tão grande?
O desenvolvimento é compartilhado, enquanto a produção atualmente é usada apenas por este aplicativo. O uso da terceira caixa seria quase o mesmo da produção. Limpei praticamente todos os caches para os quais eles dão um DBCCcomando. O ambiente de desenvolvimento é usado regularmente como um sistema de treinamento, então estou bastante confiante de que não foi um problema de cache de plano.
A única diferença com a terceira caixa é que não há um aplicativo conectado a ela, mas houve pouco ou nenhum uso de aplicativo enquanto eu estava testando a função em produção, então a diferença foi, com base na minha experiência trabalhando nesse ambiente , insignificante. A única coisa que não pude fazer foi reiniciar o servidor de produção, mas a documentação da Microsoft afirma explicitamente que a habilitação optimize for ad hoc workloadsnão limpa ou afeta nenhum plano existente, então não vejo qual seria a diferença.
