SQL Server buffer pool

On 05 Nov, 2015

By : Charith Silva

No Comments

SQL Server uses a buffer pool of memory to cache data pages, reducing I/O demand and improving overall performance. SQL Server buffer pool also called as buffer cache is a place in the SQL Server system memory that is used for caching table and index 8KB data pages as they are modified or read from disk.

Below script will return data page count and total page size that are currently in the SQL Server buffer pool. When a data page is read from disk, the page is copied into the SQL Server buffer pool first and cached for reuse. DBCC DROPCLEANBUFFER command can be used to clean the buffer. Also The Buffer Pool Extension is an extension for the SQL Server buffer pool that targets non-volatile storage devices, such as solid-state disk drives (SSDs). When the Buffer Pool Extension is enabled, SQL Server uses it for data pages in a similar way to the main buffer pool memory.

SELECT [DatabaseName],
[Dirty Page Count],
[Dirty Page Count] * 8 / 1024 AS [Dirty Page Size(MB)],
[Clean Page Count],
[Clean Page Count] * 8 /1024 AS [Clean Page Size(MB)]
FROM 
(
  SELECT 
  DB_NAME([database_id]) AS [DatabaseName],
  SUM(CASE WHEN ([is_modified]=1) THEN 1 ELSE 0 END) AS [Dirty Page Count],
  SUM(CASE WHEN ([is_modified]=1) THEN 0 ELSE 1 END) AS [Clean Page Count]
  FROM sys.dm_os_buffer_descriptors 
  GROUP BY [database_id]
) AS buffers
ORDER BY [Databasename]

SELECT [DatabaseName],

[Dirty Page Count],

[Dirty Page Count] * 8 / 1024 AS [Dirty Page Size(MB)],

[Clean Page Count],

[Clean Page Count] * 8 /1024 AS [Clean Page Size(MB)]

FROM

(

SELECT

DB_NAME([database_id]) AS [DatabaseName],

SUM(CASE WHEN ([is_modified]=1) THEN 1 ELSE 0 END) AS [Dirty Page Count],

SUM(CASE WHEN ([is_modified]=1) THEN 0 ELSE 1 END) AS [Clean Page Count]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id]

) AS buffers

ORDER BY [Databasename]

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva