Verificando o Buffer Cache do SQL Server – dm_os_buffer_descriptors

Olá pessoal!

Após alguns meses sem postar, venho aqui falar sobre um assunto muito importante para o SQL Server: “O Buffer Cache”.

O Buffer Cache é a memória que o SQL Server utiliza para executar planos de execução, alocar páginas e rodar as mais variadas querys em uma instância.

gerenciador do Buffer

A consulta em memória é muito mais rápida, que uma consulta em disco (paginação). Para limpeza do Buffer Cache primeiramente rode o comando:

--Persiste as páginas de alteração no disco
CHECKPOINT

Depois rode o comando:

--Remove todas as páginas existentes no Buffer Cache
DBCC DROPCLEANBUFFERS

OBS.: A operação acima não é recomendada para ambientes críticos.

Conforme a technet: “http://technet.microsoft.com/pt-br/library/aa337525(v=sql.105).aspx”, um buffer é uma página de 8KB de memória, ou seja, o buffer cache é dividido em várias páginas de 8KB.

Conforme as consultas vão ocorrendo o Gerenciador do Buffer Cache vai alocando novas páginas e substituindo as páginas menos utilizadas e ou mais antigas, para atender as demandas do banco. Esse processo de “Substituição/Novas Páginas” é conhecido como Clock Hands (Ponteiro de Relógio). É possível pesquisar o Clock Hands através da Dynamic View “sys.dm_os_memory_cache_clock_hands “. Para nos ajudar a verificar a volumetria do Buffer Cache, existe uma Dynamic View : sys.dm_os_buffer_descriptors, que lista todas páginas alocadas no Buffer Cache por Banco de Dados.

Fiz uma procedure que traz o tamanho total do buffer da instância em relação ao Banco de dados e a segunda opção que detalha o tamanho alocado por objeto de banco.


USE master
GO
/*
/************Modelos de Execução**************/
--Retorna os Databases que estão usando o Buffer Cache
EXEC master.dbo.SP_VerificaBufferCache --"Sem Parâmetro" 
--Retorna os Objetos do Banco de Dados Especificado que estão usando o Buffer Cache 
EXEC master.dbo.SP_VerificaBufferCache 'NomeBanco' 
*/
CREATE PROCEDURE [dbo].[SP_VerificaBufferCache]

@NomeBanco varchar(300) = NULL

AS
SET NOCOUNT ON

IF OBJECT_ID ('tempdb..#dm_os_buffer_descriptors') IS NOT NULL DROP TABLE #dm_os_buffer_descriptors
IF OBJECT_ID ('tempdb..#QTDPaginasBufferCachePorBanco') IS NOT NULL DROP TABLE #QTDPaginasBufferCachePorBanco

CREATE TABLE #QTDPaginasBufferCachePorBanco
(NomeBanco VARCHAR(300),NomeObjeto VARCHAR(500),QTDPaginasEmCacheObjeto INT,ComandoSPSpaceUsed NVARCHAR(1000))

--Carrega a tabela temporária #dm_os_buffer_descriptors
SELECT *
INTO #dm_os_buffer_descriptors
FROM sys.dm_os_buffer_descriptors

IF @NomeBanco IS NULL

BEGIN
--Top Databases pela Qtd de páginas no buffer cache.
SELECT NomeBanco,
QTDPaginasEmCacheDB,
CASE WHEN (QTDPaginasEmCacheDB * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheDB * 8)) + ' KB'
WHEN (QTDPaginasEmCacheDB * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheDB,
CONVERT(DECIMAL(10,4),CONVERT(decimal(10,2),QTDPaginasEmCacheDB) * 100.00 /(SELECT CONVERT(decimal(10,2),COUNT(*)) FROM #dm_os_buffer_descriptors)) AS [PorcentagemUso],
CASE WHEN ((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)) + ' KB'
WHEN ((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),(((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),(((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024/1024)) + ' GB'
END AS TamanhoTotalBufferCache

FROM (
SELECT DB_NAME(database_id) as NomeBanco,
COUNT(*)AS QTDPaginasEmCacheDB
FROM #dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ) AS Top_DB

ORDER BY QTDPaginasEmCacheDB DESC;
END

IF @NomeBanco IS NOT NULL

BEGIN

DECLARE @SQL nvarchar(MAX) =
--Qtd Páginas por Objeto e Banco
N'USE '+@NomeBanco+'

INSERT INTO #QTDPaginasBufferCachePorBanco
SELECT
db_name(database_id) as NomeBanco,
name as NomeObjeto,
COUNT(*) AS QTDPaginasEmCacheObjeto,
''EXEC '+@NomeBanco+'.dbo.sp_spaceused ''+name+'' '' As ComandoSPSpaceUsed
FROM #dm_os_buffer_descriptors AS bd
INNER JOIN (SELECT object_name(object_id) AS name,
index_id,
allocation_unit_id
FROM '+@NomeBanco+'.sys.allocation_units AS au
INNER JOIN '+@NomeBanco+'.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL

SELECT
object_name(object_id) AS name,
index_id,
allocation_unit_id
FROM '+@NomeBanco+'.sys.allocation_units AS au
INNER JOIN '+@NomeBanco+'.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
WHERE database_id = DB_ID('''+@NomeBanco+''')
GROUP BY name, db_name(database_id)
ORDER BY QTDPaginasEmCacheObjeto DESC'

--Insere na tabela temporária #QTDPaginasBufferCachePorBanco
EXEC (@SQL)

--Qtd de Páginas no Buffer Cache por Objeto de um Banco
SELECT t1.NomeBanco,
t1.NomeObjeto,
QTDPaginasEmCacheObjeto,
CASE WHEN (QTDPaginasEmCacheObjeto * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheObjeto * 8)) + ' KB'
WHEN (QTDPaginasEmCacheObjeto * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheObjeto * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheObjeto * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheObjeto,
CASE WHEN (QTDPaginasEmCacheDB * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheDB * 8)) + ' KB'
WHEN (QTDPaginasEmCacheDB * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheDB,
convert(decimal(10,4),convert(decimal(10,2),QTDPaginasEmCacheObjeto) * 100.00 / CONVERT(decimal(10,2),QTDPaginasEmCacheDB)) AS [PorcentagemUso] ,
t1.ComandoSPSpaceUsed

FROM #QTDPaginasBufferCachePorBanco t1
inner join (SELECT DB_NAME(database_id) as NomeBanco,
COUNT(*)AS QTDPaginasEmCacheDB
FROM #dm_os_buffer_descriptors
--Aqui é filtrado o banco da tabela temporária #dm_os_buffer_descriptors
WHERE DB_NAME(database_id) = @NomeBanco
GROUP BY DB_NAME(database_id)) t2 on t1.NomeBanco = t2.NomeBanco

ORDER BY QTDPaginasEmCacheObjeto DESC

END

Espero que tenha ajudado pessoal!

Cristo os abençoe!!!

Anúncios

Um comentário sobre “Verificando o Buffer Cache do SQL Server – dm_os_buffer_descriptors

Deixe um comentário

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 )

Foto do Google+

Você está comentando utilizando sua conta Google+. 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 )

Conectando a %s