SQL Server index internal structure: SQL Server index Ladder level 10

authorDavid Durant, -years1Month -Day the series This article is“StairwaySeries:SQL Serverpart of the ladder of the index indexes are the basis of database design and tell developers to use the database with regard to the designer's intentions. Unfortunately, when performance problems arise, indexes are often added as an afterthought. Here at the end is a simple series of articles that should enable them to quickly make any database professionalFastat the previous level, we took a reasonable approach to the indicator, focusing on what they could do for us. Now it's time to take a physical approach and check the internal structure of the indicator;Understanding the internal nature of the index results in an understanding of the index cost. Only by understanding the exponential structure and how to maintain the exponential structure can we understand and minimize the cost of index creation, change and elimination;and rows are inserted, updated, and deleted. Therefore, starting from this level, we focus on the cost of indicators and the benefits of indicators. After all, minimizing costs is part of maximizing revenue. and maximizing the benefits of your indicator is that this ladder is all. leaves and non-Ye Shuping the structure of any indicator is composed of blades and non-blades. Although we have never explicitly expressed it, all previous levels have been concentrated at the leaf level of the index. Therefore, the leaf level of the clustered index is the table itself;each leaf level entry is a row of the table. For nonclustered indexes, each row contains the leaf level of an entry (in addition to the filtered index);Each entry consists of an index key column, an optional include column, and a bookmark, which is a clustered index key column orRID(LineID) value. index entries are also known as index rows;whether it is a table row (clustered index leaf level entry), refers to a table row (non-clustered index leaf level) or a page that points to a lower (non-leaf) level. The non-leaf level is a structure constructed at the leaf level, which enablesSQL Serverto:? maintain index entries in the index key sequence. Quickly find leaf-level rows based on index key values.in the1level, we use the phone book as a metaphor to help explain the benefits of indexing. We are looking for "Meyer,Helenthe phone book user knows that the portal will be close to the middle of any sorted LastName list and jump directly to the middle of the white page to start the search. However,SQL Serverno intrinsic knowledge of English surnames or other data. You will not know which page is the "middle" page unless it traverses the entire index from start to finish. SoSQL Serversome additional structures have been established in the index. non-leaf level this additional structure is called the non-leaf level or node level of the index;and is considered to be built at the top of the leaf level, regardless of where the physical location of the page is. Its purpose is toSQL Serverprovides a single page entry point for each index, and a short traversal from the page to the page that contains any given search key value. each page in the index, regardless of its level, contains index rows or entries. In leaf-level pages, as we have seen repeatedly, each entry points to a table row or table row. So if the table contains10billions of rows, the index's leaf level will containTenbillions of entries. at the level above the leaf level, i.e. the lowest non-leaf level;each entry points to a leaf-level page. If we haveTenbillion entry index average per page has -An entry, which is a realistic number for its search keyword indexed by several numbers, dates and code columns;then the leaf level will contain1,000,000,000/100 = 10,000,000a page. Conversely, the lowest non-leaf level will contain10,000,000Entries , each pointing to a leaf-level page, and will span100,000a page. entries for each higher non-leaf level page point to the next level of page. Therefore, our next higher non-leaf level will contain100,000entries, and the size is $page. The above levels will contain $entries, and the size isTenpage;The only entry that contains only 10 entries is one page.;This is where the stop is. The stand-alone page at the top of the index is called the root page. The level of the index below the root page and above the leaf level is called the intermediate level. The numbering of the levels starts from zero and works upward from the leaf level. Therefore, the lowest intermediate level is always level1. non-leaf level entries contain only index key columns and pointers to lower-level pages. included columns exist only in leaf-level entries;They do not take place in non-leaf level entries. In addition to the root page, each page in the index contains two additional pointers. These pointers point to the next and previous pages in the index sequence at the same level. the resulting two-way page chain makesSQL Serverability to scan any level of page in ascending or descending order. a simple example the following figure1The simple diagram shown helps illustrate the index of this tree structure. This figure indicates the use of the followingSQLin theoryPersonnel.employeeof the tableLastname/firstnameindex created on the column:

CREATE nonclustered INDEX Ix_full_name

On Personnel.employee

(

LastName,

FirstName,

)

GO

Chart notes: a pointer to a page consists of a database file number and a page number. Therefore, the pointer value is 5:4567 points to page 4567 of the database file #5 . Most of the sample values are from the Person.Contact table in the AdventureWorks database . For illustrative purposes, additional content has been added. Carl Olson is the most popular name in the sample. There are many Karl Olsens, whose entries span the entire intermediate index page.

