Eu me deparei com um problema estranho em que o SQL Server 2016 Standard Edition de 64 bits parecia ter se limitado a exatamente metade da memória total alocada para ele (64 GB de 128 GB).
A saída de @@VERSIONé:
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) 22 de dezembro de 2017 11:25:00 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Datacenter 6.3 ( Build 9600: ) (Hipervisor)
A saída de sys.dm_os_process_memoryé:
Quando eu consulto sys.dm_os_performance_counters, vejo que Target Server Memory (KB)está em 131072000e Total Server Memory (KB)está em pouco menos da metade disso em 65308016. Na maioria dos cenários, eu entenderia que isso é um comportamento normal, pois o SQL Server ainda não determinou que precisa alocar mais memória para si mesmo.
No entanto, ele está "preso" em ~ 64 GB há mais de 2 meses. Durante esse período, realizamos uma quantidade significativa de operações com uso intensivo de memória em alguns dos bancos de dados e adicionamos cerca de 40 bancos de dados à instância. Temos um total de 292 bancos de dados, cada um com arquivos de dados pré-alocados em 4 GB com taxa de crescimento automático de 256 MB e arquivos de log de 2 GB com taxa de crescimento automático de 128 MB. Eu executo um backup completo uma vez à noite às 00h00 e começo os backups de log de transações de segunda a sexta-feira, das 6h00 às 20h00 em um intervalo de 15 minutos. Esses bancos de dados são relativamente baixos em sua taxa de transferência geral, mas estou cético de que algo esteja errado, já que o SQL Server não se aproximou doTarget Server Memorynaturalmente por meio de novas adições de banco de dados, execuções normais de consulta, bem como pipelines ETL com uso intensivo de memória que foram executados.
A própria instância do SQL Server está sobre um servidor Windows Server 2012R2 virtualizado (VMware) com 12 CPUs, 144 GB de memória (128 GB para SQL Server, 16 GB reservados para Windows) e 4 discos virtuais totais que ficam sobre um vSAN com unidades SAS de 15.000 . O Windows fica naturalmente em um disco C: de 64 GB com um arquivo de paginação de 32 GB. Os arquivos de dados ficam em um disco D: de 2 TB, os arquivos de log ficam em cima de um disco L: de 2 TB e o tempdb fica em um disco T: de 256 GB com arquivos de 8 x 16 GB sem crescimento automático.
Verifiquei que não há outras instâncias do SQL Server em execução no servidor além do MSSQLSERVER.
Esse servidor é totalmente dedicado apenas à instância do SQL Server, portanto, não temos outros aplicativos ou serviços em execução que possam consumir memória.
Eu utilizo o RedGate SQL Monitor para análise, e abaixo está um histórico dos últimos 18 dias de Total Server Memory. Como você pode ver, a utilização de memória permaneceu totalmente estagnada, exceto por um único aumento de ~ 300 MB no início de abril.
Qual pode ser a causa disso? O que posso examinar mais de perto para determinar por que o SQL Server não deseja usar os 64 GB + de memória adicionais alocados para ele?
A saída de execução sp_Blitz:
sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;Prioridade 50: Desempenho :
CPU Schedulers Offline - Alguns núcleos de CPU não são acessíveis ao SQL Server devido a problemas de licenciamento ou mascaramento de afinidade.
Nós de memória offline - devido a problemas de licenciamento ou mascaramento de afinidade, parte da memória pode não estar disponível.
Prioridade 50: Confiabilidade :
- Remote DAC Disabled - O acesso remoto à Dedicated Admin Connection (DAC) não está habilitado. O DAC pode tornar a solução de problemas remota muito mais fácil quando o SQL Server não responde.
Prioridade 100: Desempenho :
Muitos planos para uma consulta - 300 planos estão presentes para uma única consulta no cache do plano - o que significa que provavelmente temos problemas de parametrização.
Acionadores do servidor ativados
O acionador do servidor [RG_SQLLighthouse_DDLTrigger] está habilitado. Certifique-se de entender o que esse gatilho está fazendo - quanto menos trabalho ele fizer, melhor.
O acionador do servidor [SSMSRemoteBlock] está ativado. Certifique-se de entender o que esse gatilho está fazendo - quanto menos trabalho ele fizer, melhor.
Prioridade 150: Desempenho :
Consultas forçando dicas de junção - 1.480 instâncias de dicas de junção foram registradas desde a reinicialização. Isso significa que as consultas estão dominando o otimizador do SQL Server e, se elas não souberem o que estão fazendo, isso pode causar mais danos do que benefícios. Isso também pode explicar por que os esforços de ajuste do DBA não estão funcionando.
Consultas forçando dicas de pedidos - 2.153 instâncias de dicas de pedidos foram registradas desde a reinicialização. Isso significa que as consultas estão dominando o otimizador do SQL Server e, se elas não souberem o que estão fazendo, isso pode causar mais danos do que benefícios. Isso também pode explicar por que os esforços de ajuste do DBA não estão funcionando.
Prioridade 170: Configuração do Arquivo :
Banco de dados do sistema na unidade C
master - O banco de dados master possui um arquivo na unidade C. Colocar bancos de dados do sistema na unidade C corre o risco de travar o servidor quando ficar sem espaço.
model - O banco de dados do modelo possui um arquivo na unidade C. Colocar bancos de dados do sistema na unidade C corre o risco de travar o servidor quando ficar sem espaço.
msdb - O banco de dados msdb tem um arquivo na unidade C. Colocar bancos de dados do sistema na unidade C corre o risco de travar o servidor quando ficar sem espaço.
Prioridade 200: Informativo :
Trabalhos do agente iniciando simultaneamente - vários trabalhos do SQL Server Agent são configurados para iniciar simultaneamente. Para listas de programação detalhadas, consulte a consulta na URL.
Tabelas no mestre do banco de dados mestre - A tabela CommandLog no banco de dados mestre foi criada por usuários finais em 30 de julho de 2017, 17h22. As tabelas no banco de dados mestre não podem ser restauradas em caso de desastre.
TraceFlag ativado
O sinalizador de rastreamento 1118 está habilitado globalmente.
O sinalizador de rastreamento 1222 está habilitado globalmente.
O sinalizador de rastreamento 2371 está habilitado globalmente.
Prioridade 200: Configuração de servidor não padrão :
Agent XPs - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
backup checksum default - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
padrão de compactação de backup - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
limite de custo para paralelismo - Esta opção sp_configure foi alterada. Seu valor padrão é 5 e foi definido como 48.
grau máximo de paralelismo - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 12.
max server memory (MB) - Esta opção sp_configure foi alterada. Seu valor padrão é 2147483647 e foi definido como 128000.
otimizar para cargas de trabalho ad hoc - esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
mostrar opções avançadas - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
xp_cmdshell - Esta opção sp_configure foi alterada. Seu valor padrão é 0 e foi definido como 1.
Prioridade 200: Confiabilidade :
Procedimentos armazenados estendidos no mestre
master - O procedimento armazenado estendido [sqbdata] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbdir] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbmemory] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbstatus] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbtest] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbtestcancel] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbteststatus] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqbutility] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
master - O procedimento armazenado estendido [sqlbackup] está no banco de dados master. O CLR pode estar em uso e o banco de dados mestre agora precisa fazer parte do planejamento de backup/recuperação.
Prioridade 210: Configuração de banco de dados não padrão :
Read Committed Snapshot Isolation Enabled - Esta configuração de banco de dados não é o padrão.
RedGate
RedGateMonitor
Isolamento de Instantâneo Habilitado - Esta configuração de banco de dados não é o padrão.
RedGate
RedGateMonitor
Prioridade 240: Estatísticas de espera :
- 1 - SOS_SCHEDULER_YIELD - 1770,8 horas de espera, 115,9 minutos de tempo médio de espera por hora, 100,0% de espera de sinal, 1419212079 tarefas em espera, 4,5 ms de tempo médio de espera.
Prioridade 250: Informativo :
- SQL Server está sendo executado em uma conta NT Service - estou executando como NT Service\MSSQLSERVER. Eu gostaria de ter uma conta de serviço do Active Directory.
Prioridade 250: Informações do Servidor :
Conteúdo de rastreamento padrão - O rastreamento padrão contém 36 horas de dados entre 14 de abril de 2018 23h21 e 16 de abril de 2018 11h13. Os arquivos de rastreamento padrão estão localizados em: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log
Espaço C da unidade - 196816,00 MB livres na unidade C
Espaço D da unidade - 894823,00 MB livres na unidade E
Espaço L da unidade - 1361367,00 MB livres na unidade F
Espaço na unidade T - 114441,00 MB livres na unidade G
Hardware - Processadores lógicos: 12. Memória física: 144GB.
Hardware - Configuração NUMA
Nó: 0 Estado: ONLINE Agendadores online: 4 Agendadores offline: 2 Grupo de processadores: 0 Nó de memória: 0 Memória VAS GB reservado: 186
Nó: 1 Estado: OFFLINE Agendadores online: 0 Agendadores offline: 6 Grupo de processadores: 0 Nó de memória: 0 Memória VAS GB reservado: 186
Inicialização instantânea de arquivo habilitada - a conta de serviço tem a permissão Executar tarefas de manutenção de volume.
Plano de energia - Seu servidor tem CPUs de 2,60 GHz e está no modo de energia balanceada -- Uh... você quer que suas CPUs funcionem em velocidade máxima, certo?
Última reinicialização do servidor - 9 de março de 2018 7h27
Nome do servidor - [redigido]
Serviços
Serviço: o SQL Server (MSSQLSERVER) é executado na conta de serviço NT Service\MSSQLSERVER. Última hora de inicialização: 9 de março de 2018 7h27. Tipo de inicialização: Automático, atualmente em execução.
Serviço: o SQL Server Agent (MSSQLSERVER) é executado na conta de serviço LocalSystem. Última hora de inicialização: não mostrada. Tipo de inicialização: Automático, atualmente em execução.
Última reinicialização do SQL Server - 9 de março de 2018 6h27
Serviço SQL Server - Versão: 13.0.4466.4. Nível de patch: SP1. Atualização cumulativa: $ 7. Edição: Edição padrão (64 bits). Grupos de Disponibilidade Habilitados: 0. Status do Gerente de Grupos de Disponibilidade: 2
Servidor Virtual - Tipo: (HIPERVISOR)
Versão do Windows - Você está executando uma versão bastante moderna do Windows: era do servidor 2012R2, versão 6.3
Prioridade 254: Data de execução :
- Diário do capitão: stardate algo e algo...
Aposto que você configurou as CPUs virtuais de forma que alguns dos nós de CPU e/ou nós de memória fiquem offline.
Faça o download do sp_Blitz (disclaimer: sou um dos autores desse script de código aberto gratuito) e execute-o:
Procure avisos sobre nós de CPU e/ou memória estarem offline. O SQL Server Standard Edition vê apenas os primeiros 4 soquetes de CPU e você pode ter configurado a VM como algo como 6 CPUs de núcleo duplo. Acabará atingindo um problema semelhante a como os limites de 20 núcleos da Enterprise Edition limitam a quantidade de memória que você pode ver .
Se você quiser compartilhar a saída do sp_Blitz aqui, você pode executá-lo assim para enviar para Markdown, que você pode copiar/colar em sua pergunta:
sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;Atualização 16/04/2018 - confirmada. Você anexou a saída sp_Blitz (obrigado por isso!) e realmente mostra que você tem nós de CPU e memória offline. Quem construiu a VM a configurou como 12 CPUs de núcleo único, então o SQL Server Standard Edition está vendo apenas os primeiros 4 soquetes (núcleos) e a memória anexada a eles.
To fix it, shut down the VM, configure it as a 2-socket, 6-core VM, and then SQL Server Standard Edition will see all of the cores and memory. This will also reduce your SOS_SCHEDULER_YIELD waits too - right now, your SQL Server is hammering the first 4 cores, but that's it. After this fix, it'll be able to work on all 12 cores.
As an addendum to Brent Ozar's plan of action, I wanted to share the results. As Brent noted, within VMware we had configured the Virtual Machine improperly with 12 single-core CPUs. This resulted in the remaining 8 cores being inaccessible by SQL Server, and as a result, led to the memory issue described in my original question. We placed our services in maintenance mode last night in order to reconfigure the VM appropriately. Not only are we seeing the memory creep up in a normal fashion, but as Brent also hinted, the number of waits went down exponentially and our overall SQL Server performance has skyrocketed. The vNUMA configurations are now happy little components that are slicing through our workloads.
For those that might be utilizing VMware vSphere 6.5, the brief steps to complete the action item described by Brent are as follows.
Within the primary pane, go to
Configure > VM hardware, click theEditbutton in the top right-hand corner. You will open up a context menu that hasEdit Settings. For reference, the below image is the incorrect configuration. Note that I haveCores per Socketset to1. Given the limitations of SQL Server Standard Edition, this is a bad configuration.The fix is as simple as adjusting the
Cores per Socketvalue. In our case, we set it to6so that we have2 Sockets. This allows SQL Server to utilize all 12 processors.An important note: Do not set the value to where either the
Number of Coresor theSocketswould be an odd number. NUMA loves balance, and by rule of thumb, needs to be divisible by 2. For instance, a configuration of 4 cores to 3 sockets would be imbalanced. In fact, if you were to runsp_Blitzwith this type of configuration, it would toss a warning about this.Section 3.3 in Architecting Microsoft SQL Server on VMware vSphere (PDF warning) outlines this in detail. The practices outlined in the whitepaper are applicable to most all on-premise virtualization of SQL Server.
Here are a few more resources I have compiled through my research after Brent's post:
Virtualizing large databases - VMware CPU capacity planning
Virtual Machine vCPU and vNUMA Rightsizing – Rules of Thumb
Decoupling of Cores per Socket from Virtual NUMA Topology in vSphere 6.5
I'll end on a capture from RedGate SQL Monitor over the past 24 hours. The primary point of note is the CPU utilization and number of waits - during our peak hours yesterday, we were experiencing heavy CPU use and wait contentions. After this simple fix, we have improved our performance tenfold. Even our disk I/O has reduced significantly. This is a seemingly easily overlooked setting that can improve virtual performance by an order of magnitude. At least, it was overlooked by our engineers and a complete d'oh moment.
Also, according to MSDN, SQL Server standard is limited to 64GB of RAM. We 'solved' this by splitting the database into multiple instances, but your situation might not allow for that.
Hmm 2016 seems to have 128GB as a limit, but the instance-split might be an option still.