Anatomy of SQLSERVER Article 2 reverse the data page header)

Anatomy of the second article of SQL Server to reverse the data page header (translated) improve. dkreverse-engineering-SQL-server-page-headers when developing OrcaMDF, the first challenge was to parse the data page header. We know that the data page consists of two parts, 96-byte page header and 8096-byte data

Anatomy of SQL Server Second on the data page header reverse http://improve.dk/reverse-engineering-sql-server-page-headers/ in the Development of OrcaMDF when the first challenge is to parse the data page header, we know that the data page is divided into two parts, 96-byte page header and 8096-byte data

AnatomySQLSERVER SecondArticle Data PageHeader Proceed ReverseTranslation)

Http://improve.dk/reverse-engineering-sql-server-page-headers/

The first challenge during OrcaMDF development is parsing.DataPageHeader, we knowDataPageDivided into two parts, 96 bytesPageHeader and 8096-byteDataLine

Paul Randal wrote an article describing the page header structure. However, even though the article is very detailed, I still cannot find any format for page header storage.

For each fieldDataTypes and their order

We can use the dbcc page command to fill in some randomDataGo inDataPage, And thenPageDump out

PageNo. Is ):

DBCC TRACEON (3604)DBCC PAGE (TextTest, 1, 101, 2)

The result is divided into two parts. First, we get the formatted dbcc page.PageContent.SecondSome are 96 bytesPageHeader

Now, we need to find outPageHeadersDataValue correspondingDataWhat is the type?

For simplicity, we need to pay attention to some unique values so that we do not get some ambiguous values.

Starting from the m_freeCnt field, we can see that the m_freeCnt value is 4066, whileDataThe row size is 8060, so it is obvious that the m_freeCntDataThe type cannot be tinyint.

M_freeCnt is unlikely to use the int type. It is assumed that m_freeCnt may use the smallint type, which causesDataThe row can accommodate 0-8060 bytes.Data

Smallint: an integer from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767)DataThe storage size is 2 bytes. I think the value of m_freeCnt cannot be too large.

Now, the decimal number of 4066 is changed to the hexadecimal value 0x0FE2. The byte is switched to 0xE20F. Now we know that we have matched the m_freeCntDataType

In addition, we already knowDataType and location

/*    Bytes    Content    -----    -------    00-27    ?    28-29    FreeCnt (smallint)    30-95    ?*/

Continue our search. We can see that m_freeData = 3895 is converted into a hexadecimal value of 0x0F37 bytes after the 0x370F

We found that the m_freeCnt field is stored behind m_freeCnt.

With this technique, we can match the unique and unambiguous content stored in the page header.DataValue

However, for the m_level field, the values of the m_xactReserved field, m_reservedCnt field, and m_ghostRecCnt field are the same.

How do we know which value of 0 belongs to the m_level field? And how can we find outDataWhat about the type? This may be of the tinyint to bigint type.

Let's go to Visual Studio and shutdown SQLSERVER

Drag the mdf file into VS and VS will open the hex editor.PageOffset CalculationPageLocation

101*8192 = 827,392

Looking at the byte content marked by the red box, he has identified ourPageHeader content, and confirm that we have jumped to the correct position

Now, we will fill in some numeric values in the mdf file and save the file. Please do not create them randomly in production.DataDatabaseProceedTest

Before

After

Now we start SQLSERVER and run the dbcc page command again.

DBCC TRACEON (3604)DBCC PAGE (TextTest, 1, 101, 2)

You can note thatPageThe header becomes like this

Several numeric values have changed. The previous numeric value of the m_xactReserved field is 0, and now it is 30806. convert this number to hexadecimal format andProceedByte exchange returns 0x5678

Take a lookPageHeader. Now we have identified the value andDataType (smallint)

Let's update our page header table.

/*    Bytes    Content    -----    -------    00-27    ?    28-29    FreeCnt (smallint)    30-49    ?    50-51    XactReserved (smallint)    30-95    ?*/

Continue following this method and set the page headerProceedModify the modified PAGE header and dbcc page output in disorderProceedAssociation, it is possible to find outDataType

If you see the following message, you will know thatPageMessy Headers

You should be proud that no one can fix your randomly modified errors.

I have prepared a page header structure table.

/*    Bytes    Content    -----    -------    00    HeaderVersion (tinyint)    01    Type (tinyint)    02    TypeFlagBits (tinyint)    03    Level (tinyint)    04-05    FlagBits (smallint)    06-07    IndexID (smallint)    08-11    PreviousPageID (int)    12-13    PreviousFileID (smallint)    14-15    Pminlen (smallint)    16-19    NextPageID (int)    20-21    NextPageFileID (smallint)    22-23    SlotCnt (smallint)    24-27    ObjectID (int)    28-29    FreeCnt (smallint)    30-31    FreeData (smallint)    32-35    PageID (int)    36-37    FileID (smallint)    38-39    ReservedCnt (smallint)    40-43    Lsn1 (int)    44-47    Lsn2 (int)    48-49    Lsn3 (smallint)    50-51    XactReserved (smallint)    52-55    XdesIDPart2 (int)    56-57    XdesIDPart1 (smallint)    58-59    GhostRecCnt (smallint)    60-95    ?*/

I'm not sure about the correspondence between other bytes in the PAGE header and the fields output by DBCC PAGE.PageThese bytes seem to be stored as 0

I think these should be reserved bytes for some future use. Now, we have obtained the page header format. It is easy to read each field.

