Andy M, para atingir esse nível de tunning no SQL, você deve usar o Administrador de Recursos. Pode ser um problema, já que está disponível somente no data center (expensivo) & Edições corporativas.
Se você não puder pagar por essas edições, há algumas coisas que você pode considerar:
GERAL
- Divida seu banco de dados em 2: um para o aplicativo de roteamento e outro para o aplicativo visualizador. Dessa forma, você pode ajustá-los separadamente para obter o melhor desempenho;
- Considere a aquisição de HDs para seu servidor (SSD, se puder pagar) e divida os arquivos de dados / índice entre eles para aumentar a E / S (assim, a capacidade de resposta);
APP DE ROTEAMENTO
- Se você precisar de um alto desempenho apenas na consulta de dados, poderá pré-processar esses dados, deixando-os em sua modelagem final. Essa abordagem pode impulsionar o desempenho de sua consulta em ordens de magnitude;
- Se você tiver que processar informações para essas solicitações, mas nem todo esse processamento for relevante para a saída, tente armazenar em buffer o processamento: analise qual processamento de dados pode ser adiado e apenas grave os dados em algumas tabelas fictícias (talvez em outro DB) e processá-los em um horário diferente.