Shulou(Shulou.com)06/01 Report--

A data page is a structure that contains user data that has been added to a database table.

As mentioned earlier, there are three kinds of data pages, each of which stores data in a different format.

SQL server has inline data pages, row overflow data pages, and LOB data pages.

Like all other types of pages in SQL server, the size of the data page is fixed at 8 KB or 8192 bytes.

They consist of three main components: a page header, a row of data, and an array of row offsets, as shown in figure 6-4.

Page title

As shown in figure 6-4, the page title occupies the first 96 bytes of each data page (8096 bytes reserved for data, row overhead, and row offset). Table 6-5 lists some of the information displayed when checking the page title.

Data rows of inline data

After the page title is the area where the actual data rows of the table are stored. Inline data with a maximum size of 8060 bytes for a single row.

Rows can also store row overflows and LOB data on separate pages.

The number of rows stored on a given page varies according to the table structure and the data stored.

A table with all fixed-length columns can always store the same number of rows per page

Variable length rows can store as many rows as possible based on the actual length of the input data.

Keeping the length shorter can make more rows fit the page, reducing the iUnix and increasing the likelihood that the required data will be cached.

Row offset array

The row offset array is a block of 2-byte items, and each entry represents the offset on the page at the beginning of the corresponding data row.

Each row has a 2-byte entry in this array (as discussed earlier, when you read the 10 overhead bytes required for each row).

Although these bytes are not stored in data rows, they do affect the number of rows appropriate to the page.

The row offset array indicates the logical order of the upstream of the page.

For example, if the table has a clustered index, SQL server stores the rows in the order of the clustered index keys.

This does not mean that rows are physically stored on the page in the order of aggregated index keys.

Instead, slot 0 in the offset array references the first row in the order of the clustered index keys, slot 1 refers to the second row, and so on.

As you can see when you examine the actual page, the physical location of these rows can be anywhere on the page.

View the data page

You can use the DBCC page command to view the contents of the data page

This allows you to view the page title, data row, and row offset table for any given page in the database.

Only the system administrator can use the DBCC page.

However, because you usually do not need to view the contents of the data page, you cannot find information about the DBCC page in the SQL server document.

However, if you want to use it, here is the syntax:

DBCC PAGE ({dbid | dbname}, filenum, pagenum [, printopt])

The DBCC page command contains the parameters shown in tables 6-6.

The code and results in listing 6-4 show sample output from the DBCC page with a printopt value of 1.

Note that DBCC TRACEON (3604) instructs SQL server to return the result to the client.

Without this traceflag, no output is returned for the DBCC page command.

As you can see, the output of the DBCC page is divided into four main parts: the buffer, the page title, the data, and the offset table (actually an offset array).

The buffer section displays information about the buffer for a given page.

The buffer in this context is the memory structure of the management page, and the information in this section is relevant only when the page is in memory.

Displays data for all title fields on the page from the title section of the output page of the DBCC page.

(table 6-5 shows the meaning of most of these fields. The data section contains information for each row.

A DBCC page with a printopt value of 1 or 3 is used to represent the slot location, that is, 6 tables per row store 257, the offset of the rows on the page, and the length of the rows.

The row data is divided into three parts.

The left column indicates the byte position in the row where the data is displayed.

The middle section contains the actual data stored on the page, showing eight hexadecimal numbers for every five columns.

The rightmost column contains the ASCII character representation of the data. Only character data in this column is readable, although some other data may be displayed.

The offset Table section displays the contents of the row offset array at the end of the page.

In the output of the DBCC page, you can see that the page contains 23 lines, with the first line (indicated by slot 0) starting with the offset 1585 (0x631).

The first row actually stored on the page is actually six rows, while the offset in the row offset array is 96. A DBCC page with a printopt value of 1 displays rows in the slot number order, and even if you can see it from the offset of each slot, it is not the order in which the rows actually exist on the page.

If you use a DBCC page with a printopt value of 2, you will see dumps of all 8096 bytes of the page (after the header) in the order in which they are stored on the page.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.