Post reply

Disk to memory and back again

  • October 4, 2012 at 7:28 am

    #265393

    Hi All

    I understand that when a query is submitted to SQL Server, the pages that it needs to fulfill the query are first fetched from disk and then brought into memory.

    Does the very same thing happen with non-leaf index pages? When SQL traverses an index, does it also have to bring the root and intermediate pages into memory?

    Thanks

  • October 4, 2012 at 7:49 am

    #1545540

    When SQL engien traversing through Clustered index , the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page).

  • October 4, 2012 at 8:52 am

    #1545602

    db_expert_pradeep (10/4/2012)

    When SQL engien traversing through Clustered index , the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page).

    I'm trying to figure out whether the root and intermediate pages are brought into memory

    Thanks

  • October 4, 2012 at 10:29 am

    #1545670

    Absolutely. The query processor knows nothing whatsoever about a disk. Anything it needs to read for any reason must be brought into memory first if it's not already in memory (and chances are reasonably good that root and non-leaf levels of frequently used tables will always be in memory)

    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

  • October 5, 2012 at 12:17 am

    #1545904

    GilaMonster (10/4/2012)

    Absolutely. The query processor knows nothing whatsoever about a disk. Anything it needs to read for any reason must be brought into memory first if it's not already in memory (and chances are reasonably good that root and non-leaf levels of frequently used tables will always be in memory)

    Thanks

    When an Index/Data page is moved into memory and then modified in memory, SQL server then replaces the page on disk with the modified page in memory, right? Does this replacement have to happen before any other subsequent queries need that same page?

    Thanks

  • October 5, 2012 at 1:25 am

    #1545913

    Pages are not moved into memory, they are copied.

    If a page is modified in memory it'll be written back to disk sometime, there's no importance as to when and certainly doesn't before anything else uses the page. Remember, all queries run from pages in memory, always.

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

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