SQL Server storage mechanism

1. Section

A section (extent) is a basic storage unit used to allocate space for tables and indexes. It consists of 8 contiguous 64KB data pages.

The main points of the concept of allocating space based on segments rather than actual use of space:

1. Once the section is full, the space that the next record will occupy is not the size of the record, but the size of the whole new zone. Assign one segment at a time instead of assigning a record.
2. By pre-allocating space, SQL Server saves time to allocate new space for each record.

Adding too many rows to the currently allocated section is going to take up the entire segment, which seems like a waste, but this method wastes little space. However, these wasted spaces accumulate, especially in more fragmented environments.

The advantage of taking up all the space is that SQL Server eliminates the overhead of allocating time and does not need to consider allocation issues every time a row is written, and SQL Server processes additional space allocations only when a new section is needed.

Do not confuse the space occupied by the section with the space occupied by the database, and the space allocated to the database is the amount of free space that the disk drive will have to reduce. A section is simply a way to allocate again within the entire space that the database retains.

　　2. Page

A page is an allocation unit in a specific section. Each section contains 8 pages.

It is also the last storage level that can be reached before the actual data row is reached. Although the number of pages in each section is fixed, the numbers of rows in each page are not fixed. This depends on the size of the row, and the size of the row can vary. You can think of a page as a container for table rows and index row data. Cross-line is not normally allowed.

Common page types:

1. Data pages: Data is also not to be interpreted-they are the actual data in the table, except for any BLOB data that is not defined with the text in row option, varchar (max), or varbinary (max).

2. Index pages: Index pages are also fairly intuitive-they include both non-page and page-level pages for non-clustered indexes, and non-page-level pages for clustered indexes.

　　In SQL Server, the smallest unit of storage is the page. SQL Server reads the page, either the entire read, or completely without reading, without compromise.

The size of the database page is 8K, and the actual amount of data that can be stored is 1024*8=8192-header (96)-line offset (36) = 8060 bytes.

　　Page splits

When the page is full, it is split. This means that multiple new pages are assigned-and also means that nearly half of the data on the existing page is moved to the new page.

There are exceptions when using clustered indexes. If you have a clustered index and the next inserted record is physically the last record in the table, create a new page and add the new row to the new page without relocating any existing data.

　　3, line

A row is a row of records in a table. The maximum line can be up to 8KB. In addition to the 8060-character limit, there is a maximum of 1024 standard columns (non-sparse columns), and the column is a column of data for the table, which is the column you understand. In fact, it is not uncommon to run out of columns before breaking the 8060 character limit. 1024 provides an average column width of less than 8 bytes. 1024 columns, a table is usually only more than 10 columns ah. When you break the 1024 columns, you apply the sparse columns.

　　4. Sparse columns

Sparse columns are a special kind of data structure introduced by SQL Server2008. They are primarily used to handle repetitive scenarios where the columns are only occasionally used. Most of the time it's empty. Many times, once a small number of such columns are encountered, it means that a large number of such columns may be encountered in the future. With sparse columns, you can increase the total number of columns allowed in a single table to 30000. What's the concept of 30,000 columns? What system is used to get so many columns.

The Image, text, ntext, geography, geometry, timestamp, and all user custom data types cannot be marked as sparse columns.

In SQL Server, the storage of data is in page units. Eight pages for a single area. A page of 8K, a zone of 64K, this means that 1M of space can accommodate 16 zones. 1 is shown below:

Figure 1. Pages and extents in SQL Server

1 (PS: Found in the Windows own drawing program to draw the picture in the blog is also good) you can see that the allocation unit in SQL Server is divided into three kinds, respectively, the in_row_data of storing the data in the row, storing the lob_data of the LOB object, storing the overflow data row_ Overflow_data. Let's take a more specific example to understand these three allocation units.

I created the table shown in 2.

Figure 2: Test table

The test table in Figure 2 is not difficult to see by inserting data so that the length of each row exceeds the maximum length of 8060 bytes that can be accommodated per page. This results in not only a row overflow (row_overflow_data), but also a page that stores the LOB. The inserted statement of the test and the distribution seen through the DBCC IND are shown in 3.

Figure 3: Pages allocated for rows with more than 8060 bytes

When you drop an IAM page, the 1 rows of data require three pages to store. The first is the LOB page, which is designed to store binary files that exist in the database, and when this type of column appears, a 24-byte pointer is stored in the original column, and the specific binary data exists in the LOB page, except for the text, VarBinary (max) exists in the LOB page. Then is the overflow line, in SQL Server 2000, a row more than 8060 bytes is not allowed, the version after SQL Server 2005 has improved this feature, when using data types such as Varchar,nvarchar, when the row size does not exceed 8060 bytes , all in-line in-row data, when there is too much data stored in varchar to make the whole row more than 8060 bytes, the additional parts will be stored in the Row-overflow data page, if the update column reduces the row size to less than 8060 bytes, The line will return to the In-row data page.

SQL Server storage mechanism