De olho na memória e no Buffer Pool

Olá,

Um dos componentes essenciais para o bom funcionamento do SQL Server é a memória do Sistema Operacional. Para o SQL Server, de maneira geral, quanto mais memória estiver disponível para ele utilizar, melhor. Mas, da memória disponível no servidor, quanto o SQL Server está utilizando? Vamos descobrir.

Primeiro, vamos dar uma olhada no total de memória do servidor e o quanto está alocado neste momento para o SQL Server. Com a consulta abaixo, temos estas informações:

-- Quanto de memória tem o servidor e o SQL Server?
select 
    (mem.total_physical_memory_kb / 1024) as total_physical_memory_MB,
    (available_physical_memory_kb / 1024) as available_physical_memory_MB,
    system_memory_state_desc
from sys.dm_os_sys_memory mem

O resultado da consulta é o tota de memória disponível no servidor, o total de memória alocada para o SQL Server neste momento, e uma descrição da situação atual a respeito da memória.

Olhando pra “dentro da caixa”, temos um componente fundamental para o SQL Server que é o Buffer Pool (muito bem explicado pelo Fabricio Catae em seu blog aqui). O Buffer Pool é o maior consumidor de memória dentro do SQL Server e tem fator fundamental em questões de desempenho, portanto, temos de ficar de olho nele também.

A consulta abaixo nos retorna dois valores do Buffer Pool:

-- Como está o Buffer Pool?
select
    (info.bpool_committed * 8 ) / 1024 as bpool_commited_MB,
    (info.bpool_commit_target * 8 ) / 1024 as bpool_commit_target_MB
from sys.dm_os_sys_info info

O valor de bpool_commited no diz o tamanho atual do Buffer Pool e o valor de bpool_commit_target no diz o tamanho que o Buffer Pool quer atingir. Se o valor do bpool_commited for MENOR que o valor do bpool_commit_target, significa que o Buffer Pool tem espaço para crescer. Agora, se for o contrário, o valor do bpool_commited for MAIOR que o valor do bpool_commit_target, significa que o tamanho do Buffer Pool deverá ser reduzido.

Se quisermos entrar um pouco mais no detalhe do que tem dentro do Buffer Pool, podemos utilizar como referência a consulta abaixo, criada por Tony Voellm e Gaurav Bindlish e divulgada aqui.

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Até mais!

4 Respostas para “De olho na memória e no Buffer Pool

  1. Pingback: Monitorando de forma automatizada a memoria de um ambiente OLTP « Fabrizzio A. Caputo

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