Logical reads

SQL Server Administration
1

Hi everyone,

In the following code:

create table t(i int,j char(3000))
create table t1(i int,j char(3000))
create unique clustered index ixt on t(i) with (FILLFACTOR=20)
declare @n int = 0
while @n < 1000
begin
insert into t values(@n2,'a')
insert into t1 values(@n2,'a')
set @n = @n+1
end
create unique clustered index ixt1 on t1(i) with (FILLFACTOR=20)

1: select * from t where i between 100 and 150 (returns 16 logical reads)
2: select * from t1 where i between 100 and 150 (returns 30 logical reads)

Can anyone please tell me why statement 2 returns more logical reads than statement 1 ?

Thank you very much!

2

The key here is the fill factor
For the first table, the fill factor is known at the beginning ; and when you insert new ever increasing records, it does not use fill factor if it’s allocating a fresh new page at the end of the index. You will have 2records/1page. (500 data pages)
see more here:
https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/

For the second table, the fill factor is applied after the records are inserted so it is only one record kept in a page.So here we will have 1000 pages. 1record/1page

DBCC IND(9,'dbo.t',-1)
DBCC IND(9,'dbo.t1',-1)

For the t table , you have an index with a leaf level + one level (lvl 1)
For the t1 table , you have an index with a leaf level + 2 more level (lvl 1, 2) - and is 1 record per page - this is why you see extra logical reads
(pageType = 2 == intermediary levels)

select * 
, %%lockres%% AS LockResource
,sys.fn_PhysLocFormatter(%%physloc%%) as location
from t
where i between 100 and 150

select * 
, %%lockres%% AS LockResource
,sys.fn_PhysLocFormatter(%%physloc%%) as location
from t1
where i between 100 and 150

Here you will have, in t, two records per location(page) ( 26 records , divided by 2 records per page = 13 pages to read at minimum. To this , add the 1 read of the root level page and 2 extra pages. So 16 logical reads)
and in t1 , one record per location (26records returned , divided by 1 record per page = 26 pages to read. To this also add 1 read of root level page, 1 read of intermediary level page and 2 extra pages. So you have 30 logical reads)

3

Hello stepson, Thanks a lot for your reply and help !!!

4

My pleasure!