Calculo: Consumo Buffer Pool

Ola Pessoal,

Apenas para compartilhar um simples script de como calcular o quanto de memoria o Buffer Pool esta consumindo.

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)] into #temp
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 — system databases
AND database_id <> 32767 — ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

select ‘DATA CACHE’ as TipoDeMemoria,SUM([Cached Size (MB)]) as TotalEmMB from #temp
union all
select ‘PLAN CACHE’ as TipoDeMemoria,(SUM(cast(size_in_bytes as bigint)) / 1024 / 1024) as TotalEmMB
from sys.dm_exec_cached_plans
union all
select ‘BUFFER POOL’ as TipoDeMemoria,(SUM(cast(size_in_bytes as bigint)) / 1024 / 1024) + (SELECT SUM([Cached Size (MB)]) from #temp)
from sys.dm_exec_cached_plans

Segue em um dos ambientes o resultado.

Imagem

Vale lembrar que aqui estou falando apenas de Buffer Pool, ou seja, nao estou mencionando o VAS (VIRTUAL ADDRESS SPACE) que será discutido em um proximo post.

Advertisements

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on May 21, 2012, in Administração, SQL Server, VirtualPass and tagged , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: