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

This paper makes a detailed analysis of the principle of table and index structure storage in SQL Server and how to speed up the search speed and improve efficiency.

The following figure shows the storage organization of the table, each table has a corresponding object ID, and contains one or more partitions, each partition will have a heap or more B-trees, the structure of the heap or B-tree is reserved. Each heap or B-tree has three allocation units for storing data, namely, data, LOB, and row overflow. The most frequently used allocation unit is data. If there is LOB data or records longer than 8000 bytes, there may be additional LOB allocation units and row overflow allocation units.

Small summary: a table can have multiple partitions, but each partition (heap / B-tree) can have up to three allocation units, and each allocation unit can have many pages. For the data pages in each allocation unit, depending on whether the table has an index, and whether the index is clustered or non-clustered, there are three ways to organize:

1. Heap

A heap is a table that does not contain a clustered index. There is a row in the sys.partitions of the heap, with index_id = 0 for each partition used by the heap. There is only one partition, and in the system table, each allocation unit under this partition has a connection to the Index Allocation Map page (IAM), which describes the zone information in the IAM page.

The column first_iam_page in the sys.system_internals_allocation_units system view points to the first page of a series of IAM pages that manage the allocated space for the heap in a particular partition. SQL Server uses IAM pages to move through the heap. The data pages and rows in the heap are not in any particular order and are not linked together. The only logical connection between data pages is the information recorded in the IAM page.

two。 Tables with nonclustered indexes

If there is a table that has only a nonclustered index but no clustered index, the corresponding index number is 2murme250. So for each nonclustered index, there is a corresponding partition, and in the system table, for each allocation unit under this partition, there is a connection to the root page. The data pages are connected with each other through the front and back pointers, which is a complete tree structure. At the bottom of the tree, there will be a connection to the real data in the form of file number + page number + line number, while the real data is stored as a heap. As shown in the following figure:

3. Table with clustered index

The clustered index in the table with the corresponding index number 1. It has a corresponding partition under which each allocation unit has a connection to the root page. For a clustered index, the leaf node holds real data, not a join like a nonclustered index. As shown in the following figure:

A nonclustered index and a clustered index have the same B-tree structure, and there are two significant differences between them:

The data rows of the underlying table are not sorted and stored in the order of non-clustered keys.

The leaf layer of a nonclustered index consists of index pages rather than data pages

Case study: let's look at the storage structure of a table. The table we use here is a production table with more than 100 million records. Check the object_ID of the table, as shown in the following figure:

I have partitioned this table to view its partition information. You can use the command shown in the following figure:

As you can see from the above figure, this table has 16 partitions, corresponding to different indexes, basically each partition has more than 10 million records. From this figure, you can also see that the ID of the heap or B-tree is the same as the partition ID. If you want to further view the specific information of an index, you can use the following command, such as viewing the information of 72057594067419136.

From this figure, we can see that there is only one allocation unit in this partition. IN_ROW_DATA indicates that this allocation unit is only used to store specific data, a total of 5353 pages, 5346 pages have been used, and data occupies 5320 pages.

If you want to see the location of the root page, you can use the following command:

However, it should be noted that the location of the root page shown here is 0xEC0100001100. Because of storage, it is parsed in reverse order, that is, 0x0011000001EC. The first two bytes indicate the filegroup number, and the last four bytes are the page number, that is, (1d0x01CE), which is replaced by decimal (1492). Then you can use the DBCC PAGE command we mentioned in the previous section to view the page information, as shown in the following figure:

You can see the specific data, and the return results of this interface will vary depending on the clustered and nonclustered indexes on the table. If you look at the total number of pages and extents used by a table, you can also use the command: DBCC SHOWCONFIG, as shown in the following figure:

In the case of the same table structure, the establishment of a clustered index will not increase the size of the table, but the establishment of a nonclustered index will increase a lot of space. In terms of performance, the SQL Server product group has been tested, and the performance of the clustered index is higher under select, update and delete operations. when inserting records, the performance of the clustered index is the same as that of the nonclustered index, and there is no phenomenon that the clustered index affects the insertion speed, but in the production environment. You still have to be careful.

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.