SQL Server big Talk Storage structure (3)

Reading directory (content)

• 1 Introduction
• 2 Data rows
• 2.1 Data row structure
• 2.2 Special cases (large object, row overflow, and ForWord)
• 2.2.1 Large Objects
• 2.2.2 Line Overflow
• 2.2.3 ForWord
• 3 Test Storage conditions
• 3.1 Heap Table Analysis
• 3.2 Adding a primary key
• 3.3 Add a column: Nullable variable length column
• 3.4 Add a column: non-empty variable length column + default value
• 3.4.1 Non-large object columns
• 3.5 Delete columns with no data
• 3.6 Delete Columns with data
• 3.7 Rows Overflow
• 3.8 ForWord
• 4 Line structure and DDL

How is a row of data stored?    The variable-length column and the fixed-length column, null and NOT NULL, are actually organized into 8k data pages?    How will the row storage structure be affected by the addition or subtraction of tables, modification of length, adding default values and other DDL SQL?       What is a large object, what is a row overflow, and how does the storage engine handle them? If reproduced, please indicate the source of the blog: www.cnblogs.com/xinysu/, copyright belongs to the blog Garden su Home radish all.    Hope you support! Previous post in this series link: SQL Server big Talk Storage structure (2) _ Nonclustered index how to find Row Records back top (go to top) 1 introduced in a DB, each table can find the corresponding description in Sys.sysobjects, each column, can be from the SY    The description is found in the s.columns. This is a SQL that is used in daily administration to describe the data structure of a table. querying table Structure SQL

Top (go to top) 2 Data rows 2.1 data row structureData rows in the data page storage structure is described in the following table, divided into several parts: basic information 4 bytes, fixed-length column correlation, variable-length column-related and null bitmap. See the table below.   The content of this section is specific to the SQL Server Technology Insider: Storage Engine, chapter 6th.          For reference, the size of a row of data is calculated as follows: Row_size=fixed_data_size+variable_data_size+null_bitmap+4. Each part is actually better understood, the state B bit is not used, the state a bit, described below in detail.

• Status bit a: Bitmap representing row properties, 1 bytes, 8bit
• • Bit 0 bit, version information
  • Bits 1-3-bit, row record type
  • • 0,primary record, master records
    • 1,forwarded Record
    • 2,forwarding stub
    • 3,index record, Index records
    • 4,blob or row overflow data
    • 5,ghost Index Records
    • 6,ghost Data Logging
  • Bit 4-bit, null bitmap
  • Bit 5-bit, indicating a variable-length column in a row
  • Bit 6-bit, reserved
  • Bit 7-bit, Ghost record (Ghost recording)
• Column Offset matrix
• • If a table does not have a variable length column, then the table does not need a column offset matrix
  • A variable-length column that has a column offset matrix and a column offset matrix of 2 bytes that represent the end position of each column in the variable-length column.

2.2 Special cases (large object, row overflow, and ForWord) 2.2.1 Large ObjectsText, ntext, image, nvarchar (max), varchar (max), varbinary (max), and XML such data columns, called large object columns, note that variable-length data type Nvarchar,varchar,   varbinary becomes a large object column only if the storage content is greater than 8k. Rows cannot span pages, but the part of the row can move the page where the travel is located, so the row may actually be very large. The maximum amount of data and overhead in a single row of a page is 8,060 bytes (8 KB).    Consider that large object columns are extremely space-intensive, so in the master record of a row of data, there is no large object column stored, only 16 bytes pointing to the location where the large object column is actually stored on the LOB data page. For example, a large object column Text,text column stores 5000 characters, the other columns occupy 50 characters, if it is stored together, 10 rows of data will need 10 page, the scan requires 10 IO; and if you don't put it at once, a in-row-data The page can store 10 rows of data, and the text column is stored separately in the LOB data column, so scanning the master record of the 10 rows requires only 1 IO.    Therefore, large object columns are not stored with the master record. Thus, a 8k data page can store as many master records as possible, avoiding large object columns occupying the master record space when querying, resulting in increased IO times. 2.2.2Row overflow exceeding 8,060 bytes of row size limit can affect performance because SQL Server still maintains a 8 KB per page limit. When a column that incorporates a varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type exceeds this limit, the SQL Server database engine moves the record column of the largest width to the Row_overflow_data On another page of the allocation unit, and then record a 24-byte pointer in the master record, with the actual storage location of the column that the description was moved out of.    For example, the total size of a row of data is more than 8k, then in the insert process, the maximum width of the record will be moved to another data page. If the update operation makes the record longer, large records are moved dynamically to another page. If the update operation makes the record shorter, the record may be moved back to the original page in the In_row_data allocation unit.    In addition, executing queries and other selection operations, such as sorting or merging large records that contain row-overflow data, will prolong processing time because the records will be processed synchronously rather than asynchronously. A row of data (excluding large object columns) has a total length of more than 8k, which moves the contents of the column with the largest width to the Row_overflow_data page, leaving a 24-byte pointer on the master record to describe the actual storage location of the column content being overrun, which is called a row overflow. 2.2.3ForWord a data page in a pile of tables, storing n rows of data, where a column of one row of data is modified, causing its column length to increase, and the remaining page space cannot store the column data, then the column data is moved to the new In_row_data page.    Leave a 9-byte pointer in the main record, pointing to the storage location of the actual column, which is called ForWord.    The forward condition is that the heap table, variable length column, update operation, and the remaining space on the data page are not sufficient to store the new column contents. Why do we have to pile up tables? Because if this is a clustered index table, the data page will split, storing half of the content in addition to the new data page, because the nonclustered index key value query on the clustered index is based on the primary key, so the split operation does not affect the nonclustered index, but the nonclustered index structure lookup row for the heap table is based on the RID    , if also split, all nonclustered indexes need to modify the key-value RID, so on the heap table, ForWord is used. Why is the update operation?     Because if it is an insert operation, there is a lack of space in the beginning, it has run away to the new data page, no more space on the data page insert operation. For example, a row of data is originally stored in a data page, but update a column, increase its storage content, found that the data page does not have free space to store the column content, the column will be forword to another data page In_row_data storage, the main record left a 9-byte pointer. Top (go to top) 3 Test Storage conditionsTest ideas

1. Create a heap table with only 2 columns of non-empty fixed-length columns , and insert a row of data to check page store contents
2. Add primary key to check storage page contents
3. Add a column: Nullable variable length column
4. Add a column: non-empty variable length column + default value (large objects and non-large objects)
5. Delete columns with no data
6. Delete columns that have data
7. Row overflow
8. ForWord

3.1 Heap Table AnalysisCREATE TABLE Tbrow (ID int not NULL identity (max), name char () not NULL) INSERT into Tbrow (name) select ' Xinysu ';d bcc trace On (3604) DBCC IND (' dbpage ', ' Tbrow ',-1)--judging by the return result, 324 is the data page, if not understood, see the first post in this series DBCC page (' Dbpage ', 1,324, 3)

Look at the ' message ' content, you can see that slot 0 stores the row data size is 21 bytes, because now the Tbrow table, only two columns int and char, because are fixed-length columns, all the variable-length columns of the enclosure is empty, but note that even if the entire table does not allow null columns,    A null bitmap will still occupy one byte.    So the length of the line record = status A + status B + Fixed Length field length + Fixed Length field content + Total martyred +null bitmap =1+1+2+ (4+10) +2+1= bytes. Based on the row's 16 record: 10001200 01000000 78696e79 73752020 2020020000 to analyze the storage of this row of data in detail. This string of characters is first distinguished by the number of bytes, in which the note part needs to be reversed and then converted to decimal. Detailed analysis and derivation see. 3.2 Adding a primary keyALTER TABLE Tbrow ADD constraint Pk_tbrow primary key (ID) DBCC TRACEON (3604) DBCC IND (' dbpage ', ' Tbrow ',-1) can see that the table's IAM page and number It all changed, because when a heap table adds a primary key to a clustered index table, the data page needs to be re-organized, stored in the order of key values for the clustered index, so that the entire data page storage has changed.    If you are adding a clustered index to a large heap of tables, this is a very time consuming and IO-intensive operation, and will lock the table until the end of the operation and be cautious. Analyze the current row record again. DBCC page (' Dbpage ', 1,311, 3) can see that the contents of the data row have not changed, adding a primary key (clustered unique index), will reorganize the entire table storage order, but will not affect the data in the row. 3.3 Add a column: Nullable variable length columnALTER TABLE Tbrow ADD constraint Pk_tbrow primary key (ID) DBCC TRACEON (3604) DBCC IND (' dbpage ', ' Tbrow ', -1) dbcc page (' Dbpage ', 1,311, 3)

It began to be interesting to find that, after adding a column of nullable nullable columns, the row record 16 binary did not change. The comparison is as follows. /* First behavior heap table row record second behavior after adding primary key row record third behavior add nullable variable long column after row record 10001200 01000000 78696e79 73752020 202002000010001200 01000000 78696e79 73752020 202002000010001200 01000000 78696e79 73752020 2020020000*/ even if the table has columns that are NULL, there are variable-length columns, but only those columns have no values, and it is important that the data records for this row are not affected! Because it means that when you add an empty column to a table, the storage engine does not need to modify the row record stored in the table, just add a change to the data dictionary, which requires a schema lock on the table and executes it, which is very fast.      This is handled very differently from MySQL, although 5.6 adds the online DDL to avoid the table lock table during DDL, but when it comes to adding columns, it involves table structure changes and new temporary files are needed to store the frm and IBD files, which is an IO-consuming process. See the previous blog post in detail: improvements and applications for MySQL Online DDL. 3.4 Add a column: non-empty variable length column + default value3.4.1 Non-large object column ALTER TABLE Tbrow Add task varchar (A) NOT null default ' all A ';d bcc traceon (3604) DBCC IND (' dbpage ', ' Tbrow ',-1 ) DBCC page (' Dbpage ', 1,311, 3) view 16 binary line records: 10001200 01000000 78696e79 73752020 2020020000, found the same as before, view the table contents, set the Not    Null with the default value of the column, in fact, the query out of the task column is stored in value, the store content is ' all A ', but when viewing the 16 binary content, but found that the data page in the row record storage content has not changed. This is a magical way of handling!    Why is it? Look closely at the parsing of the page and find: Slot 0 Column 4 Offset 0x0 Length 5 length (physical) 0. The column data length is 5, but the actual storage length is 0, which means that the column is not stored in the data page at all. personal speculation: When a NOT NULL column + default value (not a large object column) is added, there is no modification to the previous data store record, but at the time of the query, it is determined whether the column has stored data and if not, the default value is displayed. This has a very big benefit: Save storage space, do not change the row records, DDL period, no need to do the previous record processing, only need to modify the data dictionary. 3.4.2 Large Object ColumnsALTER TABLE Tbrow Add descriptions text NOT null The default ' I love SQL Server ';d bcc traceon (3604) DBCC IND (' dbpage ', ' Tbrow ', -1) A thin table, a row of records, because a large object column was added, an IAM page for LOB data, and a data page for LOB data. However, this time only the master record data page pageid=311 is analyzed.    --Master Record Data page pageid=311dbcc page (' Dbpage ', 1,311, 3) still to analyze the row to store records, the original length is 21, why add a text with default values of the column, the length is increased to 50bytes? Note here two places: the original task column and the Description column.    The task column is not actually storing the data content, but now the data content is stored, the description large object column does not store the data in the master record, but is stored in the other LOB data data page, in the master record only the description of the column specific location content, accounting for 16bytes.    So the length of the line record = status A + status B + Fixed Length field length + Fixed Length field content + total number of columns +null bitmap + variable length column number + column offset Matrix + variable length data content =1+1+2+ (4+10) +2+1+2+2*3+ (5+16) = bytes. Take a look at this 16 binary string: 30001200 01000000 78696e79 73752020 20200500 0403001d 00220032 80616c6c 20410000 d1070000 00004b01 00000100 0000, detailed analysis of the storage of this row of data. First of all, this string of characters in accordance with the number of bytes, detailed analysis and derivation see.

This leads to several inferences: The column not null+ default value of a large object is the actual storage of default values on the data page, and the other columns in the table that do not store the default values are affected, and the entire table becomes the actual storage of the default values in the data page. When a large table needs to add a column of large object columns not null+ default values, it will affect each row of records in the table, each row of records to add a 16 byte to describe the storage location of large object columns, and the original column does not store the default values, will also actually store the default value to the data page, This is a lock table long-consuming IO operation, for a large table. Is it a horrible thing to find yourself adding a large object column + default value? If there is such a demand, and it is a big watch, please consider it carefully. 3.5 Delete Columns with no data--based on previous query results, skill This column is an ALTER TABLE tbrow drop column skilldbcc traceon (3604) DBCC IND (' dbpage ', ' Tbrow ', -1) with no stored data Page (' Dbpage ', 1,311, 3) can be found, delete this column, the actual data store has no effect, but the column will have an identity value dropped=[null] indicates that the column has been deleted, note that this representation is not stored in each row of data,    Instead, the database storage engine records. Interception data page inside of the 16 content: 78696e79737520202020 01000000, 0500 0300 1d0022003280 616c6c2041 0000d107000000004b01000001000000, found the same as before the deletion, the comparison is as follows:/* The first row is recorded as deleted before the second row is recorded for deletion after 01000000 78696e79737520202020 0500 0300 1d0022003280 616c6c2041 0000d107000000004b0100000100000030 78696e79737520202020 0500 04 0300 1d0022003280 616c6c2041 0000d107000000004b01000001000000*/concludes that deleting a row of columns without data does not require modifying the in-row data store, only modifying the data dictionary involved to hold the schema lock during deletion. This is a very fast process (but if the table has been manipulated by other users, the request for schema lock will also be waiting). 3.6 Delete Columns with data--based on previous query results, skill This column is an ALTER TABLE tbrow drop column namedbcc traceon (3604) DBCC IND (' dbpage ', ' Tbrow ', -1) that does not store data in the DBCC page (' Dbpage ', 1,311, 3) analysis here, you can find that SQL server in the processing of the Delete column is very ingenious, minimizing the impact on table availability, regardless of the data, delete, only processing the data dictionary class related content, to identify that the column has been deleted,    But actually did not go to each page to delete the data, but the space occupied by these columns is logically modified to not exist, allowing later write overwrite. As a small DBA, I personally think that the storage structure of the row data, for the addition of columns or delete column processing, SQL SERVER design is very clever and efficient! Compared with MySQL's improved online ddl,sql server, the availability of the table is greatly increased and the impact on system resources is reduced. (Only the increase of the discussion column deletes the DDL this piece) 3.7 Rows OverflowLine overflow this block, not analyze its 16-line record, focusing on the handling of the row overflow. #新表格测试create table Tbflow (id int not NULL, cola varchar (6000), Colb varchar (6000), Colc varchar (6000)) INSERT into Tbflow SEL ECT 1,replicate (' 1 ', + +), replicate (' 1 ', ""), Replicate (' 1 ',) DBCC TRACEON (3604) DBCC IND (' dbpage ', ' Tbflow ',-1) DBCC page (' Dbpage ', 1,334,3) Cola column 1000 characters, colb column 5,000 characters, colc column 3,000 characters, not counting other bytes used, light 3 column length of the sum of more than 8k, according to row overflow processing, can be pushed to measure is Colb is moved to the Row-overflow data column, so the first analysis of page 334, to see the main record storage, the actual situation and speculation consistent. 3.8 ForWordForWord this piece, does not analyze its 16 binary line record, focuses on the ForWord processing way. CREATE TABLE Tbforword (ID int not NULL, cola varchar (6000), Colb varchar (6000), Colc varchar (6000)) insert INTO Tbforword sel ECT 1,replicate (' 1 ', +), replicate (' 1 ', $), replicate (' 1 ', $) insert into Tbforword select 2,replicate (' 1 ', 1000), Replicate (' 1 ', $), replicate (' 1 ', $) insert into Tbforword select 3,replicate (' 1 ', +), replicate (' 1 ', 500), Replicate (' 1 ', +) DBCC TRACEON (' dbpage ', ' Tbforword ', -1) #记录 iam 385, the master record is 384 page update Tbforword set colb= Replicate (' 1 ', 4500) where ID=2DBCC traceon (3604) DBCC IND (' dbpage ', ' Tbflow ', -1) pageid=384 data page, storing 3 rows of records probably used 6k+ space, this time , the id=2 colb column modified to 4.5k length, more than a page 8k range, it means that the modified column will be ForWord, according to the new data page 386, it can be inferred that the ForWord column is stored in 386. Now analyze PageID 384来 to verify the conjecture. See, the findings are consistent with speculation. DBCC page (' Dbpage ', 1,384, 3) back to the top (go to top) 4 line structure and DDL

SQL Server big Talk Storage structure (3)