SQL Server 2005 – Identificando Memory Pressure II

Rodrigo's profileRodrigo's spacePhotosBlogLists

< Previous Next >

January 08

SQL Server 2005 – Identificando Memory Pressure II

Pessoal como já falamos em outra postagem, existem centenas de tipos de wait’s para descrever a situação pelo qual o banco está passando. Para identificarmos quais são os wait types, dê uma boa olhada na sys.dm_os_wait_stats.

Pois bem, tivemos um evento muito interessante certa manhã ao chegarmos no trabalho e identificarmos que os nossos servidores onde estão nossos VLDB’s estavam apresentando o seguinte sintoma:

- CPU: Normal

- Memória: Normal (inclusive Cache Hit Ratio)

- Disco: Normal

- Paging File: Normal

Porém estávamos com as consultas muito lentas, então resolvemos identificar algumas dessas e obviamente olharmos o plano de execução e tratar o gargalo, pois bem identificamos as consultas mas não conseguimos nem mesmo traçar o plano de execução dessas.

Como todo o processo de troubleshooting traçamos uma linha de atuação e fomos eliminando as possibilidades, chegamos a conclusão que havia alguma coisa errada com o comportamento do SQL Server.

Abrimos o Profiler e escolhemos alguns eventos, entre eles o de SP:Cache Miss, ao deixarmos o trace rodando durante algum tempo, observamos que exatamente as queries que estavam lentas, reportavam diversos eventos desse tipo. Esse evento nada mais é do que não ter encontrado o plano de execução no Procedure Cache.

Muito bem vale lembrar que o SQL Server possui diversos Memory Components entre eles os mais conhecidos Procedure Cache e Data Cache (Buffer Pool), esses componentes não fazem parte da memória que alocamos com Min e Max Server Memory parameter, com este parâmetro delimitamos apenas o tamanho do Buffer Pool.

Sendo assim fica a cargo do SQL Server gerenciar as outras áreas como o tamanho do Procedure Cache. sempre que precisar o SQL Server solicita memória para o Data Cache, mas muitas vezes a memória realmente está ocupada e não existem dirty pages para liberar.

O plano de atuação para resolver esse problema basicamente foi identificar se existia alguma cláusula ou parâmetro nas stored procedures que pudessem causar recompilação, concluímos que não.

A partir deste adotamos a estratégia de disponibilizarmos mais memória "fora" do Buffer Pool afim de que o SQL Server fizesse uso dessa área "liberada" para Procedure Cache, com isso diminuimos a quantidade de memória do parâmetro “Max Server Memory” e disponibilizamos memória para o Sistema Operacional e consequentemente para outros memory components do SQL Server, inclusive o Procedure Cache, a partir dessa alteração não houve mais incidentes reportando SP:Cache Miss.

Com isso podemos concluir mais uma vez que identificar problemas do tipo Memory Pressure pode tornar-se complicado e demandar muito tempo, vale utilizar todas as ferramentas disponíveis.

Até a próxima…

Abraços,

Rodrigo Fernandes

12:45 PM | Blog it | SQL Server Trobleshooting

Comments (8)

To add a comment, sign in with your Windows Live ID (if you use Hotmail, Messenger, or Xbox LIVE, you have a Windows Live ID). Sign in

