Dica do Mês – Identificando pressão de memória interna e externa no Microsoft SQL Server

Olá comunidade, muito boa tarde.

Tudo bem? Mais um mês que chega ao final, graças a deus este mês de Julho foi complicado, espero que toda a correria que passei mesmo em período de férias não se repita por um longo tempo. Estamos no sétimo post desta nova sessão lançada no início de 2016, muito legal, já temos um pouco de história para contar em relação a aceitação dos visitantes do meu blog em relação a esta sessão.

Como você já deve estar acostumado quando visita o meu blog, todo final de mês é dedicado a post exclusivos das sessões Dica do Mês e Para que Serve. No último post publicado no mês de Junho, apresentei um conteúdo mais focado para área acadêmica, destaquei um dos conceitos mais importantes quando estamos falando de Modelagem de Banco de Dados, a tão temida e misteriosa Normalização(kkkkk), algo que para muitos é um bicho de sete cabeças, mas na verdade não se passa de um gatinho lindo e manhoço.

Como diria aquela boa e velha “Amanheceu pequeno a viola botei na sacola e fui viajar”, no meu caso seria mais ou menos assim: “Amanheceu entrei no meu carro peguei minha mochila e fui trabalhar, anoiteceu entre no meu carro peguei minha apostila e fui lecionar……”.

Deixando a brincadeira de lado no post de hoje vou falar um pouco sobre como podemos identificar o consumo de memória por parte do SQL Server ou Windows através do Management Studio ou Scripts, estou me referindo a chamada pressão de memória interna ou externa.

Então, seja bem vindo a mais uma dica do mês, começa aqui a Dica do Mês – Identificando pressão de memória interna e externa no SQL Server.


Introdução

Uma das maiores preocupação de qualquer profissional de infraestrutura ou administrador de banco de dados é saber se seus recursos de hardware estão atendendo as demandas solicitadas por suas aplicações e usuários.

Quando se referimos a consumo de memória por parte de um servidor de banco de dados, existem pontos que podem nos direcionar a uma pequena confusão, dentre elas destaco:

  • Identificar o quanto uma sessão ou transação esta consumindo de memória;
  • Identificar o quanto um ou mais bancos de dados estão consumindo de memória; ou
  • Identificar o quanto um servidor ou instância de sql server esta consumindo de memória.

Você pode fazer isso, bom identificar o quanto o SQL Server esta consumindo de memória é fácil, basta eu utilizar as ferramentas Task Manager ou Resource Monitor para obter estes dados, na verdade não é bem assim, ambas as ferramentas podem ajudar a encontra vestígios deste possível consumo, o que particularmente não pode ser considerada uma tarefa complexa nem tão simples.

Mas quando se referimos a pressão de memória, o cenário muda totalmente, trata-se de uma situação onde nosso servidor pode por diversos motivos estar sofrendo uma sobrecarga de processamento interno ou externo que gera um consumo elevado ou até mesmo falta de memória para atender todas as requisições que estão sendo disparadas para ele. Seguindo nesta linha de raciocínio a Microsoft disponibilizou a partir da versão do Microsoft SQL Server 2008 e 2008 R2 duas DMVs – Dynamic Management Views (Visões de Gerenciamento Dinâmico):

  • sys_dm_os_ring_buffers: Retorna um conjunto de rings “anéis” de dados que nos permitem relacionar com outras dmv afim de identificarmos problemas de erros de conectividade, faixa exceções, além disso, utilizada também como mecanismo para identificar a saúde de nosso ambiente de banco de dados, sem proporcionar qualquer tipo de pressão de memória, deadlocked ou mudança no comportamento dos agendadores de tarefas em execução.
  • sys_dm_os_sys_info: Retorna um conjunto de informações relacionadas as recursos de hardware utilizados pelo SQL Server, como por exemplo:
    • CPU_TICKS;
    • CPU_COUNT; e
    • Physical_Memory_KB.

Você pode estar pensando, bom já que a sys_dm_os__sys_info apresenta um conjunto de informações relacionadas a hardware que esta em uso pelo SQL Server, é através dela que eu poderei identificar o consumo de memória. Bom se você me pergunta-se isso neste momento a minha resposta seria NÃO.

