SQL Server Buffer Pool Part 1: Deep Look inside buffer pool

Hi Friends,

Today, i am going to explain various things about the sql server buffer pool which is also known as buffer cache. Buffer Pool consist of various type of cache like data cache, plan cache, log cache etc. Here data cache is the very important part of buffer pool which is used to store the various types of pages to serve particular query.Suppose if we run a particular select query on a table to show all data rows of that table. Then all the data pages of that table will be required to fulfill the requirement of this query. Here first all data pages will move from disk to buffer pool. This operation of reading data pages from disk to memory is known as physical IO. But if we running the same query again then there is no need to read data pages from disk to buffer pool because all the data pages are already in buffer pool. This operation is known as Logical IO. How can we identify how much amount of memory is currently occupied by Buffer Pool and how much maximum amount of memory can be occupied by Buffer Pool. There is a DMV provided by SQL Server, with the help of which we can identify all these things easily. Let’s run the below query:

-- On SQL Server 2005, 2008 and 2008 R2
select bpool_committed,bpool_commit_target from sys.dm_os_sys_info

Here bpool_commited and bpool_commit_target both represents number of 8K Buffers. In SQL Server 2012 the name of these column has been changed. So if you are running SQL Server 2012 then you will run the below query:

-- On SQL Server 2012
select committed_kb,committed_target_kb from sys.dm_os_sys_info

Here both the columns represent memory in terms of KB. Which is more meaningfull as compare to earlier versions.

Now we will look what is currently stored in Data Cache by using below query:

select * from sys.dm_os_buffer_descriptors

This will show you the complete view of your Data Cache page storage. Here you can also view the space consumed by databases.

select DB_NAME(database_id) Database_Name, count(*) Pages, count(*) 
from sys.dm_os_buffer_descriptors 
group by database_id  
order by database_id

In the above output NULL represents to resource database with id 32767.So with the help of above output you can easily identify how much space is currently consumed by which database.

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook