about logical reads question

• lovexueer

  SSC Rookie

  Points: 47

  More actions

  February 11, 2009 at 3:12 am

  #206375

  I create ths script as following:

  CREATE TABLE [dbo].[t](

  [id] [char](6) NOT NULL,

  [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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 11, 2009 at 4:00 am

  #941973

  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, MVP, M.Sc (Comp Sci)
  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