Considere o uso de DBCC DROPCLEANBUFFERS antes de executar seu teste. Isso fará o que você quiser, como uma queda única do cache. Você precisará executá-lo no início de cada teste.
Eu sei que essa é uma solicitação engraçada, mas gostaria de desativar o cache de página em nosso banco de dados de desenvolvimento. Eu acredito que seria muito mais fácil ver o impacto de vários ajustes que são feitos, e estou mais curioso sobre o tempo para recuperar páginas não armazenadas em cache, não tanto em como o MSSQL pode extrair dados do cache com eficiência.
No entanto, isso deve ser feito no nível do banco de dados, portanto, não posso simplesmente configurar o cache de memória do servidor para um nível absurdamente baixo (e isso tem a chance de eliminar os caches de índice que prefiro não fazer, e nosso sistema de desenvolvimento tem outros bancos de dados que eu prefiro não tocar).
Os comandos para liberar o banco de dados AFAIK irá liberá-lo em todos os bancos de dados, e eu prefiro não fazer isso em nosso banco de dados de desenvolvimento.
Não é realmente uma coisa obrigatória, mas o benchmarking é bastante difícil de acertar quando posso colocar todo o meu banco de dados de desenvolvimento no cache de páginas.
Se o banco de dados de teste não estiver sendo usado por mais nada, você poderá desanexar e, em seguida, reconectar o banco de dados imediatamente. (Você pode fazer isso com algumas chamadas de procedimento armazenado do sistema, sp_detach_db e sp_attach_db.) Isso jogaria todas as páginas fora do cache e haveria poucas, ou nenhuma, página de dados em cache até você começar a executar consultas.
Claro, se você está comparando algo mais complexo do que uma única consulta e outra pessoa executa uma grande consulta em algum outro banco de dados em seu sistema de desenvolvimento compartilhado, seus resultados (medidos em segundos para executar a consulta) podem ser distorcidos algumas ou todas as suas páginas podem ser descartadas do cache de dados, fazendo com que sua consulta seja executada por mais tempo do que seria de outra forma.
Se seu objetivo for comparar consultas únicas (ou um conjunto de consultas ou um procedimento armazenado) com o objetivo de otimizá-las e você não tiver seu próprio servidor, sugiro que se concentre em leituras de páginas e não na hora demora para executar a consulta.
Se você tiver um servidor lento (desenvolvimento) e um servidor rápido (produção), com dados e código idênticos, uma consulta poderá ser executada em 10 segundos no servidor lento e em 1 segundo no servidor rápido. Ambos lerão o mesmo número de páginas. (Você encontrará uma pequena variação, mas geralmente é da ordem de menos de 1%.) Se você melhorar a consulta para ler menos páginas no servidor lento, ele lerá menos páginas no servidor rápido. Uma consulta que lê menos páginas é quase sempre o melhor desempenho.
Você pode obter informações de leitura de página do Profiler, que pode assistir a qualquer programa cliente. (Ao usar o Profiler, certifique-se de filtrar todas as conexões / bancos de dados / eventos / colunas que você não precisa e que você não esteja impactando o desempenho do servidor que você está criando.)
(Isto costumava ser mais difícil quando os sistemas dev eram single-core e o servidor poderia ser dual ou quad core. Os planos no sistema dev seriam diferentes para grandes consultas, que são aquelas que você geralmente deseja otimizar.)
Outro ângulo para o trabalho é observar leituras lógicas e leituras físicas de uma consulta ao executá-las no SSMS. Procure a opção "definir estatísticas de E / S em" na caixa de diálogo das opções de consulta. Está sob a seção "avançada". Isso funciona melhor para consultas simples ou procedimentos armazenados simples. Para coisas complicadas que tocam em muitas tabelas, fica feio. Nesse caso, tente encontrar alguns dos piores criminosos e isole as partes realmente ruins, depois examine essas partes em detalhes.
Você desejará minimizar as leituras lógicas. Se você minimizar as leituras lógicas, as leituras físicas também deverão ser descartadas quando o servidor precisar extrair dados do disco. Além disso, em situações em que os dados que você precisa são armazenados em cache, a minimização de leituras lógicas fará com que uma consulta seja executada mais rapidamente.