Quando se referimos a utilização de memória por parte do SQL Server, muitas discussões, dúvidas e até mesmo informações
desencontradas são apresentadas. Para muitos profissionais, desenvolvedores e até mesmo entusiastas do SQL Server, este assunto é algo que acaba se tornando um grande enigma.
Básicamente uma porcentagem da memória consumida pelo SQL Server refere-se ao pool de buffers(exclusivamente, por dados em uso), independente da instância que possa estar sendo utilizada.
Mas como podemos tentar descobrir como este sendo consumido pelo SQL Server? Quais objetos e até mesmo Banco de Dados estão fazendo uso dos pools de buffers?
Pois bem, é justamente a respostas para estas perguntas que vou procurar responder neste artigo!!!
Para realizarmos este tipo de busca de informações, necessitamos vasculhar um pouco das informações armazenadas pelo SQL Server em seu catálogo de visões. Isso pode ser considerado algo difícil de se dizer. Qual dos seus bancos de dados estão consumindo mais memória de pool de buffer, e mais ainda, que objetos dentro desses bancos de dados, podem ser forçando este tipo de situação.
No que estas informações podem me ajudar?
Estas respostas podem se tornar informações de grande importância, por exemplo, se você está considerando uma mudança de
aplicativo para dividir seu banco de dados em vários servidores ou tentando identificar bancos de dados que são candidatos à consolidação. Além disso, se o seu ambiente estiver apresentando algum tipo de consumo excessivo de memória em determinados aplicativos ou funcionalidades em execução.
Utilizando a DMV Sys.dm_os_buffer_descriptors.
Para conseguirmos encontrar as informações referentes aos buffers existentes em cache colocados pelo SQL Server após a execução de suas transações, podemos utilizar a exibição de gerenciamento dinâmico (DMV) introduzido no SQL Server 2005, chamada sys.dm_os_buffer_descriptors. Esta DMV contém uma linha para cada página que foi colocado no cache na área de buffer.
Usando esta DMV, você poderá determinar rapidamente quais bancos de dados estão utilizando a maioria da memória de pool do buffer. Depois de ter identificado os bancos de dados que estão ocupando grande parte da área de buffer, fica mais fácil detalhar de
forma individual o que esta sendo consumido por cada banco.
Para demonstrar como podemos começar a utilizar a sys.dm_os_buffer_descriptors, apresento a seguir a Listagem 1.
Através deste código conseguiremos descobrir exatamente como grande pool de buffers atualmente é esta sendo gerado fazendo uso de outra DMV (sys.dm_os_performance_counters) o que vai permitir calcular a porcentagem de área de buffer que está sendo usada por cada banco de dados, conforme apresenta a Figura 1.
Observação: Para consultar a DMV sys.dm_os_buffer_descriptiors é necessário possuir permissão de acesso as visões de estado do servidor(View Server State). Segue abaixo a Listagem 1:
— Listagem 1: Consultando o pool de buffers em uso –
–Declarando a varíavel @Buffers_EmUso —
Declare @Buffers_EmUso Int;
/* Acumando o valor dos contadores na variável @Buffers_EmUso, filtrando pelo Object_Name=Buffer Manager e Counter_Name=Total Pages*/
Select @Buffers_EmUso = cntr_value From Sys.dm_os_performance_counters
Where Rtrim(Object_name) LIKE ‘%Buffer Manager’
And counter_name = ‘Total Pages’;
— Declarando a CTE Buffers_Pages para contagem de Buffers por página —
;With DB_Buffers_Pages AS
(
SELECT database_id, Contagem_Buffers_Por_Pagina = COUNT_BIG(*)
From Sys.dm_os_buffer_descriptors
Group By database_id
)
— Retornando informações sobre os pools de Buffers por Banco de Dados com base na CTE DB_Buffers_Pages —
Select Case [database_id] WHEN 32767 Then ‘Recursos de Banco de Dados’ Else DB_NAME([database_id]) End As ‘Banco de Dados’,
Contagem_Buffers_Por_Pagina,
‘Buffers em MBs por Banco’ = Contagem_Buffers_Por_Pagina / 128,
‘Porcentagem de Buffers’ = CONVERT(DECIMAL(6,3), Contagem_Buffers_Por_Pagina * 100.0 / @Buffers_EmUso)
From DB_Buffers_Pages
Order By ‘Buffers em MBs por Banco’ Desc;
Figura 1 – Pool de Buffers utilizados por Bancos de Dados.
Você pode observar através da Figura 1, que os os Bancos de dados de Sistema foram incluídos em nossa listagem, caso você deseje retornar somente seus próprios bancos de dados, basta realizar uma pequena alteração na CTE DB_Buffers_Page.
Acrescente na claúsula Where a filtragem por bancos de dados usuário, algo que poderá variar entre as versões do SQL Server,
inclusive na próxima versão por enquando denominada Denali, que deverá apresentar um novo banco de dados para os serviços de integração, chamado SSISDB.
Analisando o resultado apresentado na Figura 1, podemos observar que neste momento na minha instância SQL Server, o Resource DB(Recursos Internos de Banco de Dados) estão ocupando a maior porcentagem de Pool Buffers, aproximadamente 29% de todos os recursos de pools buffers disponíveis. Mas a análise que pretendo apresentar neste artigo, será realizado sobre um dos meus bancos de dados, vou então utilizar o banco de dados denominado CRIPTOGRAFIA,
que esta consumindo aproximadamente 13% dos recursos de pools buffers disponíveis.
Então, agora que sabemos que este banco de dados esta ocupando uma parte considerável dos meus recursos disponíveis para o SQL Server, vamos posteriormente a esta análise, começar a vasculhar quais os objetos estão consumindo memória dentro deste banco.
Para realizarmos este procedimento, podemos utilizar mais uma vez a DMV, sys.dm_os_buffer_descriptors só que desta vez, em vez de agregar as contagens de página no nível do banco de dados, nós podemos utilizar um conjunto de exibições do catálogo para determinar o número de páginas (e, portanto, quantidade de memória) dedicado a cada objeto. Neste caso vou utilizar as seguinte visões de catálogo:
- Sys.partitions: Contém uma linha para cada partição de todas as tabelas e índices no banco de dados. Todas as tabelas e índices no SQL Server 2008 contêm pelo menos uma partição, estejam ou não divididos
explicitamente. - Sys.allocation_units: Contém uma linha para cada unidade de alocação no banco de dados.
- Sys.objects: Contém uma linha para cada objeto criado pelo usuário no banco de dados.
- Sys.indexes: Contém uma linha para cada índice criado pelo usuário no banco de dados.
Agora que já sabemos quais visões de catálogo vou utilizar em conjunto com dmv sys.dm_os_buffers_descriptions, podemos executar a Listagem 2 apresentada a seguir:
— Listagem 2: Retornando pools Buffers de Objetos por Banco de Dados —
USE CRIPTOGRAFIA
GO
— Declarando a CTE Buffers_Pages para retorno dos Objetos alocados em Pool —
;WITH DB_Buffers_Pages_Objetos AS
(
Select
SO.name As Objeto,
SO.type_desc As TipoObjeto,
COALESCE(SI.name, ”) As Indice,
SI.type_desc As TipoIndice,
p.[object_id],
p.index_id,
AU.allocation_unit_id
From sys.partitions AS P INNER JOIN sys.allocation_units AS AU
ON p.hobt_id = au.container_id
INNER JOIN sys.objects AS SO
ON p.[object_id] = SO.[object_id]
INNER JOIN sys.indexes AS SI
ON SO.[object_id] = SI.[object_id]
AND p.index_id = SI.index_id
Where AU.[type] IN (1,2,3)
And SO.is_ms_shipped = 0
)
— Retornando informações sobre os pools de Buffers de Objetos por Banco de Dados com base na CTE DB_Buffers_Pages_Objetos —
Select Db.Objeto, Db.TipoObjeto As ‘Tipo Objeto’,
Db.Indice,
Db.TipoIndice,
COUNT_BIG(b.page_id) As ‘Buffers Por Página’,
COUNT_BIG(b.page_id) / 128 As ‘Buffers em MBs’
From DB_Buffers_Pages_Objetos Db INNER JOIN sys.dm_os_buffer_descriptors AS b
ON Db.allocation_unit_id = b.allocation_unit_id
Where b.database_id = DB_ID()
Group By Db.Objeto, Db.TipoObjeto, Db.Indice, Db.TipoIndice
Order By ‘Buffers Por Página’ Desc, TipoIndice Desc;
Figura 2 – Pool de Buffers utilizados por Objetos dentro do Banco de Dados Criptografia.
Analisando o resultado apresentado na Figura 2, podemos observar os objetos, seus respectivos índices, a quantidade de buffers por página e buffers em megabytes. Vale ressaltar que somente a tabela Eventos possui um índice clusterizado.
Outra observação importante todos os pool de buffers possuem um fluxo constante, e que esta consulta apresenta exatamente o último pool de buffer objetos do sistema, estes números vão sempre variar, e conforme o uso dos objetos os mesmo poderam aumentar de acordo com sua carga de dados. Ainda assim, isso deve dar uma ideia de quais objetos estão usando mais de seu pool de buffers.
Ao investigar o desempenho de seus servidores, dados de pool do buffer são apenas uma parte da imagem, mas que em muitas vezes esquecido pelos Profissionais durante os procedimentos de manutenção ou investigação.
Bom após esta enchurrada de informações, acredito ter conseguido atinguir o objetivo deste artigo, apresentando como o SQL Server trabalha com a memória em seus bancos de dados e objetos. De que forma ocorre o consumo deste recursos, qual a importância dos pools de buffers disponíveis para estes objetos.
Espero que você tenha gostado de mais este artigo, que as informações apresentas aqui sobre Consumo de Memória, Pools de Buffers, possam ser úteis no seu trabalho e estudos.
Agradeço a sua visita, até o próximo artigo.
Valeu.
———————————————————————————————————————————————————-
Referências: http://www.mssqltips.com/tip.asp?tip=2393 – Aaron Bertrand.
Books On-Line SQL Server 2008 R2.
Junior Galvão - MVP - Data Platform 