SQL server Optimization in terms of physical structure

The basic unit of data storage in SQL Server is page. Disk space allocated to data files (. mdf or. ndf) in the database can be logically divided into pages (from 0 to n consecutive numbers ). Disk I/O operations are performed on pages. That is, SQL Server reads or writes data pages.

　　When you use SQL Server Profiler or open the Statistics IO option, you can see physical reads. The unit of logic is page.

In SQL Server, the page size is 8 KB. This means that each MB of the SQL Server database has 128 pages. Each page starts with 96 bytesHeaderUsed to store system information about the page. This information includesPage number, page type, available space of the page, and allocation unit ID of the object that owns the page.

 

The following table describes the page types used in the data files of the SQL Server database.

Page type	Content
Data	WhenText in rowWhen set to ON, includingText,Ntext,Image,Nvarchar (max),Varchar (max),Varbinary (max)AndXmlAll data rows except data.
Index	Index entries.
Text/Image	Large Object Data Type: Text,Ntext,Image,Nvarchar (max),Varchar (max),Varbinary (max)AndXmlData. Variable-length data type columns when the data row exceeds 8 KB: Varchar,Nvarchar,VarbinaryAndSQL _variant
Global Allocation Map, Shared Global Allocation Map	Information about whether a zone is allocated.
Page Free Space	Information about page allocation and available space of the page.
Index Allocation Map	Information about the partitions used by tables or indexes in each allocation unit.
Bulk Changed Map	Information about the region modified by the large-capacity operation after the last backup log statement in each allocation unit.
Differential Changed Map	Information about the zones changed after the last backup database statement in each allocation unit.

On the data page, data rows are placed in sequence followed by headers. The end of the page is a row offset table. For each row on the page, each row Offset Table contains an entry. The distance between the first byte of the corresponding row and the header of each entry record. The order of entries in the row Offset Table is the opposite to that of the row on the page.

Large Row Support

A row cannot span pages, but some of the rows can be moved to the page where the trip is located. Therefore, the row may be very large. The maximum data size and overhead of a single row of a page are 8,060 bytes (8 KB ). However, this does not include data stored in the Text/Image page type. IncludeVarchar,Nvarchar,VarbinaryOrSQL _variantColumns are not restricted. When the total size of all rows of fixed and variable columns in the table exceeds the limit of 8,060 bytes, SQL Server dynamically moves one or more variable-length columns from the maximum-length column to the page in the ROW_OVERFLOW_DATA allocation unit. This operation is performed whenever an insert or update operation increases the total size of a row to 8,060 bytes exceeding the limit. After moving the column to the page in the ROW_OVERFLOW_DATA allocation unit, the 24-byte pointer is maintained on the original page in the IN_ROW_DATA allocation unit. If the row size is reduced in subsequent operations, SQL Server dynamically moves the column back to the original data page.

Data rows are stored on the page, which exceeds the page size.

In this way, we can easily come up with two principles:

Table Design:The Row occupies the shortest length and less space, so that more rows can be stored on a page. In this way, more data can be obtained with the same number of reads.

Query: When the Select statement is used, do not move it easily *, because when the row contains Image, text, etc., or the length of the variable-length field vchar exceeds 8 k, you need

ROW_OVERFLOW_DATA is read once. This increases the read overhead. Of course, it is not recommended to use * for network and other expenses *.