Data Page Structure in MS SQL Server 6.5



General concepts

Examples

  • Discounts data page structure

  • After delete

  • After insert

  • Literature

    General concepts

    There are five kinds of pages in MS SQL 6.5:

  • Data pages

  • Index pages

  • Allocation pages

  • Text/Image pages

  • Distribution pages
  • In this article I want to tell you about data page structure in
    MS SQL 6.5. The size of the data page in MS SQL 6.5 is 2Kb, i.e.
    2048 bytes. Every data page consists of three part:

  • 32 bytes header

  • data rows

  • offset table
  • See Figure 1:





    Figure 1. General data page structure

    This is the description of the page header:

    pageno    - is a logical page number
    nextpg    - next logical page number
    prevpg    - previous logical page number
    objid     - id of the table
    timestamp - internal identifier
    nextrno   - next row number, that will be written on this page
    level     - the index level
    indid     - id of the index
    freeoff   - pointer to the free space at the end of the page
    minlen    - minimum rows length for this data page

    Offset table contains two bytes for each row on the data page.
    MS SQL 6.5 uses the offset table to find the address of row.
    Every cell of the offset table contains the address of the row
    on the data page.

    When you delete record, MS SQL 6.5 writes in the appropriate
    cell the zero value, deletes row, moves other rows to avoid
    free space between them and sets the new value into freeoff
    field of the page header. All rows are stored continuously
    on the data page.

    When you insert new record, MS SQL 6.5 scans the offset table
    from the end. If there is cell with zero value in it, then
    offset of the new row will be inserted into this cell. If there
    is no cell with zero values in it, then new cell will be added
    in the offset table, new value will be written into freeoff field
    of the page header and the nextrno field of the page header will
    be increased by 1.

    You can use the following command to view the data page structure
    (this command is not very good documented in SQL Server Books Online):


    DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

    PARAMETERS:

       Dbid or dbname - Enter either the dbid or the name of the database
    in question.

    Pagenum - Enter the page number of the SQL Server page that is to
    be examined.

    Print option - (Optional) Print option can be either 0, 1, or 2.

    0 - (Default) This option causes DBCC PAGE to print
    out only the page header information.
    1 - This option causes DBCC PAGE to print out the
    page header information, each row of information
    from the page, and the page's offset table. Each
    of the rows printed out will be separated from
    each other.
    2 - This option is the same as option 1, except it
    prints the page rows as a single block of
    information rather than separating the
    individual rows. The offset and header will also
    be displayed.

    Cache - (Optional) This parameter allows either a 1 or a 0 to be
    entered.
    0 - This option causes DBCC PAGE to retrieve the page
    number from disk rather than checking to see if it is
    in cache.
    1 - (Default) This option takes the page from cache if it
    is in cache rather than getting it from disk only.

    Logical - (Optional) This parameter is for use if the page number
    that is to be retrieved is a virtual page rather then a
    logical page. It can be either 0 or 1.

    0 - If the page is to be a virtual page number.
    1 - (Default) If the page is the logical page number.


    Examples

    In this examples I also use trace flag 3604.
    Trace flag 3604 sends trace output to the client. This trace
    flag is used only when setting trace flags with DBCC TRACEON
    and DBCC TRACEOFF.


    Discounts data page structure

    In this example one data page is output from the table discounts,
    database pubs.


    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DECLARE @pgid int
    SELECT @pgid = first FROM sysindexes WHERE id = object_id('discounts')
    DBCC PAGE (pubs, @pgid, 1)
    GO

    This is the result from my computer:


    PAGE:
    Page found in cache.

    BUFFER:
    Buffer header for buffer 0xf96580
    page=0x1181000 bdnew=0xf96580 bdold=0xf96580 bhash=0x0
    bnew=0xf964c0
    bold=0xf96640 bvirtpg=2540 bdbid=5 bpinproc=0 bkeep=0 bspid=0
    bstat=0x1004 bpageno=488

    PAGE HEADER:
    Page header for page 0x1181000
    pageno=488 nextpg=0 prevpg=0 objid=544004969 timestamp=0001 0000486a
    nextrno=3 level=0 indid=0 freeoff=122 minlen=5
    page status bits: 0x100,0x1

    DATA:
    Offset 32 -
    01181020: 0100017e 041a0049 6e697469 616c2043 ...~...Initial C
    01181030: 7573746f 6d657202 1707 ustomer...

    Offset 58 -
    0118103a: 0401019e 02200056 6f6c756d 65204469 ..... .Volume Di
    0118104a: 73636f75 6e746400 e803051a 18161607 scountd.........

    Offset 90 -
    0118105a: 020201f4 01200043 7573746f 6d657220 ..... .Customer
    0118106a: 44697363 6f756e74 38303432 031c1807 Discount8042....

    OFFSET TABLE:
    Row - Offset
    2 (0x2) - 90 (0x5a), 1 (0x1) - 58 (0x3a),
    0 (0x0) - 32 (0x20),


    See Figure 2:




    Figure 2. Discounts data page structure

    After delete

    When you delete record, MS SQL 6.5 writes in the appropriate
    cell the zero value, deletes row, moves other rows to avoid
    free space between them and sets the new value into freeoff
    field of the page header. All rows are stored continuously
    on the data page.


    DELETE FROM discounts WHERE discounttype = 'Volume Discount'

    This is the result from my computer:


    PAGE:
    Page found in cache.

    BUFFER:
    Buffer header for buffer 0xf96580
    page=0x1181000 bdnew=0xf5a448 bdold=0xf5a448 bhash=0x0
    bnew=0xf964c0
    bold=0xf96640 bvirtpg=2540 bdbid=5 bpinproc=7 bkeep=0 bspid=0
    bstat=0x1004 bpageno=488

    PAGE HEADER:
    Page header for page 0x1181000
    pageno=488 nextpg=0 prevpg=0 objid=544004969 timestamp=0001 0000519c
    nextrno=3 level=0 indid=0 freeoff=90 minlen=5
    page status bits: 0x100,0x10,0x1

    DATA:
    Offset 32 -
    01181020: 0100017e 041a0049 6e697469 616c2043 ...~...Initial C
    01181030: 7573746f 6d657202 1707 ustomer...

    Offset 58 -
    0118103a: 020201f4 01200043 7573746f 6d657220 ..... .Customer
    0118104a: 44697363 6f756e74 38303432 031c1807 Discount8042....

    OFFSET TABLE:
    Row - Offset
    2 (0x2) - 58 (0x3a), 1 (0x1) - 0 (0x0),
    0 (0x0) - 32 (0x20),


    See Figure 3:




    Figure 3. After delete


    After insert

    When you insert new record, MS SQL 6.5 scans the offset table
    from the end. If there is cell with zero value in it, then
    offset of the new row will be inserted into this cell. If there
    is no cell with zero values in it, then new cell will be added
    in the offset table, new value will be written into freeoff field
    of the page header and the nextrno field of the page header will
    be increased by 1.


    insert discounts values('Volume Discount', NULL, 100, 1000, 6.7)

    This is the result from my computer:


    PAGE:
    Page found in cache.

    BUFFER:
    Buffer header for buffer 0xf96580
    page=0x1181000 bdnew=0xf5a448 bdold=0xf5a448 bhash=0x0
    bnew=0xf964c0
    bold=0xf96640 bvirtpg=2540 bdbid=5 bpinproc=8 bkeep=0 bspid=0
    bstat=0x1004 bpageno=488

    PAGE HEADER:
    Page header for page 0x1181000
    pageno=488 nextpg=0 prevpg=0 objid=544004969 timestamp=0001 0000519e
    nextrno=3 level=0 indid=0 freeoff=122 minlen=5
    page status bits: 0x100,0x10,0x1

    DATA:
    Offset 32 -
    01181020: 0100017e 041a0049 6e697469 616c2043 ...~...Initial C
    01181030: 7573746f 6d657202 1707 ustomer...

    Offset 58 -
    0118103a: 020201f4 01200043 7573746f 6d657220 ..... .Customer
    0118104a: 44697363 6f756e74 38303432 031c1807 Discount8042....

    Offset 90 -
    0118105a: 0401019e 02200056 6f6c756d 65204469 ..... .Volume Di
    0118106a: 73636f75 6e746400 e803051a 18161607 scountd.........

    OFFSET TABLE:
    Row - Offset
    2 (0x2) - 58 (0x3a), 1 (0x1) - 90 (0x5a),
    0 (0x0) - 32 (0x20),


    See Figure 4:




    Figure 4. After insert


    Literature

    1. SQL Server Books Online.

    2. INFO: Description of DBCC PAGE Command

    http://support.microsoft.com/support/kb/articles/Q83/0/65.ASP


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Get the Free Newsletter!

    Subscribe to Cloud Insider for top news, trends & analysis

    Latest Articles