SQL Server Storage Engine--data

4. Data chapter of SQL Server storage engine

(4.1) Documents

(0) Primary data file. MDF initial file size is at least 3MB, secondary data file. ndf initial size, the same log file at least 512KB;

(1) SQL Server logically uses filegroups to manage files in batches (similar to Oracle's tablespace), a filegroup can contain multiple files, and when data is inserted, all files in the same filegroup grow proportionally. For example: There are two files in the filegroup, the initial size is 100M and 200M respectively, when the 3M data is inserted, file1 new (100/300) *3m=1m,file2 new (200/300) *3m=2m.

(2) page, the data file in SQL Server is made up of 8K size data pages, the pages in each data file are numbered from 0, the page size cannot be customized, and each page belongs to only one data object.

(3) District (extent), or extension, 8 physically contiguous pages for an extension, i.e. 64k; the presence of an extension is designed to avoid the constant allocation of 8k pages, increasing the efficiency of page allocation. SQL Server has two types of zones, such as:

Mixed zone: In order to save space, a small amount of data table or index in the mixed area, when the table or index data growth to 8 pages, then use the Unified zone to store, a mixed area has 8 pages, each page can belong to different data objects, that is, each mixing zone up to 8 data object sharing.

Unified Zone: All by a single data object, if you create an index on existing data in the table, and the index is larger than 8 pages, the index will all use the uniform zone, and there is no allocation process for the blending zone.

(4.2) page

(4.2.1) Non-data pages

(0) The file header, the first page of each data file, the page number is 0, the page mainly includes the current file property description, such as: Filegroup ID, file ID, file current size, file maximum/minimum value, file increment, a series of LSN, etc.

(1) Page free space (PFS), second page of each data file, page number 1, which records the space status of each data page of the current data file: The page is empty, 1% to 50% full, 51% to 80%, full 81% to 95%, or full 96% to 100%. The PFS page uses 1 bytes to describe the allocation and space State of 1 data pages, and each PFS page has approximately 8,088 bytes of free space, that is, a PFS page appears in approximately 64M of space within the data file.

(2) Global Allocation Map (GAM), the 3rd page of each data file, page number 2, the page records the current data file allocation status of each area, 0 is used (as a mixed or unified zone), 1 is not used (free zone, unassigned).

(3) Shared allocation Mapping (SGAM), the 4th page of each data file, the page number 3, which records the current data file which areas are used as a mixed area, 1 is a mixed area containing free pages, 0 is a free zone or full mixed area.

(4) Index allocation Mapping (IAM), which keeps track of which data object the page in the data file belongs to, and the IAM header has 8 page pointers, pointing to the data object in the mixed area of the data page (if the data in the mixed area is deleted may be less than 8 pointers), An IAM page with a bit of 1 indicates that the area belongs to the data object to which it belongs, and a bit of 0 indicates that the zone does not belong to the data object to which it belongs.

　　　　　　　　

(4.1) Each allocation unit of each data object has an IAM page, iam, like GAM, Sgam can manage about 4G of space, if the allocation unit contains multiple files, or the file size is more than 4G, you need an additional IAM page to manage, the IAM page through a doubly linked list;

(4.2) The location of the First_iam page can be obtained through the sysindexes or sys.system_internals_allocation_units system directory, and the position of the IAM page in the data file is random, Perhaps the file where the IAM page resides is not the one that is being managed;

(5) Differential Change mapping (DCM), the 7th page of each data file, page number 6 (page number 4,5 is a reserved page), the page tracks the current data file, since the last full backup modified area, to improve the efficiency of differential backup, 1 is modified, 0 is not modified;

(6) Large-volume change mapping (BCM), the 8th page of each data file, page number 7, the page tracks the current data file, since the last log backup was modified by the large number of areas, 1 is modified, 0 is not modified;

(4.2.2) data page

(0) The data page contains the page header, the data row, the row offset matrix three parts, such as:

　　　　　　　　　　　　　　　　　　　　　　　

(1) in-line data (in_row_data), rows not exceeding 8060B, or single row more than 8060B but still stored on the current page of data, called inline data;

(2) Row overflow data (row_overflow_data), in SQL SERVER2005 and later versions, if the table defines a data type, allowing a single row of data length to break 8060B, the excess is the row overflow data, if the variable length column is updated and shortened, May be moved back to the in-row data page (typically less than 1000 bytes, SQL Server checks to see if it can be moved back).

(3) Large object data (Lob_data), such as Text/image/xml/varchar (max), such as the maximum length can exceed 8000B data type data;

Large object data is also stored in 8k data pages, the data page contains a 16-byte pointer to the root page of large object data, large object data through the B-tree structure to organize multiple data pages;

You can store large object data in a row data page by opening the text in row option, and when large object data is updated over 500B, the large object data is moved out of the inline data page, which is a log operation, so the move operation is time-consuming, so it is not recommended to turn on this option;

(4) Data line

Each row of data, in addition to the data for each column, includes the state bit, fixed-length column offset, total number of columns, NULL bitmap, variable-length column, and column-offset matrix, which is the row overhead.

Create a fully fixed long column table with data rows such as:

　　

　　　　　　　　

If object_id (' Test_col ') is not null

drop table Test_col;

GO

CREATE TABLE Test_col

(

col1 char (1),

Col2 Char (2)

)

GO

INSERT into Test_col values (' A ', ' B ')

--------------------------------------------------------------------------------------
Translated from: http://qianzhang.blog.51cto.com/317608/1217346
--------------------------------------------------------------------------------------

SQL Server Storage Engine--data