SQL Server Buffer Pool Part 2: Impact of Clustered Index on Buffer Pool

Hi Friends,

Here, I am going to introduce you with a very interesting thing about clustered indexes. We all knew that when we execute a particular select query then the required data pages first comes in to the buffer pool. Actually SQL Server reads complete extent rather than individual data pages. But the interesting thing is SQL Server also reads some other adjacent extents which are not required to that particular select query in case of clustered index. This Blog is the continuation of my first blog on SQL Server Buffer Pool part 1;

Let’s Start with the below code

USE master
GO
create database PRINCE
GO
USE PRINCE
GO
create table xtbuffertest
(
    id int identity(1,1),
    name char(8000)
)
go
create clustered index IX_CLUS on xtbuffertest(id)
go
insert into xtbuffertest values('SQLServerGeeks')
go 30
alter index IX_CLUS on xtbuffertest REBUILD
go
dbcc extentinfo('PRINCE','xtbuffertest')
go

The above code will create a new PRINCE database and also create one table xtbuffertest. Here one record will store on one data page due to the record length and data page size. We create a clustered index on id column. After that we insert 30 data rows inside that table. Then rebuild the index to remove the mixed extent allocation. If you don’t know about why I rebuild the index here, then you can go with this blog;

Here the output of above query is as shown below. This provides the extent allocation information to the table xtbuffertest:

1_SQL_Server_Buffer_Pool_Part2_Deep_Look_inside_buffer_pool

Here 278 is the index page while extent [304-311], [312-319], [320-327], [328-333] contain allocated data pages. To know about the row storage you can run below code:

dbcc traceon(3604)
go
dbcc page('PRINCE',1,278,3)

Now from the output of above query it is clear that extent [page id 304-311 contain id 1-8], [page id 312-319 contain id 9-16], [page id 320-327 contain id 17-24] and [page id 328-333 contain id 25-30].

Now here we will see the three different scenario of select statement:

First: Select a record which is the First Page in any middle extent. Like id=9

USE PRINCE
go
checkpoint
dbcc dropcleanbuffers
go 
select id,name from xtbuffertest where id=9
go
select * from sys.dm_os_buffer_descriptors where database_id=DB_ID('PRINCE') order by page_id

2_SQL_Server_Buffer_Pool_Part2_Deep_Look_inside_buffer_pool

   

Here why SQL Read First extent, while we only select the data page 312 which is having id=9 and exist in the second extent [312-319].

Second: Select a record which is the middle Page in any middle extent. Like id=18

USE PRINCE
go
checkpoint
dbcc dropcleanbuffers
go 
select id,name from xtbuffertest where id=18
go
select * from sys.dm_os_buffer_descriptors where database_id=DB_ID('PRINCE') order by page_id

3_SQL_Server_Buffer_Pool_Part2_Deep_Look_inside_buffer_pool

Third: Select a record which is the Last Page in any middle extent. Like id=16

USE PRINCE
go
checkpoint
dbcc dropcleanbuffers
go 
select id,name from xtbuffertest where id=16
go
select * from sys.dm_os_buffer_descriptors where database_id=DB_ID('PRINCE') order by page_id

4_SQL_Server_Buffer_Pool_Part2_Deep_Look_inside_buffer_pool

Here in this scenario when we select any record page then it will also select the previous page as well as next page. For example in first case when we select id=9 which is the first record page in second extent then SQL also select the Previous page where id=8 but that is in the first extent, so SQL read the complete first extent. Next page where id=10 is the second record page in second extent which is already read.

For Second case when we select id=18 which is the second record page in third extent, here previous page id=17 and next page id=19 both are in same extent. So SQL read only third extent here.

For third case when we select id=16 which is the last record page in second extent. Here previous page id=15 is also in the second extent while next page id=17 is in the third extent, so read the complete third extent also.

 

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

   

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 →

5 Comments on “SQL Server Buffer Pool Part 2: Impact of Clustered Index on Buffer Pool”

  1. Good read

    1. I remember when your total server memory is not up to the target server memory page reads will be converted to extent reads to speed up future operation (SQL Knows your bpool is empty so converts page read in to extent read to make future reads faster). But once your Bpool is full (total = target) it reads only a single page.

    2. When you run the select query enable set statistics IO on and check if there is read ahead reads or prefetch in the plan which might cause this.

    Thanks

    Karthick P.K

  2. Hi Karthick,

    There is no read ahead count when you run select statement after enabling the set statistics io on.

    Thanks & Regards:

    Prince Kumar Rastogi

  3. Hi Prince,

    Superb post for understanding bpool functionality.I’ve got one doubt though.
    When I tried the code snippet provided,for any [id] in where clause,I get an index page and only 3 data pages cached in the bpool. i.e. the page where the [id] actually resides and the prev & next page.

    Though I query for an [id] that sits in the last page of an extent,am supposed to see all the pages from 2 extents; but what I see is only current-prev-next page not the entire extent.

    Am not able to see the scenario where the entire extent is read in the bpool as shown by you.
    Could you clarify on this?
    Note: Am using SQL Server 2014 Express Edition.

  4. Hi Vignesh,

    Actually this depends on the buffer pool allocated size (in our case it’s automatically allocated by system) and also depends on how much available space in buffer pool (i means total versus target, as mention by Karthick P.K in the first comment above).

    i have tested this situation,
    1- After SQL instance restart, when automatically allocated buffer size was very less and total < target
    Then it was reading one previous page + current requested page + One Next page

    2- After sometime when automatically allocated buffer size was increased automatically and total < target
    Then it was reading extents as mention in the above blog.

    3- For Total=Target again it was reading one previous page + current requested page + One Next page

    you can use below query to check total vs target on SQL 2012 or above

    Select committed_kb,committed_target_kb from sys.dm_os_sys_info

Leave a Reply

Your email address will not be published.