about logical reads question

  • lovexueer

    SSC Rookie

    Points: 47

    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: 1004424

    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

Viewing 2 posts - 1 through 2 (of 2 total)

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