Porque não, porque estes dados do que esta sendo consumido de maneira interna pelo SQL Server ou até mesmo externamente pelo Windows que possui relação são para muitos conhecidos como ring_buffers ou melhor dizendo ring “anéis”, e são através destes anéis ou basicamente falando com base nestes neles que temos como através da DMV sys.dm_os_ring_buffers coletar os ring_buffers que estão consumindo recursos de memória.

Então se você me perguntar se através da dmv sys.dm_os_ring_buffers podemos obter informações sobre pressão interna ou externa de memória a resposta será SIM. Pois bem, o segredo foi revelado o pulo do gato para saber detalhes escondidos de como o SQL Server coleta e armazena estes anéis é relavado pela sys.dm_os_ring_buffers.

Agora que sabemos os recuros que vamos utilizar para coletar estes dados e identificar os recursos  que estão consumindo memória em nosso ambiente, para tal utilizaremos o bloco de código 1 apresentado a seguir:

— Bloco de Código 1 – Identificando pressão de memória interna e externa —

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] – tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value(‘(//Record/ResourceMonitor/Notification)[1]’, ‘varchar(30)’) AS [Notification_type],
cast(record as xml).value(‘(//Record/MemoryRecord/MemoryUtilization)[1]’, ‘bigint’) AS [MemoryUtilization %],
cast(record as xml).value(‘(//Record/MemoryNode/@id)[1]’, ‘bigint’) AS [Node Id],
cast(record as xml).value(‘(//Record/ResourceMonitor/IndicatorsProcess)[1]’, ‘int’) AS [Process_Indicator],
cast(record as xml).value(‘(//Record/ResourceMonitor/IndicatorsSystem)[1]’, ‘int’) AS [System_Indicator],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[1]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[2]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[3]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/MemoryNode/ReservedMemory)[1]’, ‘bigint’) AS [SQL_ReservedMemory_KB],
cast(record as xml).value(‘(//Record/MemoryNode/CommittedMemory)[1]’, ‘bigint’) AS [SQL_CommittedMemory_KB],
cast(record as xml).value(‘(//Record/MemoryNode/AWEMemory)[1]’, ‘bigint’) AS [SQL_AWEMemory],
cast(record as xml).value(‘(//Record/MemoryNode/SinglePagesMemory)[1]’, ‘bigint’) AS [SinglePagesMemory],
cast(record as xml).value(‘(//Record/MemoryNode/MultiplePagesMemory)[1]’, ‘bigint’) AS [MultiplePagesMemory],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]’, ‘bigint’) AS [TotalPhysicalMemory_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]’, ‘bigint’) AS [AvailablePhysicalMemory_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalPageFile)[1]’, ‘bigint’) AS [TotalPageFile_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailablePageFile)[1]’, ‘bigint’) AS [AvailablePageFile_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]’, ‘bigint’) AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’, ‘bigint’) AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value(‘(//Record/@id)[1]’, ‘bigint’) AS [Record Id],
cast(record as xml).value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type],
cast(record as xml).value(‘(//Record/@time)[1]’, ‘bigint’) AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’
ORDER BY rbf.timestamp ASC

 

Perfeito, após executar este bloco de código o Management Studio vai retornar as linhas de recursos internos e externos que podem estar gerando uma possível pressão de memória para seu servidor ou instância SQL Server, isso vai variar e depender muito de cada ambiente, hardware, versão de sistema operacional e principalmente versão do SQL Server e service pack instalado.


Sendo assim chegamos ao final de mais uma dica do mês.

Espero que você tenha gostado deste post,  acredito que as informações e exemplo publicadas aqui possam de alguma maneira ajudar e colaborar em suas atividades diárias, profissionais e ou acadêmicas.

Fique ligado nos próximos meses, novas dicas relacionadas a sys.dm_os_ring_buffers serão postadas, todas voltadas para ajudar identificar como solucionar problemas que o SQL Server pode estar apresentando e você ainda não percebeu dentre eles erros de segurança.

Desejo um forte abraço, agradeço mais uma vez a sua visita.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s