Lesson 8	Monitoring with dbcc, continued
Objective	Use dbcc buffer to monitor the Data Cache

Use dbcc buffer to monitor Data Cache

dbcc buffer

The dbcc buffer command is used to look at the SQL Servers data cache. You can see all of the cache, including the cache for a specific database, for a specific object, or a given number of pages. You also have the capability of controlling the number of pages returned.

This dbcc command is not documented by Microsoft and should use your own judgement when using the command.
The syntax of the dbcc buffer command is as follows:

dbcc buffer [(dbname|db_id [,object_name|objectid [,number of pages [ ,display option]]] )]

Monitoring with dbcc and dbcc Buffer

1) For all of the dbcc buffer options, you will receive header information describing the page being displayed. If you have more than one page, you will see the BUFFER block and PAGE HEADER information for each page.

2) With the display option 1 you will see each data record for the table on the pages returned. SQL Server also displays a row offset table, showing where each row is located in memory if you specify an object.

3) With the display option 2, SQL Server returns a memory dump of the pages containing the object. SQL Server also displays a row offset table, showing where each row is located in memory if you specify an object.

4) With the display option 3, SQL Server returns a memory dump of the pages containing the object, separated into records. SQL Server also interprets the records, displaying the information in a more readable format.

When dbcc buffer returns information, it returns the most recently used (MRU)^[1] page first; unless the number of pages is less than 0, then it returns the least recently used (LRU) page first. The display option can be 0 (default), 1, 2, or 3. The following SlideShow illustrates the output of the dbcc buffer command. Least recently used (LRU): The in memory data page that has gone the longest time without being accessed.
In the next lesson, you will learn about three more methods for using dbcc to monitor SQL Server performance.

[1]Most recently used (MRU): The in memory data page that was the last data page to be accessed.