Post reply

SQL Guru Help: Does “Clustered Index Scan” scan the intermediate pages? Why?

  • February 25, 2010 at 3:44 am

    #156714

    I have following code:

    use tempdb

    go

    CREATE TABLE tmp (id int ,c1 char(500),c2 char(500))

    CREATE CLUSTERED INDEX CI_id ON tmp(id)

    DECLARE @i INT

    SET @i=0

    WHILE @i<20129

    BEGIN

    INSERT INTO tmp(id,c1,c2)

    SELECT @i,'a','z'

    SET @i=@i+1

    END

    set statistics io on

    select COUNT(*) from tmp

    It does a clustered index scan, from stats IO we know it read 2890 pages. Use DBCC IND(tempdb,tmp,-1) we can see the table has 2891 pages, including 13 indexes page. This means when clustered index scan it reads all indexes pages (intermediate level and leaf level.)

    But as we know, clustered index pages all have page header containing the information about the “previous page” and “next page”. Thus why SQL need to read all intermediate index pages, instead of finding the beginning page and read the following using page header?

    Can not find the answer for this, could any SQL guru explains? Thank you!

  • February 25, 2010 at 7:19 am

    #1124960

    Anybody gives some clue?

  • February 25, 2010 at 7:40 am

    #1124986

    Gee, impatient are we....? This is a forum, not a chatroom. Asking a rather complex question and then bumping it after less than 4 hours is really not called for. If no one answered in 2 or 3 days, then maybe yes.

    The simple answer to your question. SQL reads the intermediate pages in order to drive the read-ahead read, whereby the storage engine fetches pages before they are necessary.

    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

  • February 25, 2010 at 8:17 am

    #1125022

    Hi, GilaMonster, apology for the impatience.

    But if all the pages are in cache, then reading intermediate pages will be more expensive than only reading from page headers. Is this correct? Thanks!

  • February 25, 2010 at 9:11 am

    #1125050

    Not by much, I really doubt it's a measurable amount. There aren't that many intermediate pages in an index.

    I would guess that reading the intermediates also lets SQL check that all the pages it needs really are in cache and that a couple haven't been kicked out (and if some have, then they can be requested before they are needed)

    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 5 posts - 1 through 4 (of 4 total)

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