O DBA Sênior anterior deixou a empresa e eu fui informado de um servidor que sofre de vários problemas, em grande parte lentidão (desde SSMS demorando muito para abrir, consultas demoradas e trabalhos SSIS com falha (Data Warehouse), até dificuldades de conexão com servidores vinculados (mais de 150 deles)).
Provavelmente há muita coisa acontecendo neste servidor que contém 5 instâncias empilhadas. Um novo DBA Sênior se juntará à equipe em breve, mas seria bom ter tudo resolvido para ele ou ela quando eles se juntarem.
Então, o âmago da questão:
Quando tomei conhecimento do problema, descobri que 94% da memória do servidor estava alocada para o SQL Server. Fui em frente e reduzi para 85% desalocando a memória de duas instâncias que foram superprovisionadas.
Percebi então que MAXDOP em nossa instância padrão foi definido como 4 (possivelmente 6, não me lembro) junto com uma configuração de CPU Affinity. Essas CPUs foram rastreadas enquanto havia atividade mínima nas outras. Eu fui em frente e removi a configuração Affinity (uma vez que essas configurações estavam em vigor antes de CPUs adicionais serem adicionadas). Defino MAXDOP como 20 em todas as 5 instâncias.
Atualmente, ainda estou vendo 4 das CPUs atreladas, mas com uma média geral. uso (em todas as CPUs) de aproximadamente apenas 25%.
Eu usei o ProcExp da SysInternal, o Monitor de recursos e o Windows Performance Toolkit para observar o problema, mas não sei como isolar qual(is) processo(s), especificamente, é(são) a causa raiz. Alguma recomendação/orientação sobre como realmente isolar o que está acontecendo aqui? (ou seja, contadores / rastreamentos / outros programas específicos.)
UPDATE , por solicitações:
Informações do sistema:
Windows Server 2012 R2 Standard
64 GB de memória total
20 CPUs
Configuração:
24 GB de memória alocada para esta instância
26,5 GB de memória alocada para outras instâncias (total de 50,5 GB - 78,9%)
Limite de custo para paralelismo = 50 (em todas as instâncias)
Desativei um processo SSAS não utilizado.
Então você tem 5 instâncias 'empilhadas' em um único servidor Windows. Você não disse exatamente quantos soquetes/CPUs estão disponíveis e quanta memória. Gosto de definir afinidade para cada instância nesses casos, mesmo que decida ter CPUs sobrepostas na luta para equilibrar a carga geral da CPU (depende da carga de cada instância).
Qualquer instância com mais de 4 CPUs pode usar uma configuração DOP explícita em minha experiência - raramente acima de '4' em casos empilhados como o seu. Não se esqueça de definir 'Limite de custo do paralelismo' para cada instância para algo razoável (50?) Para evitar paralelismo excessivo - no seu caso, isso é ainda mais importante.
Lembre-se que a memória deixada "para o SO" deve ser maior agora, já que você tem que contabilizar o footprint de cada instância (em cima do SSIS etc). Verifique no SQL Config Mgr se o SSAS também está em execução e ajuste sua 'memória máxima' de acordo, por padrão, ele ocupa 80% de toda a memória do servidor (!)
Também talvez valha a pena remover e 'Bloquear páginas na memória' da(s) conta(s) do Serviço SQL para que o sistema operacional possa respirar e fazer seu trabalho melhor (se paginar, todo mundo sofre!). Também é uma boa prática definir uma 'memória mínima' razoável para cada instância.
Acho que executar sp_blitz e sp_blitz_first em cada instância forneceria algumas dicas rápidas sobre questões mais urgentes.
Você também pode querer monitorar alguns contadores do windows permon como 'memória disponível' e 'conjunto de trabalho' para cada um dos processos em execução lá, caso encontre horários específicos do dia/noite em que o servidor está sofrendo.
A causa desse problema quase certamente estava no nível da VM. A equipe de operações configurou o servidor não apenas para ter 20 CPUs, mas também 20 soquetes.
Li online que os servidores virtuais não diferenciam entre soquetes, núcleos ou CPUs, mas o problema desapareceu desde que solicitei a alteração da configuração. Além disso, as ferramentas VMware não relataram nenhum problema. As ferramentas de solução de problemas do VMware nem estavam disponíveis (supostamente), pois um problema não foi reconhecido.