Quando o otimizador de consulta calcula o custo de um plano de execução, você pode esperar que o plano seja diferente dependendo da quantidade de recursos disponíveis para ele?
Considere o seguinte cenário:
- Você tem uma máquina virtual VMware executando o SQL Server 2008 R2 com 32 GB de RAM, 8 núcleos de CPU conectados a uma SAN. Existem 7 VMs compartilhando a caixa do host.
- Você descobre que o uso da CPU atinge um pico de 130% regularmente, então você move a maioria das VMs para fora do host, deixando apenas 3 das 7 VMs originais, incluindo a caixa do SQL Server.
- Você descobre imediatamente que o desempenho real é mais lento agora que há mais espaço de CPU disponível para ele. Você pode ver que a utilização da CPU no convidado está muito maior (porque mais recursos da CPU estão disponíveis para uso) do que os 30% que poderia reunir antes. Apesar disso, os usuários do aplicativo percebem uma desaceleração acentuada no desempenho.
Minha interpretação de por que uma VM SQL tem um desempenho mais lento quando mais recursos de CPU estão disponíveis para ela é que o cache de procedimento contém planos calculados abaixo/otimizados para um ambiente com menos disponibilidade de CPU. Quando o recurso extra estiver disponível, o cache de procedimento fornecerá planos abaixo do ideal que podem ter um desempenho inferior.
Isso faz sentido? Tivemos esse cenário exato algumas semanas atrás e recebemos muitas reclamações sobre o desempenho assim que interrompemos a surra da CPU no host. Executei o dbcc freeprocache e, a partir daí, o desempenho pareceu melhorar - ou isso ou os usuários se acostumaram com isso.
Pensamentos? Obrigado
Primeiro, algumas informações básicas
Até agora (até o SQL Server 2014), o otimizador de consulta levará apenas algumas coisas em consideração:
Núcleos de CPU: a contagem de núcleos atribuída ao SQL Server a partir da máscara de afinidade determina o quão eficiente pode ser a criação de um plano paralelo. Por exemplo, em um sistema com 4 núcleos, você pode esperar uma aceleração de 4x ao alternar para um plano paralelo, enquanto um sistema com 2 núcleos obtém apenas uma duplicação. Por causa disso, a contagem de núcleos pode afetar a escolha do plano. Infelizmente, nem toda consulta pode escalar linearmente com a contagem de núcleos, mas o otimizador acreditará que sim. Isso significa que, em máquinas com mais de 12 a 16 núcleos, obter mais paralelismo realmente DEIXARÁ a consulta. A velocidade da CPU não é levada em consideração, apenas o número de núcleos.
Memória disponível : Quando o planejamento é feito, a quantidade de memória disponível é levada em consideração. Isso determina estratégias como hash joins, espaço de classificação e outras operações com uso intensivo de memória. A estimativa incorreta aqui é muito perigosa e pode levar a um desempenho ruim. Especialmente se você superestimar a memória disponível para uma junção de hash e tiver que derramar arquivos
tempdb.seu caso específico
Sem medições do seu sistema é difícil, se não impossível, saber exatamente o que aconteceu no seu cenário. Existem muitas variáveis que potencialmente mudaram ao mesmo tempo. Pode ser simplesmente que alguma outra coisa tenha mudado no ambiente. Qualquer diagnóstico é pura suposição e o trabalho real do DBA é uma ciência, não um exercício artístico de adivinhação.
Você precisaria coletar o seguinte para obter conhecimento.