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:

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

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

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

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 Twitter |  Follow me on FaceBook