Who's taking my buffer Pool?

The original: Who took my buffer Pool

Transferred from: http://blogs.msdn.com/b/apgcdsd/archive/2011/01/11/buffer-pool.aspx

I was doing SQL Server 7.0 technical support when a customer asked me, "My SQL Server buffer pool is large, is there a way to know which objects eat my buffer pool memory?" For example, can you tell which database it is, which table, and which index occupies buffer pool? "I didn't find the answer to that question at the time, but I kept that in mind. This problem was solved until the SQL Server 2005 version appeared. The answer is to use the Dynamic view (DMV) sys.dm_os_buffer_descriptors.

This DMV is very powerful. Based on SQL Server Books Online, this view is "returning information about all the current data pages in the SQL Server buffer pool." You can use the output of this view to determine the distribution of database pages within a buffer pool based on the database, object, or type. Specifically, this view can return the following properties of a 8K data page in buffer pool:

(1) Which database the page belongs to

(2) which file the page belongs to in the database

(3) page_id of this page

(4) The type of the page. This can be used to determine whether the page is indexed or a data page.

(5) How many rows of data are in the page

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

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

With the above information, we can easily count a few useful data, as follows.

1. The memory of Buffer pool is mainly occupied by that database?

SELECT Count (*) *8 as cached_pages_kb, case database_id

when 32767 and then ' Resourcedb '

ELSE db_name (database_id)

END  as database_name

from SYS . dm_os_buffer_descriptors

GROUP  by db_name (database_id) ,database_id

ORDER by cached_pages_kb DESC;

The results are as follows:

From the above results, you can see that the database AdventureWorks occupies about 30MB of buffer pool space.

Note that the DMV does not return information about non-data pages (such as cache execution plans) in buffer pool. This means that the DMV does not return information for all pages in the buffer pool.

2. More specifically, which table or index in the current database occupies the most 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 results are as follows (partial):

From the above results you can see that the table individual in the pool memory buffer the most, perhaps this is a frequent access to the hot table, or is a relatively large table. Note that the buffer pages inside the pool are constantly changing. If you run the statement again, it might be another table that appears on the headline.

3. Size of the total number of pages modified in the buffer pool. This is relatively easy:

SELECT Count (*) *8 as cached_pages_kb,

Convert(varchar(5),Convert(decimal(5,2),(100-1.0*(SelectCount(*) fromSYS.dm_os_buffer_descriptorsBwhereB.database_id=A.database_id andIs_modified=0)/Count(*)*100.0)))+'% 'Modified_percentage

, Case database_id

when 32767 and then ' Resourcedb '

ELSE db_name (database_id)

END  as database_name

from SYS . Dm_os_buffer_descriptors A

GROUP  by db_name (database_id) ,database_id

ORDER by cached_pages_kb DESC;

Results:

As you can see from the above results, about 13.84% of the data in the AdventureWorks database has been modified. If the majority (more than 80%) of a database is modified, this database writes very much. Conversely, if the ratio is close to 0, the activity of the database is almost read-only. The proportion of reads and writes is important for disk scheduling. Of course, there are other performance data to get the approximate percentage of the database read and write, which is limited to a few pages.

Who's taking my buffer Pool?