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

1_SQL_Server_Buffer_Pool_Part1_Deep_Look_inside_buffer_pool

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

2_SQL_Server_Buffer_Pool_Part1_Deep_Look_inside_buffer_pool

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

3_SQL_Server_Buffer_Pool_Part1_Deep_Look_inside_buffer_pool

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

4_SQL_Server_Buffer_Pool_Part1_Deep_Look_inside_buffer_pool

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 TwitterFollow me on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *