M77 M77 - 4 months ago 8
SQL Question

Difference in number of logical reads for similar set of data

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(@n*2,'a')
insert into t1 values(@n*2,'a')
set @n = @n+1
end

create unique clustered index ixt1 on t1(i) with (FILLFACTOR=20)


Both of the above tables have same structure,data types and even same data,but querying them gives me different logical reads..

select * from t where i between 100 and 150 --returns 16 logical reads
select * from t1 where i between 100 and 150 --returns 30 logical reads


Can anyone please tell me why query in
table t1
returns more logical reads than the query from
table t
?

Answer

This is due to the way you are creating an index (creating first for one table and last for one table ) With Fill factor settings..

Fill Factor determines how much free space is left in leaf level page of index,in this case you are asking to set it 20 which means leave the rest of 80% free..

And one more important point to note is fill factor setting will be honored only when index is rebuilt or created.

In First case,even though you create an index with fill factor 0f 20,there is no data,so free space is not left and we know while inserting this setting is not honored.

So querying table 1 pages gives us..

 select object_name(object_id),index_depth,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count  from 
sys.dm_db_index_physical_stats(db_id(),0,-1,0,'Detailed')
where object_id=object_id('t')

enter image description here

For Table t1,you are creating an index after data is inserted and SQL will honor the setting..page table count goes to

select object_name(object_id),index_depth,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count  from 
sys.dm_db_index_physical_stats(db_id(),0,-1,0,'Detailed')
where object_id=object_id('t1')

enter image description here

This is the reason why you are seeing different logical reads for each table with same data and structure