Casos do Dia a Dia – Diminuíndo um problema de memória no SQL Server
Fala Pessoal,
Em meu ambiente de trabalho, possuo um servidor 32 bits com o SQL Server 2005 32 bits, com 4 GB de memória e com a opção /3GB habilitada. Esse servidor possui uma média de 1500 conexões simultâneas no banco de dados durante o horário de pico e possui 200 Gb de dados. Repetindo, 200 GB de dados e 4 GB de memória.
Como pode ser visto na figura abaixo, o serviço do SQL Server consegue utilizar 2.51 GB de memória nesse servidor.
Essa informação foi gerada com a query abaixo:
select counter_name ,cntr_value,cast((cntr_value/1024.0)/1024.0 as numeric(8,2)) as Gb
from sys.dm_os_performance_counters
where counter_name like ‘%server_memory%’;
Vocês devem concordar comigo que 2.51 GB de memória é muito pouco para a quantidades de dados que possuo. Com a query abaixo é possível verificar o contador Page Life Expectancy, que é a expectativa de vida em segundos de uma página de dados na memória. No meu ambiente, esse contador tinha uma média entre 30 e 40 segundos durante o dia, sendo que um número considerável aceitável para esse contador é acima de 300 segundos.
SELECT cntr_value AS ‘Page Life Expectancy’
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘Page life expectancy’
Resultado:
Antes de realizar uma reestruturação nos meus índices (criar missed index, alterar o fillfactor, excluir índices duplicados e excluir indices não utilizados), esse contador tinha uma média abaixo de 20 segundos em determinadas horas do dia.
Buscando uma forma de amenizar esse problema(até que seja realizado um upgrade de Hardware), dei uma boa pesquisada sobre memória e verifiquei que desses 2.51 GB de memória, o SQL Server estava utilizando 1.5 GB de memória para outros tipos de cache diferentes do Data Cache (Buffer Pool).
Olha que absurdo, 1.5 GB para alguns caches (Ex: Caches de Plano de Execução de Procedures e de Adhoc Querys) e apenas 1 GB para o Buffer Pool, onde realmente estão as paginas de dados que são utilizadas por qualquer manipulação de dados no SQL Server.
Quando você realiza uma consulta, caso as páginas de dados que sua consulta deveria retornar não estejam no Buffer Pool, as páginas são lidas do disco, armazenadas no Buffer Pool e em seguida são retornadas para a sua consulta. Ou seja, quanto maior for o tamanho do Buffer Pool, mais páginas de dados serão armazenadas na memória, menos leituras serão realizadas no disco e, consequentemente, o ganho de performance será muito grande.
Executando as querys abaixo, foi possível identificar o quanto de memória estava sendo utilizado por alguns tipos de Cache do SQL Server:
— Utilização por tipo de cache
SELECT type, SUM(single_pages_kb)/1024. AS [SPA Mem, MB],SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
FROM sys.dm_os_memory_clerks
GROUP BY type
HAVING SUM(single_pages_kb) + sum(Multi_pages_kb) > 40000 — Só os maiores consumidores de memória
ORDER BY SUM(single_pages_kb) DESC
— Total utilizado
SELECT SUM(single_pages_kb)/1024. AS [SPA Mem, KB],SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
FROM sys.dm_os_memory_clerks
A figura abaixo mostra como estava o resultado dessas querys no meu ambiente no horário de produção.
O CACHESTTORE_OBJCP é o cache das Stored procedures, Triggers e Functions.
O CACHESTORE_SQLCP é o cache de Ad-hoc queries e não é muito reutilizado pelo SQL Server, pois para uma mesma consulta com parâmetros diferentes, são gerados dois planos de execuções diferentes.
O USERSTORE_TOKENOERM é o cache que armazena várias informações de segurança que são utilizadas pela Engine do SQL Server.
Analisando essas informações, tomei as seguintes medidas:
Como o CACHESTTORE_OBJCP é muito reutilizado, não mechi no mesmo pois poderia piorar ainda mais meu problema de performance. Se no meu servidor tivesse a versão do SQL Server 2008, eu poderia excluir individualmente do cache alguns planos de execução que ocupam mais espaço na memória e são poucos utilizados. Mas como ainda está na versão do SQL Server 2005, não tive o que fazer.
Já os caches CACHESTORE_SQLCP e USERSTORE_TOKENOERM, que ocupavam juntos mais de 1 GB de memória, me ajudariam bastante liberando memória para o Buffer Pool. Entretanto, limpando o cache CACHESTORE_SQLCP eu aumentaria meu consumo de CPU pois incrementaria o número de compilações de algumas querys. Todavia, como meu gargalo é memória, resolvi limpar esses caches assim mesmo.
Criei uma Stored Procedure que roda de 3 em 3 minutos para realizar a limpeza desses caches e ainda armazeno um log da frequencia com que esses caches são limpos em uma tabela para histórico conforme os scripts abaixo:
CREATE TABLE [dbo].[Log_Limpeza_Cache](
[Id_Log] [int] IDENTITY(1,1) ,
[Dt_Limpeza] [datetime] ,
[Tipo_Cache] [varchar](30) ,
[Tamanho_MB] [numeric](15, 2) )
CREATE PROCEDURE [dbo].[stpLimpa_Memory_Cache]
AS
BEGIN
DECLARE @USERSTORE_TOKENPERM numeric(15,2), @CACHESTORE_SQLCP numeric(15,2)
SELECT @CACHESTORE_SQLCP = SUM(single_pages_kb+Multi_pages_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE type = ‘CACHESTORE_SQLCP’
SELECT @USERSTORE_TOKENPERM = SUM(single_pages_kb+Multi_pages_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE type = ‘USERSTORE_TOKENPERM’
IF @USERSTORE_TOKENPERM > 30
begin
insert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
select getdate(), ‘USERSTORE_TOKENPERM’, @USERSTORE_TOKENPERM
DBCC FREESYSTEMCACHE(‘TokenAndPermUserStore’)
end
IF @CACHESTORE_SQLCP > 60
begin
insert into Log_Limpeza_Cache(Dt_Limpeza,Tipo_Cache,Tamanho_MB)
select getdate(), ‘CACHESTORE_SQLCP’, @CACHESTORE_SQLCP
DBCC FREESYSTEMCACHE(‘SQL Plans’)
end
END
Após a primeira limpeza do cache(momentos de tensão), fiquei acompanhando meu trace com as querys mais demoradas do banco de dados para ver se teria algum problema de performance. Entretanto, isso não aconteceu. Pelo contrário, a quantidade de querys lentas diminuíram.
Após alguns minutos, analisando os contadores de performance, o Page life Expectancy passou a ter uma média entre 100 e 120 segundos. Simplesmente triplicou.
Meu buffer pool, aumentou para mais de 2 GB, ou seja, a quantidade de dados que o SQL Server disponibiliza em memória para ser utilizada sem acessar o disco dobrou. Eu verifico essa informação com a query abaixo:
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 ––- exclude system databases
AND database_id <> 32767– –- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
Como já era esperado, também tive um aumento de processamento e de recompiles, mas o benefício da liberação de memória foi maior para o meu ambiente. O número de procedimentos demorando mais de 3 segundos diminuíu consideravelmente.
Além dessa alteração, fiz muitas outras alterações de performance para esse servidor aguentar o grande aumento de acesso que temos no Banco de Dados nesse Natal, mas acredito que essa alteração foi a que fez a maior diferença e me proporcionou um Natal bem tranquilo. Um excelente presente de Natal.
Claro que antes de realizar essas alterações no meu servidor de produção, testei em outros servidores menos críticos. Deixo essa experiência para quem possui um ambiente com esse mesmo tipo de problema. Espero que possa ter ajudado.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Confira mais experiências do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Ótimo post amigão.
Feliz natal.
Abraço,
Valeu Rodrigo…. Feliz Natal para você e sua família.
Ótimo post Fabrício,
Continue nessa linha que está fantástico.
Parabéns.
Obrigado Marcos.
fiz o mesmo procedimento
Page life expectancy: 152630
Acredito ser bem alto, porem ao executar o plano de cache
CACHESTORE_SQLCP 1574
MEMORYCLERK_SQLGENERAL 42
USERSTORE_TOKENPERM 39
Não encontrei o CAche_object pq ? e o cacheSQL não esta baixo?
Target Server Memor 8834040 8.42
Total Server Memory 4316672 4.12
Abraços
Seu Page life Expectancy está excelente.
Tire a cláusula
HAVING SUM(single_pages_kb) + sum(Multi_pages_kb) > 40000 — Só os maiores consumidores de memória
da query que o Cache_Object deve aparecer.
O tamanho do cacheSQL depende da quantidade de diferentes querys que são executadas no seu ambiente.
Você não tem gargalo nenhum de memória. O que diminui bastante seu acesso a disco.
Ok, realmente sem o where apareceu
CACHESTORE_OBJCP 9.203125
Porem não é baixo? para uma database com mais de 8GB e com 12GB de ram? pq ele não joga o database inteiro na RAM, assim ficaria rapido caso ele de um fullscan.?!?
CACHESTORE_OBJCP Isso é o cache de procedures, functions e outros. É o tamanho do execution plan desses objetos e não os dados das tabelas.
Os dados estão no buffer pool e podem ser visto com a query abaixo agrupado por database:
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 ––- exclude system databases
AND database_id 32767– –- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
amigo, tenho um bd Firebird 1.5 e meu banco tem 2.9gb.
acesso ele via TS para ele nao ter que navegar na rede… mesmo esse bd na rede o 1.5 nao é legal, o que posso fazer? pois vc exemplificou o sql server… que nao é o meu caso… trabalhou ou melhorou a performance do firebird 1.5 ja?
Olá virgilio, infelizmente nunca trabalhei com firebird, então não poderei te ajudar. Meu blog é bem focado no SQL Server mesmo.
Abraços
Fabrício, bom dia!
Eu preciso fazer esse trabalho no meu ambiente, porém a versão do SQL ainda é a 2000 32 bits.
Conhece alguma forma de monitorar essas áreas de memória no sql 2000 ?
Abraço.
Alexandre.
Fala Alexandre.
Sinceramente eu não conheço uma forma de realizar isso no SQL 2000. O pouco tempo que trabalhei com SQL Server 2000 eu ainda nem era DBA.
No 2005 isso se tornou bem mais facil devido as DMV’s.
Valeu.
Parabéns pelo artigo.
Passo pelas as mesmas dificuldades que você em relação a memória, mas ainda não tinha percebido esse detalhe em relação a memória segue sua dica e ficou filé aqui.
Valeu Wellington!
Fábricio
Você está de parabéns, seu post foi muito util para mim, estava passando pelos mesmos problemas.
Show de bola Carlos.
Que bom que ajudou.
Valeu.
Ola Fabricio, andei lendo varios artigos seu, queria te pergunta o que ocorre quando o Page life expectancy fica entre 58870 e subindo sem descer ^^ ?
Nao sei se e bom, nao sei como resetar, parece que so volta quando reinicia o sql =/
Olá Dante.
Quando isso acontecer fique feliz pois o valor está muito bom. Quanto maior melhor!!!
Abraços
@Dante
Dante, ele só “volta” quando reinicia porque o cache do SQL é limpo, mas um PLE de 58 mil segundos? É muita coisa meu caro, de duas uma:
1 – Seu servidor tem muita memória e os bancos são pequenos
2 – No perfmon a escala deve tá errada, ao invés de 1.0 deve tá 0.1, etc !
Fabrício, queria lhe perguntar algo que nao tem a ver muito com o Post, mais gostaria de esclarecimentos:
1 – No SQL Server Express 2008 (Gratuito) consigo até quantas conexões simultaneas sem perder performance, observando que os servidores rodam em VMs e mesmo assim tem a limitação de memoria, tamanha do banco e processador.
2 – No server 2012 as limitações so BD gratuito da microsoft aumentaram, valendo a pena a migração ou continuam as mesmas?
Vlw! Obrigado!
Olá Everton,
Seguem as respostas:
1 – No SQL Server Express 2008 (Gratuito) consigo até quantas conexões simultaneas sem perder performance: Isso depende do que essas conxões estão fazendo, queries, indices, estatísticas e etc. Não tem uma conta de padaria.
2 – No server 2012 as limitações so BD gratuito da microsoft aumentaram, valendo a pena a migração ou continuam as mesmas?
Não parece ter mudado muita coisa não. Segue o link para análise: http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.110%29.aspx
Abraços
Amigão usei sua procedure porem ocorreu esse erro, sabe me dizer o pq? Mensagem 111, Nível 15, Estado 1, Procedimento stpLimpa_Memory_Cache, Linha 11
‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.
Olá William,
Seleciona apenas o código de criar a procedure e execute novamente. Alguma coisa está sendo executada junto com o comando create procedure.
Olá Fabricio, excelente post. Queria tirar uma dúvida contigo. Utilizei as queries do post pra avaliar os resultados em meu ambiente e gostaria de sua ajuda pra interpretá-los. Servidor tem 128GB e duas bases de dados de 260GB e outra de 60GB. Estou tento problema de performance e estou pesquisando pra identificar onde está o problema de lentidão.
Target Server Memory (KB) 104.49 / Total Server Memory (KB) 104.49
Page Life Expectancy 66225
CACHESTORE_SQLCP 8365.781250
MEMORYCLERK_SQLGENERAL 1.109375
USERSTORE_TOKENPERM 19.554687
Olhando esse contador: Page Life Expectancy 66225
A utilização de memória está boa.
Um problema de performance pode ter várias causas. Difícil fazer uma análise com essas informações.
Analisa as queries mais lentas e contadores de CPU e Disco.
Se não conseguir validar e tiver orçamento para isso, eu posso fazer um Tuning nesse ambiente.
Fabricio, queries lentas tem algumas, porém fiz uma análise superficial e verifiquei uma certa pressão no CPU provavelmente por essas consultas. Identifiquei também muitos índices não usados e tabelas com até 50 índices em sua composição.
Exclua esses índices não utilizados se possível.
Digo isso pelo fato de ter observado os waitstype e ter verificado um percentual alto pra o wait CXPACKET.
WaitType Percentage
CXPACKET 46.99
ASYNC_NETWORK_IO 14.12
OLEDB 12.69
LATCH_EX 8.90
LCK_M_S 8.73
PAGEIOLATCH_SH 1.01
EXECSYNC 0.93
LCK_M_U 0.81
IO_COMPLETION 0.81
LCK_M_IS 0.79
Parabéns!
Muito bom o seu artigo.
Obrigado Fagner.
Parabéns pelo seu post, só tive a oportunidade de velo somente neste dias fiquei muito curioso com a sua analise e fiz este procedimentos em meu servidor. um DELL poweredge 2900 com 36gb, o resultado foi o seguinte conforme abaixo;
counter_name cntr_value Gb
—————————– ————- —–
Target Server Memory (KB) 12582912 12
Total Server Memory (KB) 12582912 12
2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
Page Life Expectancy
———————–
6016
1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
type SPA Mem, MB MPA Mem,MB
———————- ————– ————-
CACHESTORE_SQLCP 2926,945312 62,664062
MEMORYCLERK_SQLGENERAL 94,992187 4,578125
USERSTORE_DBMETADATA 74,273437 0
MEMORYCLERK_SOSNODE 47,796875 21,023437
CACHESTORE_PHDR 43,101562 0
CACHESTORE_OBJCP 41,179687 0,320312
6 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
SPA Mem, KB MPA Mem, KB
————– ————–
3307,890625 107,921875
1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
Database Name Cached Size (MB)
——————- ——————-
DADOSADV 6910,523437
SPED 26,359375
Gostaria de sua analise e se possível algumas ideias
Obrigado Wagner,
No meu caso eu tinha um servidor de 4 GB de memória apenas. Dessa forma, qualquer ganho de memória já era muita coisa.
No seu caso com 36 Gb de memória, acredito não ser necessário executar esse procedimento.
O ganho seria mínimo.
Seu cache de queries ad hoc também é bem pequeno:
CACHESTORE_OBJCP 41,179687 0,320312
Muito bom o post Fabrício!
Me salvou hoje em um cliente, onde o sistema que eles utilizam estava muito lento, demorava demais em algumas partes o processamento, chegando a ter rotinas que demorava em média 1 segundo demorar mais de 1 minuto, ai no fim vi que a memória máxima utilizada estava em 1GB, aumentei pra 4 GB, ai voltou a processar as informações normalmente, sem travamentos e etc. E no começo da minha análise o Page life expectancy estava em média em 5 – 6, a hora que eu vi quase cai da cadeira kkkkkk, depois que eu aumentei o limite da memória para 4 GB depois de alguns instantes já estava superior a 300.
Obrigado.
Que bom que te ajudou Kaio!
Ola Fabricio boa tarde. Parabens pelo Artigo muito bom mesmo. Estou usando no meu ambiente já há algum tempo e me ajuda muito.
Ontem comecei a ter uma lentidão no meu ambiente que está me deixando de cabelo branco, mesmo com tudo normal (Contadores, Hardware, recursos enxutos), todos os meus bancos estão muito lentos. Já não sei mais o que analisar. Tens alguma dica para me ajudar?
Obrigado e um Abraço,
Claudinei
Obrigado Claudinei.
Já identificou as queries mais lentas?
Boa noite, Fabricio.. ótimo POST, estou com uma dúvida não sei se pode me ajudar, meu conhecimento em SQL é baixo.
Toda primeira consulta a uma tabela após a conexão do sistema ao banco de dados é lenta, chega a demorar mais de 7 minutos, após a primeira consulta, passa a ser de 1 segundo a mesma consulta. Isso pode ser alguma configuração básica que eu possa fazer? ou até mesmo a limpeza do cache?
Olá Celso,
Isso acontece pois na primeira vez que você faz o select o SQL Server tem que ir no disco, pegar as páginas de dados das tabelas, jogar para a memória e depois te dar o resultado.
Quando você executa a query pela segunda vez, essas páginas já estão na memória e você não precisa fazer o acesso a disco.
E disco é LENTO!!!
Bom Dia !
Fabricio,
Estamos desenvolvendo um aplicativo mobile usando WebServices para acesso a dados, usando algumas threads para consulta de movimentações disparadas em 5 em 5 segundos. Em alguns processos utilizamos Storeds Procedures e poucas chamadas está “estourando” o processamento até 100%. Não sei se esta faltando algum a boa pratica, nosso server de testes 4gb de ram e processador 2 nucleos de 2,4ghz , bem básico mas com 6 conexões ta pipocando, se puder dar uma força.
Abraços.
Bom Dia Cleiton,
Nesse caso você tem que monitorar as queries que estão executando nesse momento de 100% de processamento para ver se podem ser melhoradas…
Algumas dicas para monitorar isso:
http://www.fabriciolima.net/blog/2010/06/05/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-1/
http://www.fabriciolima.net/blog/2016/03/17/queries-do-dia-a-dia-criando-um-log-de-historico-da-sp_whoisactive/
Ok. Vou dar uma olhada, porém andei vendo algumas coisas que me deixou com a pulga atrás da orelha, na questão de acessos simultâneos no Sql Server, estou falando de um app que deva ter entre 10 a 50 mil usuários isso usando threads dispararia em torno de 3 a 15 mil requisições simultâneas falando em 30% de atividade, isso já me exigiria um balanceamento correto ? Não sei já já trabalhou com algo desse porte em questão de acesso simultâneo, estamos repensando a Lógica toda e talvez se for caso usar um push para o os Apps Android e IOS. Num Ambiente com 1gb ram e 1 núcleo para o sql quantas conexões acha que já me colocaria num gargalo?
Acesso simultâneo é bem relativo…
Se a consulta faz 100000 reads ou 10 reads por exemplo…
Vai depender do que os acessos estão fazendo e o consumo disso…
Fabricio, muito bom seu artigo. Não conheço SQL,mas trabalho com infra.. veja se consegue me ajudar. tenho um servidor dell 32gb de ram, com Server 2012 e SQL 2008 R2. e o banco de dados está bem lento.. a base é pequena(por volta de 1,5gb), mas ele sobe 5gb no processo do SQL na memoria. abaixo estão as informações conforme suas querys:
counter_name cntr_value Gb
Target Server Memory (KB) 25595344 24.41
Total Server Memory (KB) 4667264 4.45
type SPA Mem, MB MPA Mem,MB
CACHESTORE_SQLCP 3889.906250 42.265625
MEMORYCLERK_SOSNODE 32.546875 15.953125
USERSTORE_DBMETADATA 16.101562 0.000000
CACHESTORE_PHDR 11.164062 0.015625
USERSTORE_SCHEMAMGR 8.054687 1.179687
CACHESTORE_OBJCP 6.500000 0.195312
MEMORYCLERK_SQLSTORENG 2.382812 4.648437
SPA Mem, KB MPA Mem, KB
3976.359375 70.648437
Database Name Cached Size (MB)
producao 533.453125
Obrigado.
Fabio, sql subir memória não deve ser o motivo da sua lentidão. Sua base é pequena.
Você vai ter que dar uma olhada se tem lock ou nas queries lentas.
Boa tarde
Parabens pelo artigo. Retirei todas as querys para analise e fiz o Stored Procedure no entanto estou com uma duvida. A Stored Procedure não executa de forma automatica. é normal ou deveria ser por exemplo de 3 em 3 minutos como referiu no exemplo?
Obrigado.
Não entendi a pergunta.
O que faz para colocar a rodas a stored procedure. Cria um job no sql agent para ir rodando de 3 em 3 minutos?
sim. Se quer agendar algo no SQL, o agent é o nosso amigo.