Don't have a Windows Live ID? Sign up

	Rodrigowrote: Gustavo/Alex, a MemToLeave limitada aplica-se muito em sistemas 32bits para sistemas em 64bits que é o caso do nosso cliente. Veja o limite para a MemToLeave em 32bits é 2GB, podendo ser extendida com -g. Quanto a linha de troubleshooting veja que realmente não é tão simples monitorar esse comportamento, no nosso caso observamos que isso passou acontecer quando aumentamos a quantidade de memória para o Buffer Pool. Quanto aos autores de livros e literaturas não podemos descartar o casal Kimberly Tripp e Paul Randal (Randal, foi SDE para o CHECKDB a patente está pendente no nome dele). Jan. 12
	Alex Rosawrote: legal...vendo esse "post" conclui que preciso rever alguns pontos, pois algumas coisas que foram faladas eu sabia mas havia esquecido os detalhes e outras eu não sabia. valew pessoal... Jan. 11
	Gustavowrote: Olá Pessoal, Interessante o problema e a linha de Troubleshooting. Gostaria de fazer algumas considerações: - É possível extender a área de memória utilizada pelo SQL Server fora do Buffer Pool através do parâmetro de inicialização -g. Em diversos servidores que administro com milhares de usuários simultâneos e vários Linked Servers esse parâmetro é simplesmente obrigatório. Acho que diminuir o Max Memory talvez tenha ajudado se o BP estivesse competindo com a MemToLeave, mas há situações em que isso não acontece. O uso do AWE por exemplo disponibiliza um BP muito grande, mas um MemToLeave mais restrita e nesse caso não adianta diminuir o Max Memory, pois, a MemToLeave não é capaz de enxergar a memória disponível pelo AWE. O livro The Guru's Guide To SQL Server - Architeture & Internals aborda com muita profundidade os internals do SQL Server. Ainda que seja dedicado ao SQL Server 2000, seu conteúdo continua muito válido e concordo que é um páreo duro com a Kallen Delaney, embora eu ache que na maioria dos tópicos, a Kallen é mais didática enquanto o Ken tem mais profundidade (principalmente na integração Windows - SQL). Abs, Jan. 10
	Alex Rosawrote: oba...pois é, a minha opinião sobre isso é bem simples...a Microsoft não nos oferece uma documentação sobre a Arquitetura do MSSQL adequada. O Books Online é excepcional como fonte de informação geral (menos sobre Arquitetura que é muito superficial), eu pelo menos não achei esses detalhes no BOL. Jan. 9
	Rodrigowrote: Alex, você realmente bateu no ponto que eu gostaria de discutir, a memória que gerenciamos é o Buffer Pool que no Inside the Storage Engine é definido como Data Cache, quando outros memory components necessitam de memória solicitam ao Buffer Pool, porém nem sempre essa solicitação é atendida, chegando a casos extremos onde é necessário diminuirmos manualmente o BP, para que o SQL Server consiga alocar memória para outros componentes como o caso do Procedure Cache. Jan. 9
	Alex Rosawrote: Olá Pessoal, Se eu não estiver enganado, alguns pontos em relação ao uso de memória pelos diversos CACHEs que o MSSQL não estão bem definidos. Realmente o MSSQL pode buscar memória fora da área especificada no MEMORY POOL, mas isso somente em alguns casos. Segue um artigo que li a algum tempo e que explica melhor isso: http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx Um trecho citando um exemplo: "All SQL Server's components optimized for 8KB allocations so that they can allocate memory through SQLOS's single page allocator and consequently through Buffer Pool. However there are cases when a component requires large buffers. If it happens allocation will be either satisfied by memory node's multi page allocator or by virtual allocator. As you might guess that memory will be allocated outside of Buffer Pool. This is exactly why I don’t like term MemToLeave, SQL Server does allocate memory out of that area!" O que acham? Jan. 9
	Rodrigowrote: Fabiano na realidade a solução foi diminuir o espaço alocado para o Buffer Pool, liberando memória para o Sistema Operacional e consequentemente para os outros memory components do SQL Server inclusive o Procedure Cache. Abraço, Rodrigo Jan. 8
	Fabiano Neves Amorimwrote: Deixa eu ver se entendi, a solução foi aumentar um pouco a memória disponível para o SQL? A partir deste momento o SQL identificou que havia memória livre e já alocou para o Procedure Cache? Caso ainda não tenha visto, o Adam Machanic, cansou de passar por problemas relacionados a Procedure Cache,e incluiu um pedido para o time de dev do SQL mudar isso. Infelizmente sem sucesso. Caso tenha interesse veja em: http://sqlblog.com/blogs/adam_machanic/archive/2007/08/14/want-to-control-the-procedure-cache.aspx https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188 Abraço. Jan. 8

Trackbacks

The trackback URL for this entry is:

http://lobo-fernandes.spaces.live.com/blog/cns!9300AD1C5A0A745B!372.trak

Weblogs that reference this entry

None