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:

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:

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:

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.

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