MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

Data page structure in MS SQL 6.5

Alexander Chigrik
chigrik@mssqlcity.com

In this article, I want to tell you about data page structure in MS SQL Server 6.5, which are substantially different that what you currently find in SQL Server 7.0 and SQL Server 2000.

There are five kinds of pages in MS SQL 6.5:

  • Data pages
  • Index pages
  • Allocation pages
  • Text/Image pages
  • Distribution pages


    • The size of the data page in MS SQL 6.5 is 2Kb (2048 bytes). Every data page consists of three parts:

  • 32 bytes header
  • data rows
  • offset table


    • See Figure 1:
    General data page structure
    Figure 1. General data page structure


    Here are the parts of the page header:

    pagenois a logical page number
    nextpgnext logical page number
    prevpgprevious logical page number
    objidid of the table
    timestampinternal identifier
    nextrnonext row number, that will be written on this page
    levelthe index level
    indidid of the index
    freeoffpointer to the free space at the end of the page
    minlenminimum rows length for this data page

    The offset table contains two bytes for each row on the data page. MS SQL Server 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 insert new record, MS SQL Server 6.5 scans the offset table from the end. If there is cell with a zero value in it, then the offset of the new row will be inserted into this cell. If there is no cell with a zero value in it, then a new cell will be added in the offset table, a 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.

    When you delete a record, MS SQL Server 6.5 writes in the appropriate cell a zero value, deletes the 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 contiguously on the data page.

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

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

    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.

    In the following examples, I am using the trace flag 3604 to better illustrate what is happening in the examples. 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.

    Examining the Data Page Structure

    In this example, one data page is output from the table "discounts", found in the "pubs" database, using this code:

    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:
    Discounts data page structure
    Figure 2. Discounts data page structure


    An Example of a Data Page After Deleting a Record

    When you delete record, MS SQL Server 6.5 writes to the appropriate cell a zero value, deletes the row, moves the other rows to avoid free space between them, and sets the new value into freeoff field of the page header. All rows are stored contiguously 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:
    After delete
    Figure 3. After delete


    An Example of an Insert on a Data Page

    When you insert new record, MS SQL Server 6.5 scans the offset table from the end. If there is cell with zero value in it, then the offset of the new row will be inserted into this cell. If there is no cell with a zero value in it, then new cell will be added in the offset table, the 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=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 4:
    After insert
    Figure 4. After insert


    Learning how data is structured in a database helps to provide a solid understanding of how SQL Server works. And the more you know about the underlying architecture, the better position you will be in to performance tune your SQL Server databases.
     

    		 
    Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

    (c) 1997, 2010 Bits on the Wire, Inc