HeaderVersion = header[0];Type = (PageType)header[1];TypeFlagBits = header[2];Level = header[3];FlagBits = BitConverter.ToInt16(header, 4);IndexID = BitConverter.ToInt16(header, 6);PreviousPage = new PagePointer(BitConverter.ToInt16(header, 12), BitConverter.ToInt32(header, 8));Pminlen = BitConverter.ToInt16(header, 14);NextPage = new PagePointer(BitConverter.ToInt16(header, 20), BitConverter.ToInt32(header, 16));SlotCnt = BitConverter.ToInt16(header, 22);ObjectID = BitConverter.ToInt32(header, 24);FreeCnt = BitConverter.ToInt16(header, 28);FreeData = BitConverter.ToInt16(header, 30);Pointer = new PagePointer(BitConverter.ToInt16(header, 36), BitConverter.ToInt32(header, 32));ReservedCnt = BitConverter.ToInt16(header, 38);Lsn = "(" + BitConverter.ToInt32(header, 40) + ":" + BitConverter.ToInt32(header, 44) + ":" + BitConverter.ToInt16(header, 48) + ")";XactReserved = BitConverter.ToInt16(header, 50);XdesID = "(" + BitConverter.ToInt16(header, 56) + ":" + BitConverter.ToInt32(header, 52) + ")";GhostRecCnt = BitConverter.ToInt16(header, 58);

Let's take a look at the pageheader class I wrote.

using System;using System.Text;namespace OrcaMDF.Core.Engine.Pages{public class PageHeader{public short FreeCnt { get; private set; }public short FreeData { get; private set; }public short FlagBits { get; private set; }public string Lsn { get; private set; }public int ObjectID { get; private set; }public PageType Type { get; private set; }public short Pminlen { get; private set; }public short IndexID { get; private set; }public byte TypeFlagBits { get; private set; }public short SlotCnt { get; private set; }public string XdesID { get; private set; }public short XactReserved { get; private set; }public short ReservedCnt { get; private set; }public byte Level { get; private set; }public byte HeaderVersion { get; private set; }public short GhostRecCnt { get; private set; }public PagePointer NextPage { get; private set; }public PagePointer PreviousPage { get; private set; }public PagePointer Pointer { get; private set; }public PageHeader(byte[] header){if (header.Length != 96)throw new ArgumentException("Header length must be 96.");/*                Bytes    Content                -----    -------                00        HeaderVersion (tinyint)                01        Type (tinyint)                02        TypeFlagBits (tinyint)                03        Level (tinyint)                04-05    FlagBits (smallint)                06-07    IndexID (smallint)                08-11    PreviousPageID (int)                12-13    PreviousFileID (smallint)                14-15    Pminlen (smallint)                16-19    NextPageID (int)                20-21    NextPageFileID (smallint)                22-23    SlotCnt (smallint)                24-27    ObjectID (int)                28-29    FreeCnt (smallint)                30-31    FreeData (smallint)                32-35    PageID (int)                36-37    FileID (smallint)                38-39    ReservedCnt (smallint)                40-43    Lsn1 (int)                44-47    Lsn2 (int)                48-49    Lsn3 (smallint)                50-51    XactReserved (smallint)                52-55    XdesIDPart2 (int)                56-57    XdesIDPart1 (smallint)                58-59    GhostRecCnt (smallint)                60-63    Checksum/Tornbits (int)                64-95    ?            */HeaderVersion = header[0];Type = (PageType)header[1];TypeFlagBits = header[2];Level = header[3];FlagBits = BitConverter.ToInt16(header, 4);IndexID = BitConverter.ToInt16(header, 6);PreviousPage = new PagePointer(BitConverter.ToInt16(header, 12), BitConverter.ToInt32(header, 8));Pminlen = BitConverter.ToInt16(header, 14);NextPage = new PagePointer(BitConverter.ToInt16(header, 20), BitConverter.ToInt32(header, 16));SlotCnt = BitConverter.ToInt16(header, 22);ObjectID = BitConverter.ToInt32(header, 24);FreeCnt = BitConverter.ToInt16(header, 28);FreeData = BitConverter.ToInt16(header, 30);Pointer = new PagePointer(BitConverter.ToInt16(header, 36), BitConverter.ToInt32(header, 32));ReservedCnt = BitConverter.ToInt16(header, 38);Lsn = "(" + BitConverter.ToInt32(header, 40) + ":" + BitConverter.ToInt32(header, 44) + ":" + BitConverter.ToInt16(header, 48) + ")";XactReserved = BitConverter.ToInt16(header, 50);XdesID = "(" + BitConverter.ToInt16(header, 56) + ":" + BitConverter.ToInt32(header, 52) + ")";GhostRecCnt = BitConverter.ToInt16(header, 58);}public override string ToString(){var sb = new StringBuilder();sb.AppendLine("m_freeCnt:\t" + FreeCnt);sb.AppendLine("m_freeData:\t" + FreeData);sb.AppendLine("m_flagBits:\t0x" + FlagBits.ToString("x"));sb.AppendLine("m_lsn:\t\t" + Lsn);sb.AppendLine("m_objId:\t" + ObjectID);sb.AppendLine("m_pageId:\t(" + Pointer.FileID + ":" + Pointer.PageID + ")");sb.AppendLine("m_type:\t\t" + Type);sb.AppendLine("m_typeFlagBits:\t" + "0x" + TypeFlagBits.ToString("x"));sb.AppendLine("pminlen:\t" + Pminlen);sb.AppendLine("m_indexId:\t" + IndexID);sb.AppendLine("m_slotCnt:\t" + SlotCnt);sb.AppendLine("m_nextPage:\t" + NextPage);sb.AppendLine("m_prevPage:\t" + PreviousPage);sb.AppendLine("m_xactReserved:\t" + XactReserved);sb.AppendLine("m_xdesId:\t" + XdesID);sb.AppendLine("m_reservedCnt:\t" + ReservedCnt);sb.AppendLine("m_ghostRecCnt:\t" + GhostRecCnt);return sb.ToString();}}}

SecondSummary