none
Data Paging Issue with SQL Server 2008

    Вопрос

  • Hi,

    We are upgrading the Sql Server 2005 to 2008 and during the regression testing we found there is a mismatch with order of the record selected.

    We have a SP say USP_getData, which creates local temp table to hold the records selected from multiple queries and selects the rows from that table at the end. There are no indexes on the temp table. hence the order of the data selected should be the order of the data.

    The temp table has around 200 records. table has a identity column to record the order the rows are inserted in to it.

    when I check the page id details for the row, from row 8, it goes to different page and after 90 records it uses the old page.

    Below given is the Physical RID ( FileId:PageId:Slot) and the identify column.

    (Physical RId)   Identity

    (1:219:0) 1
    (1:219:1) 2
    (1:219:2) 3
    (1:219:3) 4
    (1:219:4) 5
    (1:219:5) 6
    (1:219:6) 7
    (1:219:7) 92
    (1:219:8) 93
    (1:219:9) 94
    (1:219:10) 95
    (1:219:11) 96
    (1:219:12) 97
    (1:219:13) 98
    (1:219:14) 99

    (1:176504:0) 8
    (1:176504:1) 9
    (1:176504:2) 10
    (1:176504:3) 11
    (1:176504:4) 12
    (1:176504:5) 13
    (1:176504:6) 14
    (1:176504:7) 15
    (1:176504:8) 16
    (1:176504:9) 17
    (1:176504:10) 18

    Another thing observed is, when perm tables are used instead of local temp table, the PageId and Identity are in order.

    For local temp tables, selection of page id depends on space availability in the temp db. but I executed the same SP in different sessions and many times and it is still the case.

    Would appreciate if you could advise on this .

    thanks

    • Перемещено Tom PhillipsModerator 8 июня 2012 г. 17:34 TSQL question (From:SQL Server Database Engine)
    8 июня 2012 г. 16:57

Ответы

Все ответы