Figure 1- vertical slicing of an index for clarity, the chart differs from the typical index for the following: the number of entries per page in a typical index will be greater than the number shown in the figure, so The number of pages per level other than root will be greater than the number shown. In particular, the leaf level will be much larger than what is shown in our space limit diagram. the actual indexed entries are not sorted on the page. This is the page's entry offset pointer, which provides a sequential access entry. (For more information on offset pointers, see

The correlation between the physical and logical order of an index is often higher than shown in the figure. The lack of correlation between the physical and logical order of an index is called an external fragment,11-Level- fragments in the discussion. as mentioned earlier, an exponent can have multiple intermediate levels. It's like our white page users are looking for Helen• Meyer, open the phone book and find the first page, only the first page is pink. In the list of sort entries for the Pink page, there is a "for" the name Between Fernandez, Zelda, and "Olson, Carl," see the Blue Page5:431. When our users go to the Blue page5:431, an entry on the page says: "Kumar,Kevinand theNara,Alisonbetween the name of the first see5page:2006". The pink page corresponds to the root, the blue page corresponds to the middle level, and the white page is the leaf. Index Depth The location of the root page is stored in the system table along with other information for the index. WheneverSQL Serverwhen you need to access an index entry that matches an index key value, it starts at the root page and processes a page at each level in the index until it reaches the leaf-level page of the entry that contains the index key. In the example of our 1 billion-row table, five page reads willSQL Servertransfer from the root page to a leaf-level page and its required entries;In Our illustrated example, three reading is sufficient. In a clustered index, the leaf level entry will be the actual data row;in a nonclustered index, this entry will contain the clustered index key column orRIDvalues. the series or depth of the index depends on the size of the index key and the number of entries. In theAdventureWorksThe depth of no index in the database is greater than three. In a database with very large tables or very wide index key columns, you may receive6or greater depth. Sys.dm_db_index_physical_statsThe function provides information about the index, including the index type, depth, and size. This is a table-valued function that can be queried. Checklist1The example shown in returnssalesorderdetailtablesummary information for all indexes.

SELECT object_name (p.object_id) as ' Table '

, i.name as ' Index '

, p.index_id as ' IndexID '

, P.index_type_desc

, p.index_depth

, P.page_count

From Sys.dm_db_index_physical_stats (db_id (),

OBJECT_ID (' Sales.SalesOrderDetail '),

NULL, NULL, NULL) P

JOIN sys.indexes I on i.object_id = p.object_id

and i.index_id = p.index_id;

Listing 1: Query sys.dm_db_index_physical_stats function result 2 shows .

read level 4 - page and range. )

Figure 2: The result of querying the sys.dm_db_index_physical_stats function is reversed, listing 2 The code in the display requests the details of a particular index, that is, a nonclustered index on the uniqueidentifier column of the table of the salesorderdetail table . It returns one row for each index level, as shown in3 . Listing 2: Query sys.dm_db_index_physical_stats for more information.

SELECT object_name (p.object_id) as ' Table '

, i.name as ' Index '

, p.index_id as ' IndexID '

, P.index_type_desc

, P.index_level

, P.page_count

From Sys.dm_db_index_physical_stats (db_id (), object_id (' Sales.SalesOrderDetail '), 2, NULL, ' detailed ') P

JOIN sys.indexes I on i.object_id = p.object_id

and i.index_id = p.index_id;

Figure 3: Query sys.dm_db_index_physical_stats Results for more information

from the figure3results can be seen as follows:? The leaf level of this index is distributed in407page. ? Only two pages are required for the only intermediate level. The root level is always a page.The size of the non-leaf portion of the index is usually one-tenth to 1% of the size of the leaf level;depending on which columns include the search keyword, the size of the bookmark, and which (if any) columns are included are specified. In other words, the index is relatively broad and short. This is different from most index sample graphs,1An example of an index in an index chart, which tends to be high and narrow. keep in mind that the included columns apply only to nonclustered indexes, and they appear only in leaf-level entries;they are omitted from higher-level entries, which is why they are not added to the non-leaf level size. because the leaf level of the clustered index is the data row of the table, only the non-leaf portion of the clustered index is additional information and requires additional storage space. Data rows exist regardless of whether the index is created or not. Therefore, creating a clustered index can take time and consume resources;However, when the creation is complete, the database consumes little space. Conclusion the structure of the index makesSQL Serverany entry that can quickly access a specific index key value. Once the entry is found,SQL ServerYou can:The line that accesses the entry. The index is traversed in ascending or descending order from that point.This index tree structure has been used for a long time, even longer than a relational database, and has proven itself over time. This article isSQL Serverpart of the index ladder

SQL Server index internal structure: SQL Server index Ladder level 10