View buffer pool usage

Transferred from Microsoft technical support forum:

"My SQL Server Buffer Pool is very large. Is there a way to know which objects eat my buffer pool memory? For example, can you know which database, which table, and which index occupies the buffer pool ?" To solve this problem, you can use (DMV) SYS. dm_ OS _buffer_descriptors. This DMV is very powerful. According to SQL Server books online, this view is used to return information about all data pages in the SQL Server Buffer Pool. You can use the output of this view to determine the distribution of database pages in the buffer pool based on the database, object, or type ". Specifically, this view can return the buffer
The following attributes of an 8 K data page in the pool:

(1) database to which the page belongs

(2) which database file the page belongs?

(3) page_id of the page

(4) Type of the page. You can determine whether the index page or data page is used for this page.

(5) number of rows of data on the page

(6) How much space is available on the page.

(7) Whether the page has been modified since the disk was read.

With the above information, we can easily collect several useful data, as shown below.

1. What database accounts for the buffer pool memory?

Select
Count (*) * 8 as cached_pages_kb, Case database_id

When 32767
Then 'resourcedb'

Else
Db_name (database_id)

End
As database_name

From SYS. dm_ OS _buffer_descriptors

Group
Db_name (database_id)
, Database_id

Order by cached_pages_kb
DESC;

The result is as follows:

From the above results, we can see that the database adventureworks occupies about 30 mb of buffer pool space.

Note that this DMV does not return information about non-data pages (such as execution plan cache) in the buffer pool. That is to say, this DMV does not return information of all pages in the buffer pool.

2. Specifically, which table or index of the current database occupies the largest pool buffer space?

Select
Count (*) * 8 as cached_pages_kb

, Obj. Name
, Obj. index_id, B. type_desc, B. Name

From SYS. dm_ OS _buffer_descriptors
As BD

Inner
Join

(

Select
Object_name (object_id)
As name

, Index_id
, Allocation_unit_id, object_id

From
SYS. allocation_units
As Au

Inner
Join SYS. partitions
As P

On AU. container_id
= P. hobt_id

And (AU. Type
= 1 or Au. Type
= 3)

Union
All

Select
Object_name (object_id)
As name

, Index_id, allocation_unit_id, object_id

From
SYS. allocation_units
As Au

Inner
Join SYS. partitions
As P

On AU. container_id
= P. partition_id

And au. Type
= 2

) As OBJ

On Bd. allocation_unit_id
= Obj. allocation_unit_id

Left
Join SYS. Indexes B
On B. object_id
= Obj. object_id
And B. index_id
= Obj. index_id

Where database_id
= Db_id ()

Group by obj. Name, obj. index_id
, B. Name, B. type_desc

Order by cached_pages_kb
DESC;

The output result is as follows (partial ):

From the above results, we can see that the table individual has the most buffer in the pool memory. This may be a frequently accessed hot table or a relatively large table. Note that the buffer pages in the pool change frequently. If you run the statement again, another table may appear in the headlines.

3. The total number of modified pages in the buffer pool of the buffer pool. This is easier:

Select
Count (*) * 8 as cached_pages_kb,

Convert (varchar (5), convert (decimal (5, 2), (100-1.0 * (select
Count (*)
From SYS. dm_ OS _buffer_descriptors B
Where B. database_id = A. database_id
And is_modified = 0)/count (*) * 100.0) + '%'
Modified_percentage

, Case database_id

When 32767
Then 'resourcedb'

Else
Db_name (database_id)

End
As database_name

From SYS. dm_ OS _buffer_descriptors

Group
Db_name (database_id)
, Database_id

Order by cached_pages_kb
DESC;

Result:

From the above results, we can see that about 13.84% of the data in the adventureworks database has been modified. If most (more than 80%) of a database is modified, there are many write operations on the database. If the ratio is close to 0, the activity of the database is almost read-only. The read/write ratio is very important to the disk arrangement. Of course, there are other performance data to obtain the approximate proportion of database read/write, which will not be discussed here.