SQL Server Performance

about logical reads question

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by xyfeng78, Feb 11, 2009.

  1. xyfeng78 New Member

    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.







  2. Kewin New Member

    I can't, and it doesn't.. Not for me anyway.
    Trying the example, the result is 2 logical reads.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.
    /Kenneth
  3. Adriaan New Member

    I'm no expert on SQL internals, but I notice that you're using the <= operator against a CHAR(6)column, which as you should know contains a string of characters.
    So this is not a single numeric value, but a string of ASCII values. And it's the ASCII values in the string that get evaluated left-to-right when you use an operator like that.
    You should be aware that the character '0' has an ASCII value of 48, meaning that there are 49 characters that would pass the <= operator check (values 0 through 48) - and this only on the first position of the CHAR(6) column. For instance, ASCII value 47 is the forward slash (/). Even '0/9999' <= '000001' evaluates to true
    If you would be evaluating against an integer column, perhaps it would get you the results you're expecting.
  4. satya Moderator

    I would say the entire decision of logical or physical reads depends upon the database files and hardware. Going back to the basics of SQL optimization: Every query has its execution plan before it gets executed. Execution plan is generated by optimizer, one of components in SQL Server database engine; it contains how to access data to satisfy the query request. You probably meet few queries having slow response time by looking into physical read and logical read.
    Then going into details SQL optimizer is at best to decide depending upon the indexes on the relevant tables in your query. Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
    Hope this helps.

Share This Page