Logical Reads

Question

Logical Reads

sudhakara

SSCrazy Eights

Points: 9425
More actions
April 18, 2008 at 12:26 am

#184630

when i execute my main sp , inner sp showing read count as 6000 ,but when execute that perticular sp its not showing that much reads .
Explain why its showing like .......

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Often, SQL Server will reduce the amount of work an inner query does if the outer query can limit the rows for the inner query. I'd have to at least see the query (and probably the table structure) to say that's what's going on for sure, but that is a very common thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

marques.miguel SSC Enthusiast Points: 186 More actions · Answer 2

Hi everyone,

I'm new to SQL Server and I was wondering if anyone can help me with this question.

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)

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!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

marques.miguel (7/16/2016)
Hi everyone,
I'm new to SQL Server and I was wondering if anyone can help me with this question.
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)
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!

It's pretty easy. You told SQL Server to only allow pages to be filled by 20% as a part of the index rebuild on the two indexes.

For the index on the "t" table...

It was specified before the table had any data in it so each page could contain two 3011 byte rows. Fill Factor has NO "maintenance affect". That is, SQL Server will build the table (clustered index) according to the Fill Factor at the time the index was built. That has nothing to do with how full the table can get. In this case, there are 500 pages at the leaf level and each row has two pages. And, there's only 1 root page that the select has to look through.

For the index on the "t1" table...

That index was built after the data was added. Since each row is 3011 bytes in length, each row occupies about 37% of a page, which is larger than the 20% Fill Factor. Each page must contain at least 1 row and cannot contain 2 or more in this case because each row is larger than the Fill factor. Instead of the table being spread across just 500 pages, it is now spread across 1000 pages... doubling the number of reads to read the same data.

Run this and see...

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('t') ,NULL,NULL,'Detailed');

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('t1'),NULL,NULL,'Detailed');

Remember... Fill Factor is only observed during the creation of the index or a REBUILD/REORGANIZE. It is not maintained during inserts, updates, or deletes.

From Books Online:

[font="Arial Black"]Important: [/font]
The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

marques.miguel SSC Enthusiast Points: 186 More actions · Answer 4

Hello Jeff Moden,

Thanks a lot for your reply and help !!!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

marques.miguel (7/17/2016)
Hello Jeff Moden,
Thanks a lot for your reply and help !!!

No problem and thank you for the well laid out question with test code. It makes things easy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)