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

This article is "Stairway Series:Part of the ladder for SQL Server indexingIndexes 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. The end here is a simple series of articles that should enable them to quickly make any database professional "fast"At the previous level, we took a reasonable approach to the indicator, focusing on what they could do for us. It is time to take a physical approach to checking the internal structure of the indicator, and understanding the internal nature of the index leads to an understanding of the index overhead. 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 line insertions, updates, and deletions.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 ShupingThe 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, and 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 (rowID) value.Index entries are also referred to as index rows, whether it is a table row (clustered index leaf level entry), 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 built at the leaf level, which enables SQL Server to:? maintain index entries in the index key sequence. Quickly find leaf-level rows based on index key values.At level 1, we use phone books as a metaphor to help explain the benefits of indexing. We are looking for "Meyer,Helen "The phone book user knows that the entrance will be close to the middle of any sorted list of surnames and jump directly to the middle of the white page to start the search. ButSQL Server has no 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 Server has built some extra structures in the index.non-leaf levelThis 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 the physical location of the page. Its purpose is toSQL Server provides 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 contains 1 billion rows, the leaf level of the index will contain1 billion 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 have1 billion item index average per page100 entries, which are a realistic number for their search keywords indexed by several numbers, dates and code columns; then the leaf level will contain1,000,000,000/100 = 10,000,000 pages. Conversely, the lowest non-leaf level will contain10,000,000 entries, each pointing to a leaf-level page, and will span100,000 pages.Entries for each higher non-leaf level page point to the next level of page. Therefore, our next higher non-leaf level will contain 100,000 entries, and the size is1,000 pages. The above levels will contain1, 000 entries, and a size ofPage 10The top one with only 10 entries has 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 level 1.non-leaf level entries contain only index key columns and pointers to lower-level pages. The 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 enables SQL Server to scan any level of pages in ascending or descending order. A simple example of the simple diagram shown in Figure 1 below helps illustrate the index of this tree structure. This figure represents the index created using the following SQL on the lastname/firstname column of the Theoretical personnel.employee table:

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 topage 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 Orsen 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 outside of 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, at level 11th-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 Mayer, open the phone book, 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:At 431, an entry on the page says: "Kumar,Kevin andNara,Alison between the name of the first seePage 5:2006 ". The pink page corresponds to the root, the blue page corresponds to the middle level, and the white page is the leaf. Index DepthThe location of the root page is stored in the system table along with other information for the index. Whenever SQL Server needs 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 Server transfers from the root page to the leaf-level page and its required entriesin 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 or RID value. the series or depth of the index depends on the size of the index key and the number of entries. In the AdventureWorks database, the depth of no index is greater than three. In databases with very large tables or very wide index key columns, a depth of 6 or greater may occur. the Sys.dm_db_index_physical_stats function provides information about the index, including the index type, depth, and size. This is a table-valued function that can be queried. The example shown in Listing 1 returns summary information for all indexes of salesorderdetailtable.

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 4th - page and range. )

Figure 2: Instead of querying the results of the sys.dm_db_index_physical_stats function , the code shown in Listing 2 requests the details of a particular index, which is the table of the SalesOrderDetail table. the nonclustered index on the uniqueidentifier column. It returns one row for each index level, asshown in 3. 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

As can be seen from the results of Figure 3:? The leaf level of this index is distributed in407 pages.? 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, if any, which columns are included. In other words, the index is relatively broad and short. This is different from most index sample graphs,1 An example of an index diagram, which tends to be high and narrow.   remember that the included columns apply only to nonclustered indexes, which 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, it may take time and resources to create a clustered index, but when the creation is complete, the database consumes little space.   conclusion   The structure of the index enables SQL Server to quickly access any entry for a particular index key value. Once the entry is found, sql server can:   the line to which the entry is accessed. From this point, the index is traversed in ascending or descending order.   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 is part of the SQL Server index ladder

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