SQL Server performance Optimization (7) Nonclustered indexes

First, create a new test table

CREATE TABLE [dbo]. [Users] (
[ID] [int] IDENTITY (*) Not NULL,
[NAME] [Char] () not NULL,
[Creattime] [DateTime] Not NULL
) on [PRIMARY]
Delete the default clustered index and create a new nonclustered index on the name column

Insert 500 piece of data

View information for the page of the table

---turn on trace flag
DBCC TRACEON (3604,2588)
--DBCC Traceoff (3604,2588)
---Gets the data page of the object, structure: Database, object, display
DBCC IND (ixtest,users,-1)

Second, view the root page of the nonclustered index

DBCC page (ixtest,1,202,3)

1. The index root page has five sub-pages for 201~206. According to the previous figure, these 5 sub-page type are all 2 and also the pages of the index page, which is the middle node of the B + tree (not the page node).

2. These five subpages, each page contains 80 rows of data (258-179=79,337-258=179), and the Name column is divided, because the table now has only one index that is the name column.

3. The penultimate column is the row positioning pointer, which consists of three parts: the file identifier (ID) + the number of rows on the page.

How is the analysis performed? The reference document says:

For example, the second row of 0x 4f000000 0100 1200 minus the beginning of the 16 binary, the total 8 bytes left, from the right to the left row 2 bytes, the file ID2 bytes, the leftmost 4 bytes is the page number, so

Line number (0012) =18

File pages (0001) =1

Page number (0000004F) = 59 page

In summary, the 202 page is the root page of the nonclustered index, and the second line is "3,179", which points to a sub-index page (page number 201). There are 80 rows in this sub-index page, and each line points to 1 data. And the first position of the 80 data in the 1th file 59 pages of 18 lines.

Second, view the sub-index page of the nonclustered index

DBCC page (ixtest,1,201,3)

Look at the query plan by non-clustered index

SET STATISTICS IO on
SELECT * from Users WHERE NAME = ' Zhang 3,180 '

Summary of the query process for nonclustered indexes (such as name = ' 3,180 ')

1. First find the root index page, and the root index page will find all of its sub-indexes to see if the sub-index contains "3,180". This is the first time a physical page is read.

2. Locate the Sub-index page, and in the page of the sub-index page, locate the row where "3,180" is located. Then see the information for its RID. This is the second time the physical page is read .

3. Read this page from the hard drive, this is the third time to read the physical page .

SQL Server performance optimization (7) Nonclustered indexes