Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

about logical reads question Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 7:59 PM
Points: 1, Visits: 27
I create ths script as following:

CREATE TABLE [dbo].[t](
[id] [char](6) NOT NULL,
[b] [varchar](8000) NOT NULL,
CONSTRAINT [pk_t] PRIMARY KEY CLUSTERED
(
[id] ASC
)

insert into t values('00001',REPLICATE('A',4000) )
insert into t values('00002',REPLICATE('B',4000) )

insert into t values('000021',REPLICATE('E',4000) )

insert into t values('00003',REPLICATE('C',4000) )

insert into t values('00004',REPLICATE('D',4000) )

OK,Let's dump index internal:

dbcc ind(0,'t',1)

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID
1 1989 NULL NULL 1609772792 1 1 72057594053459968 In-row data 10 NULL 0 0
1 1988 1 1989 1609772792 1 1 72057594053459968 In-row data 1 0 1 1994
1 1992 1 1989 1609772792 1 1 72057594053459968 In-row data 2 1 0 0
1 1993 1 1989 1609772792 1 1 72057594053459968 In-row data 1 0 0 0
1 1994 1 1989 1609772792 1 1 72057594053459968 In-row data 1 0 1 1993



Page id=1992 is a root page ,

DBCC traceon(3604)
dbcc page(0,1,1992,3)

output like this:

FileId PageId Row Level ChildFileId ChildPageId id (key) KeyHashValue
1 1992 0 1 1 1988 NULL NULL
1 1992 1 1 1 1994 00002 NULL
1 1992 2 1 1 1993 00004 NULL

my question is when I isse the following sql:

select * from t where id<='00001'

sql server report it need four logical reads.

I think it only need read index root page(pageid=1192) and the left leaf index page(pageid =1988) ,so it need only two logical reads.



who can explain it why it need four logical reads.






Post #654593
Posted Wednesday, February 11, 2009 4:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
What showed 4 logical reads?

set statistics io on
go
select * from t where id<='00001'

Results:
Table 't'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #654620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse