Temos um SQL Server 2008R2 Standard Edition com vários bancos de dados pertencentes a diferentes aplicativos em um servidor de 16 núcleos.
Um aplicativo introduzido recentemente está executando regularmente consultas caras que levam a 100% de uso da CPU. É claro que os outros aplicativos estão relatando problemas de desempenho.
O Resource Governor parece ser uma ferramenta adequada para colocar as rédeas no aplicativo não autorizado, infelizmente só está disponível na Enterprise Edition.
Como os outros aplicativos são bastante simples, tentei controlar o problema reduzindo o "grau máximo de paralelismo" da instância, para que uma única consulta não pudesse derrubar tudo . Embora isso tenha conseguido manter a carga da CPU em 50%, surpreendentemente não fez nada para impedir que outros aplicativos ficassem atolados.
Agora decidimos mover os bancos de dados do novo aplicativo para uma instância dedicada, mas qual seria a melhor configuração para essa instância? Devo manter a configuração MAXDOP, usar uma máscara de afinidade de CPU ou existe outra opção para limitar o uso da CPU que eu não conheço?
Você pode usar o Windows System Resource Manager (WSRM), que é um recurso do Windows Server (não tenho certeza da versão mínima, mas definitivamente 2008 R2+).
Isso permitirá que você controle a quantidade de CPU usada por um processo; portanto, se você ainda não tivesse separado o aplicativo invasor em sua própria instância do SQL Server, teria feito isso de qualquer maneira.
Nesse ponto, você pode definir o
MAXDOPque quiser, pois o processo nunca excederá os limites máximos definidos no WSRM.Definir a máscara de afinidade da CPU é uma opção. Você deseja isolar completamente o aplicativo em seu próprio conjunto de núcleos para eliminar a contenção (observação: observe seus nós NUMA). Se você tiver esses núcleos extras disponíveis, vá em frente, mas prefiro a solução WSRM porque, quando esse aplicativo está ocioso, todas as CPUs podem ser usadas por outros aplicativos.
Se você estiver configurando MAXDOP na metade dos núcleos e outras consultas ainda estiverem sendo deixadas de lado, você pode estar lidando com contenção de memória ou E/S de disco. Quanta RAM há no servidor e qual é o tamanho do banco de dados do aplicativo?
Dê uma olhada nesses contadores de perfmon quando o desempenho cair, e você poderá ter uma ideia se o servidor precisa de mais memória ou um subsistema de disco mais rápido:
SQLServer: Gerenciador de buffer
Se as leituras de Page/s dispararem e as outras duas caírem significativamente, a consulta problemática provavelmente está destruindo seu buffer pool e causando uma tonelada de E/S de disco. Você pode trabalhar no ajuste da consulta ou colocar mais hardware nela (mais RAM, discos mais rápidos).