Eu tenho uma produção "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" que está mostrando sintomas estranhos de buffer e expectativa de vida da página (PLE).
Estou executando isso a cada minuto no meu servidor (para rastrear esse problema):
SELECT @ple = CAST([cntr_value] AS VARCHAR(20))
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
SELECT @usedBufferPages = CAST(COUNT(*) /128 AS VARCHAR(20))
FROM sys.dm_os_buffer_descriptors
DECLARE @StartDate VARCHAR(8) = Convert(VARCHAR(8), GETDATE(), 14)
RAISERROR ('%s. PLE at %s and Used Buffers at %s at %s ', 0,
1,@runCountString ,@ple, @usedBufferPages, @StartDate) WITH NOWAIT
Este é um exemplo de saída:
16. PLE em 858 e buffers usados em 7290 em 09:51:42 17. PLE em 918 e Buffers Usados em 7342 em 09:52:42 18. PLE em 978 e Buffers Usados em 7408 em 09:53:43 19. PLE em 1039 e Buffers Usados em 7547 em 09:54:43 20. PLE em 1100 e buffers usados em 7697 em 09:55:44 21. PLE em 1160 e buffers usados em 7901 em 09:56:45 22. PLE em 1221 e buffers usados em 7961 em 09:57:46 23. PLE em 1282 e buffers usados em 8012 em 09:58:46 24. PLE em 11 e Buffers Usados em 313 em 09:59:46 25. PLE em 31 e buffers usados em 966 em 10:00:46 26. PLE em 90 e buffers usados em 1580 em 10:01:47 27. PLE em 151 e buffers usados em 3072 em 10:02:47 28. PLE em 211 e buffers usados em 3152 em 10:03:47 29. PLE em 271 e buffers usados em 3729 em 10:04:47
No item #24 o SQL Server informa o PLE passando de 1.282 para 11 . O SQL Server também relata que os buffers usados vão de 8.012 para 313 .
Primeiro, procurei por consultas de execução ruim e encontrei algumas corrigidas (não tiveram efeito sobre o problema). Porém, não estou encontrando nenhuma consulta de problema relacionada às vezes em que tenho problemas de PLE/Buffer. Além disso, se fosse uma consulta em execução ruim, eu pensaria que os Buffers estariam cheios dos dados dessa consulta, não vazios/ausentes/com erros.
Em seguida, pensei que a máquina virtual estava restringindo sua memória quando isso aconteceu. Mas perguntei ao meu System Admin e ele me garantiu que a memória não é dinâmica ou compartilhada de forma alguma. (O que é atribuído, ele recebe, o tempo todo.) Além disso, executo esse script a cada 10 minutos e quando o PLE informa menos de 50:
SELECT * FROM sys.dm_os_sys_memory
E relata os mesmos/semelhantes valores quando os PLE/Buffers estão altos e quando estão baixos. Para completar, aqui está um exemplo dos valores antes e depois do nº 24 acima:
total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_cache_kb kernel_paged_pool_kb kernel_nonpaged_pool_kb system_high_memory_signal_state system_low_memory_signal_state system_memory_state_desc 20970996 4758672 24378868 7929404 4844160 686076 182752 1 0 A memória física disponível é alta 20970996 4743468 24378868 7892632 4845000 686580 182688 1 0 A memória física disponível é alta
Eu verifiquei a sessão de integridade do sistema e não mostra nada relacionado. (Tudo o que tem são falhas de representação, e seus tempos não se correlacionam com os tempos que os PLE/Buffers mostram problemas.
Acompanhei a frequência com que isso ocorre, não consigo ver um padrão ou conectá-lo a nenhum trabalho ou atividade agendada.
Aqui está um gráfico que mostra PLE e Buffers ao longo de 21 horas:
Então estou perplexo. Acho que o cerne da questão são os buffers e não o PLE. (Acho que o PLE está recebendo um relatório falso de baixo porque todos os buffers desapareceram de alguma forma.)
Mas não consigo pensar em nenhuma maneira de isso acontecer. Ou o que fazer a seguir.
Eu adoraria conselhos sobre coisas adicionais para verificar ou sugestões sobre o que esse problema pode ser.
Atualizações de perguntas nos comentários:
Então, quanta memória é fornecida ao servidor? A VM tem 20 GB de memória.
O que é memória máxima do servidor?
nome valor value_in_use descrição memória máxima do servidor (MB) 13000 13000 Tamanho máximo da memória do servidor (MB) memória mínima do servidor (MB) 0 16 Tamanho mínimo da memória do servidor (MB)
NOTA: Eu li um pouco sobre isso agora e parece que essas configurações estão erradas para o meu servidor.
Qual é o tamanho do banco de dados? Existem dois bancos de dados transacionais em execução neste servidor (estou no processo de obter servidores para isolá-los). Seus tamanhos são 383 GB e 378 GB.
Quais outros aplicativos e serviços estão sendo executados nesse servidor? Este servidor hospeda os dados do meu aplicativo. Não há outras coisas atingindo-o. (Eu tenho um armazenamento de dados operacionais replicado para relatórios e tal.
Qual é a tecnologia VM VM Ware.
Esta VM está sendo executada em um host que hospeda apenas VMs com alocação de recursos semelhante? Temos muitas VMs em nossa empresa. Todos de tamanhos variados. Este é um dos maiores embora.
Você pode confirmar o que o administrador do sistema está lhe dizendo sobre a alocação de memória sem apenas ter que acreditar nele? Não posso. Não tenho acesso a essas ferramentas.
(Na minha experiência, os administradores do sistema dirão muitas coisas para passar a responsabilidade e culpar o aplicativo ou qualquer outra pessoa se isso significar que eles não precisam fazer nada.) Entendo perfeitamente esse sentimento.
Esse padrão certamente parece uma forte pressão de memória, concordo. Eu esperava encontrar algo para provar que o SQL está sentindo a pressão da memória. Assim, posso enviá-lo de volta aos administradores do sistema para mais pesquisas.
Estatísticas de tempo de espera
WaitType Wait_S Resource_S Signal_S WaitCount Porcentagem AvgWait_S AvgRes_S AvgSig_S ---------------------- ----------- ----------- ------ --- ---------- ------------ ---------- --------- ------ --- PAGEIOLATCH_SH 16250,10 16219,14 30,96 2171649 29,59 0,0075 0,0075 0,0000 CXPACKET 14214,03 13238,56 975,47 1187935 25,88 0,0120 0,0111 0,0008 PAGEIOLATCH_EX 6814,59 6806,21 8,38 638725 12,41 0,0107 0,0107 0,0000 WRITELOG 5157,42 4873,44 283,98 3588476 9,39 0,0014 0,0014 0,0001 BACKUPIO 2569,51 2538,12 31,39 1704119 4,68 0,0015 0,0015 0,0000 LCK_M_IX 2477,15 2477,10 0,05 113 4,51 21,9217 21,9213 0,0004 ASYNC_IO_COMPLETION 2079,99 2079,66 0,33 836 3,79 2,4880 2,4876 0,0004 BACKUPBUFFER 1807,75 1759,11 48,64 380189 3,29 0,0048 0,0046 0,0001 IO_COMPLETION 986,23 985,84 0,39 116112 1,80 0,0085 0,0085 0,0000
Seu buffer pool é de apenas 13 GB e seus bancos de dados são de 383 GB e 378 GB, que você classificou como OLTP - pequenas transações executadas com muita frequência.
A situação acima, se eu tiver que imaginar é como abaixo:
Você precisa entender como o SQL Server armazena informações:
Você com certeza está experimentando falta de memória devido à grande quantidade de tamanho do banco de dados e seu buffer pool inadequado. Consulte - Como determinar a memória ideal, por exemplo?
Colete estatísticas de espera e verifique se há problemas de desempenho decorrentes do desperdício de memória do buffer pool
Recomendação:
Adicione mais memória à instância do servidor e separe os dois bancos de dados em diferentes VMs com memória adequada.
Há muito pouco para depurar aqui - você precisa adicionar memória, dividir logicamente seu banco de dados em várias VMs ou entender que o embaralhamento que você precisa fazer com memória limitada levará a problemas de desempenho e PLE volátil. Tentar encaixar 800 GB de dados em 13 GB de memória é como tentar guardar tudo em uma mochila.
Conforme discutido neste tópico do SE e confirmado pelo OP.
O problema é devido a um bug no SQL Server 2012. Esse bug foi corrigido no SQL Server 2012 SP1 CU4 . Ou, para ficar mais seguro, recomendo que você aplique o SQL Server 2012 SP2 em vez de usar o CU4.
De acordo com os detalhes da correção de bug da Microsoft
Olhe mais de perto as consultas que estão sendo executadas. O uso de memória sozinho em bancos de dados normalmente é uma métrica muito grosseira para melhorar as coisas. Supondo que você não possa afetar as consultas (aplicativo de caixa preta), ainda vale a pena entender o que está afetando o uso de memória. Por exemplo, um processo em lote pode usar todo o espaço do buffer em um único acesso, consultando todos os dados em uma tabela enorme.
Em particular, procure por quaisquer índices ausentes que causem verificações completas da tabela - pois eles podem efetivamente liberar o cache no servidor.
O SQL Server tem um excelente conjunto de ferramentas de análise que podem monitorá-lo em tempo real, e suspeito que você verá algo se destacar como um polegar dolorido quando se aprofundar nele.
Não que eu esteja sugerindo alterar o esquema do banco de dados, mas uma coisa a se observar são os campos varchar excessivamente grandes - eles podem realmente sugar o espaço do cache em um banco de dados grande.