Buffer pool reports

I’ve been looking at SQL Server buffer pool usage for quite a while, but at my current job, I had the idea of tracking it daily.   The stats are taken in the early evening, so it may not properly track what happens during the day or at night.   But it is meant to give a general idea of  memory usage.

I’ll start with an image, to inspire you to read the rest of the post.

Image

The real work here happens with the nightly data collection.

Here’s what you need to set up the collection.  First, set up the tables.

CREATE TABLE [dba].[BufferPool_Current](
[BufferPoolID] [INT] IDENTITY(1,1) NOT NULL,
[CollectionDate] [DATE] NULL,
[DB_NAME] [VARCHAR](50) NULL,
[db_buffer_pages] [INT] NULL,
[db_buffer_MB] [INT] NULL,
[db_buffer_percent] [DECIMAL](6, 3) NULL,
CONSTRAINT [PK_BufferPoolC] PRIMARY KEY CLUSTERED
( [BufferPoolID])
)

CREATE TABLE [dba].[BufferPool_Historical](
[BufferPoolID] [int] IDENTITY(1,1) NOT NULL,
[CollectionDate] [date] NULL,
[DB_NAME] [varchar](50) NULL,
[db_buffer_pages] [int] NULL,
[db_buffer_MB] [int] NULL,
[db_buffer_percent] [decimal](6, 3) NULL,
CONSTRAINT [PK_BufferPoolH] PRIMARY KEY CLUSTERED
( [BufferPoolID] )
)

Schedule this SQL to run every day:

TRUNCATE TABLE dba.BufferPool_Current;

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
INSERT dba.BufferPool_Current
( CollectionDate, db_name, db_buffer_pages, db_buffer_MB, db_buffer_percent )
SELECT
GETDATE() AS CollectionDate,
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

INSERT dba.BufferPool_Historical
( CollectionDate, [db_name], db_buffer_pages, db_buffer_MB, db_buffer_percent )
SELECT CollectionDate, [db_name], db_buffer_pages, db_buffer_MB, db_buffer_percent
FROM dba.BufferPool_Current

Then in SSRS, create a Dataset with the following query.

This will pull the historical data for any databases that either
1. Have held over 5% of the buffer pool on average, the last 3 months
2. Have used over 20% of the buffer pool at any time during the last 3 months

SELECT CollectionDate, [db_name], db_buffer_percent
FROM [Admin].[dba].[BufferPool_Historical]
WHERE
(
[db_name] IN(
SELECT DISTINCT [db_name]
FROM [Admin].[dba].[BufferPool_Historical]
WHERE CollectionDate >= dateadd(month,-3, getdate())
GROUP BY [db_name]
HAVING AVG(db_buffer_percent) > 5
)
OR [db_name] IN(
SELECT DISTINCT [db_name]
FROM [Admin].[dba].[BufferPool_Historical]
WHERE db_buffer_percent > 20
AND CollectionDate >= dateadd(month,-3, getdate())
)
)
AND CollectionDate >= dateadd(month,-3, getdate())

Create a line chart with the dataset.

For the values area, map out db_buffer_percent.
For the Category Groups, use CollectionDate
For the Series Groups, use db_name

Advertisements
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

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