Temos um aplicativo pronto para uso que usa um banco de dados Microsoft SQL. Dentro desta aplicação escolhemos vários critérios de seleção para cada relatório. Esse aplicativo executa esses relatórios.
Acredito que temos um problema de plano de consulta. O primeiro relatório que executamos todos os dias é muito rápido, 7 minutos. Qualquer relatório executado após o primeiro relatório leva mais de uma hora.
Todas as noites, executamos uma tarefa agendada que para e inicia o SQL Server Agent e o SQL Server. Existem aproximadamente 25 outros bancos de dados nesta única instância do SQL Server. Nenhum outro banco de dados tem problemas de desempenho, apenas o produto de prateleira que mencionei anteriormente.
Existe uma maneira de limpar todos os planos de consulta que o SQL Server possui atualmente na memória?
Como posso fazer isso sem impactar cerca de 30 usuários que dependem de outros bancos de dados no mesmo servidor?
Você fez duas perguntas aqui. Primeiro, você deseja saber se pode remover todos os planos armazenados na memória para uma instância do SQL. Isso é feito com DBCC FREEPROCCACHE como sugeriu Matt M.
A segunda pergunta que você fez é "Como posso fazer isso sem impactar cerca de 30 usuários que dependem de outros bancos de dados no mesmo servidor?". A resposta curta é "você não pode". Se você remover todos os planos, os outros usuários que dependem de planos na memória provavelmente sofrerão um impacto no desempenho.
A solução alternativa para isso requer alguma intervenção manual. Você pode usar DBCC FREEPROCCACHE para remover planos específicos, desde que tenha o plan_handle.
Pelo que você está descrevendo acima, parece um problema de plano, mas não tenho certeza de que remover planos seja a resposta. Eu apontaria você na direção da detecção de parâmetros antes de pensar em remover planos:
Link
Você deve ser capaz de otimizar as consultas em vez de brincar com DBCC FREEPROCCACHE de forma programada. Também aconselho que você gaste tempo analisando os eventos de espera de sua instância.
DBCC FREEPROCCACHE
Usando este comando, você pode limpar todo o cache do procedimento para um único comando. Definitivamente, leia a documentação antes de usar este comando. Leia a seção Observações algumas vezes.
A limpeza do cache de procedimentos fará com que os caches de procedimentos armazenados sejam recompilados no próximo uso. Isso pode afetar o desempenho. Use com cuidado!
Matt
Peço desculpas pela minha resposta anterior.
1) Adicione a opção WITH RECOMPILE à instrução CREATE PROCEDURE se você souber que sua consulta irá variar sempre que for executada a partir do procedimento armazenado. A opção WITH RECOMPILE impede a reutilização do plano de execução do procedimento armazenado, portanto, o SQL Server não armazena em cache um plano para esse procedimento e o procedimento é recompilado em tempo de execução. Usar a opção WITH RECOMPILE pode melhorar o desempenho se sua consulta variar toda vez que for executada a partir do procedimento armazenado porque, nesse caso, o plano de execução errado não será usado.
2) Você deve criar um guia de plano que use uma dica de consulta USE PLAN para cada tipo de consulta (todo tipo de solicitação de procedimento armazenado) para forçar o plano de execução.
Aqui está um artigo sobre o plano de execução que pode ajudar.