SQL Server Buffer Pool Distribution

Key points about SQL Server buffer pool:

1, A buffer is an 8-KB page in memory, the same size as a data or index page. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server.

2, Data is written back to disk only if it is modified. Data in the buffer cache can be modified multiple times before being written back to disk. For each logical write, a transaction log record is inserted in the log cache that records the modification.

3, Buffer pool ramp-up is the interval between SQL Server startup and when the buffer cache obtains its memory target.

4, In SQL Server 2005 Enterprise Edition, ramp-up is expedited by transforming single-page read requests into aligned eight-page(one extent/64KB) requests.

5, The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be “trimmed” from either end if the corresponding pages are already present in the cache.

6. The storage engine uses prefecting to speed base table lookups from nonclustered indexes. Prefetching is used regardless of whether the table has a clustered index. SQL Server Enterprise Edition uses more prefetching than other editions of SQL Server, allowing more pages to be read ahead. The level of prefetching is not configurable in any edition.

7. In SQL Server Enterprise Edition, the advanced scan feature allows multiple tasks to share full table scans. This mechanism is also called “merry-go-round scanning” and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

8. When the buffer manager writes a page, it searches for adjacent dirty pages that can be included in a single gather-write operation. Adjacent pages have consecutive page IDs and are from the same file; the pages do not have to be contiguous in memory. The search continues both forward and backward until one of the following events occurs:

• A clean page is found.
  
• 32 pages have been found.
  
• A dirty page is found whose log sequence number (LSN) has not yet been flushed in the log.
  
• A page is found that cannot be immediately latched.
  

In this way, the entire set of pages can be written to disk with a single gather-write operation.

9. Just before a page is written, the form of page protection specified in the database is added to the page. If torn page protection is added, the page must be latched EX(clusively) for the I/O. This is because the torn page protection modifies the page, making it unsuitable for any other thread to read. If checksum page protection is added, or the database uses no page protection, the page is latched with an UP(date) latch for the I/O. This latch prevents anyone else from modifying the page during the write, but still allows readers to use it.

10. The lazy writing, eager writing, and checkpoint processes do not wait for the I/O operation to complete.

Query 1: Check the buffer pool size:
/*
bpool_committed:        Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.

bpool_commit_target:    Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes,the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.
*/

select bpool_committed,bpool_commit_target,bpool_visible from sys.dm_os_sys_info

– or use this dbcc command

dbcc memorystatus
go

– looking(searching) for this section of the output

Buffer Counts                  Buffers
—————————— ——————–
Committed                      2621440
Target                         2621440
Hashed                         34053
Stolen Potential               2382049
External Reservation           0
Min Free                       512
Visible                        2621440
Available Paging File          11301033
(8 row(s) affected)

Query 2: Removes all clean buffers from the buffer pool. Don’t try this on production

— run this on the DBs to be tested. This forces all dirty pages for the current database
— to be written to disk and cleans the buffers.  

checkpoint
go

dbcc dropcleanbuffers [ WITH NO_INFOMSGS ] 
go

Query 3: Check the buffer pool distribution among all the databases:

DECLARE @total_buffer INT;
SELECT @total_buffer = bpool_committed from sys.dm_os_sys_info;

;WITH db_buffer_pages AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM
sys.dm_os_buffer_descriptors
 –WHERE database_id >4
GROUP BY database_id
)
SELECT
[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_pages * 8192/1024/1024
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM db_buffer_pages
ORDER BY db_buffer_MB DESC;

Query 4: Check the number of total buffer pool dirty pages:

DECLARE @total_buffer INT;
SELECT @total_buffer = bpool_committed from sys.dm_os_sys_info;

SELECT
count_big(*) as total_db_buffer_dirty_pages,
count_big(*)/128 as total_db_buffer_dirty_pages_MB,
total_db_buffer_dirty_percent =
CONVERT(DECIMAL(6,3), count_big(*) * 100.0 / (total_buffer)
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1

– Or use this dbcc command:

dbcc memorystatus
go

– looking(searching) for this section of the output

Buffer Distribution            Buffers
—————————— ———–
Stolen                         669400
Free                           2092486
Cached                         870357
Database (clean)               311099
Database (dirty)               16898
I/O                            0
Latched                        0
(7 row(s) affected)

Query 5: Check how many dirty pages each database has in the buffer pool:

DECLARE @total_buffer INT;
SELECT @total_buffer = bpool_committed from sys.dm_os_sys_info;

;WITH db_buffer_pages AS
(
SELECT
database_id, db_buffer_dirty_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
–WHERE database_id >4
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN ‘Resource DB’
ELSE DB_NAME([database_id])
END,
db_buffer_dirty_pages,
db_buffer_dirty_KB = db_buffer_dirty_pages*8,
db_buffer_dirty_percent = CONVERT(DECIMAL(6,3),
db_buffer_dirty_pages * 100.0 / @total_buffer)
FROM db_buffer_pages
ORDER BY db_buffer_dirty_KB DESC;

Query 6: Check the buffer pool distribution by objects in a specific database:

USE YOUR_DB_NAME
GO

;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ”),
[Index_Type] = i.type_desc,
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 o
ON p.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0
)
SELECT top 100
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type
ORDER BY buffer_pages DESC;

from http://glindba.com/category/sql-server/memory/