Eu tenho um procedimento armazenado que leva cerca de 15 segundos para ser executado pela primeira vez e as execuções subsequentes levam de 1 a 2 segundos. Se eu esperar por uma hora e executá-lo novamente, leva 15 segundos novamente.
Suponho que esteja usando os dados armazenados em cache no pool de buffers nas execuções subsequentes, enquanto na primeira vez ele precisa carregar dados do disco para o pool de buffers. Estou tentando ajustar esse procedimento armazenado, mas após a primeira execução não consigo testar minhas alterações, pois leva apenas 1 a 2 segundos.
Eu sei que posso usar o DBCC DROPCLEANBUFFERS
comando para liberar o cache e executar meu procedimento armazenado, mas não tenho permissão para limpar o cache no meu trabalho. Eu tentei WITH RECOMPILE
também, mas isso só cria um novo plano, mas ainda usa os dados em cache. Existe outra maneira de forçar o procedimento armazenado a não usar os dados em cache?
Não. Salve a CPU e aguarde as estatísticas do plano de execução real onde você está vendo a duração de 15 segundos. Em seguida, trabalhe para minimizar a CPU e as leituras. Se você vir que leituras X = 15 segundos de esperas de PAGEIOLATCH, poderá obter uma estimativa razoável do impacto da redução de leituras.
O importante é descobrir e corrigir as consultas que estão causando a rotatividade do buffer pool. Sua consulta provavelmente é pelo menos parcialmente culpada, mas você precisa descobrir por que os dados dessa consulta não estão presos no cache. Podem ser outras consultas, podem precisar de mais memória, ou melhor compactação, ou para evitar varreduras de tabela, etc.
Quatro comentários. Primeiro, se você precisar fazer isso, providencie um backup do banco de dados restaurado em um sistema de teste onde você possa usar DBCC DROPCLEANBUFFERS.
Segundo, use leituras lógicas em vez de leituras físicas. Eu não gosto de depender de leituras físicas e apenas duração para otimização de consulta na maioria dos casos. Se você se concentrar nas leituras lógicas e reduzi-las, as leituras físicas normalmente seguirão. Em alguns casos, você realmente deve ler muitos dados e reduzir as leituras lógicas não é uma opção. Às vezes, uma verificação do intestino pode ajudar. Se você estiver fazendo 10.000 leituras de páginas lógicas em uma tabela de 1.000 páginas para obter 1 registro, algo está muito errado. (Vi coisas ruins como essa.) Se você tem um relatório com todos os dados, então 1.000 leituras lógicas em uma tabela de 1.000 páginas é ótimo.
Terceiro, meça o desempenho. Você pode usar as seguintes instruções SET na conexão usada para testar a consulta. Ele fornecerá o tempo de CPU usado e o uso de IO para cada tabela. Esses SETs precisam ser executados apenas uma vez. Eles permanecem ativos até que a conexão seja fechada ou sejam DESLIGADOS. Isso é bom para 1 ou algumas consultas, mas será muito barulhento para alguns códigos.
Será algo como abaixo.
Se tudo o que você precisa é de um resumo do desempenho total, algo como o fluxo pode ser bom o suficiente. O SQL Server 2014 ou posterior é necessário; caso contrário, o tempo decorrido precisa ser substituído por algo que funcione (por exemplo, datediff). BTW, o request_id é incluído apenas no caso de MARS estar ativado. Não deixe as instruções SET acima habilitadas para esta, a menos que você queira um bom exemplo de ruído. (Os resultados aqui e acima são para consultas diferentes e não serão correspondentes.)
Quarto, se o código já estiver otimizado, pode haver outros problemas. Se você tiver 100 GB de dados ativos e apenas 8 GB de RAM... (nos últimos 20 anos, lembro-me de um caso em que foi HW. A consulta também era péssima, mas um problema de vCPU a levou ao limite. Um problema de desempenho provavelmente será um problema de otimização com sua consulta. No entanto, isso seria um tópico separado.)