DBCC IND,DBCC TRACEON,DBCC page data page internal exploration clustered index

Transferred from: http://www.cnblogs.com/huangxincheng/p/4231335.html

DBCC IND,DBCC TRACEON,DBCC page data page internal exploration clustered index

1: Data page

There is no reason to learn that SQL Server does not know that our records are stored as data pages, and that the size of the data page is 8k .... Where's the data page? I can

Let you see the truth.

At first glance I painted a lot of, do not be afraid, do not think that painting more, think advanced ... My simple anatomy.

<1>:d bcc ind Command

If you want to see the data page, SQL Server really provides a dedicated command DBCC satisfies you, you might ask SQL Server to provide the parameters of the IND command? I'm telling you, there really is.

, but this should turn on 2588 tracking, just like this.

<2>:pagefid,pagepid,iamfid

Just said, there are many data pages, the default is the table data page, in fact, there are IAM data pages, nothing unusual, IAM is used to track the table data page, so the above diagram,

The Iamfid field is a null record, which is the IAM page, and the following pagepid=78 is the table data page.

2. View data page

In order to avoid confusion, I would like to talk about the internal structure of the data page is probably a look, so that everyone has a general impression.

As you can see, there are a lot of slots at the end of the data page, which point to the actual records of the addresses in the area, so the table scan actually sweeps the slots,

Or take the three records in the person table above, they are saved in the 78th data page, now out of curiosity to the 78th data page export, say dry .... Very simple, you need to

There are two things to do:

<1> Turn on 3604 tracking: DBCC traceon (3604)

<2> Use the DBCC PAGE command to export the No. 78th Data page (pagefid:pagepid) = (1:78) below the 1th file, as follows ...

Data Header (page header):

Data content (Page data):

Data slot (Page slot):

If you see three slots above (0,1,2) and have the corresponding offset address (0X7E,0X92,0XBA), this address points to the offset address of the actual record in the data area.

Well, that's it, no more talking, no more washing and sleeping.

When it comes to clustered indexes, I think every yard knows, but there are a lot of rustic programmers like me that can only use rote memorization to solve this problem, what table can only build a clustered index,

Then it pulls in a directory search to help readers remember .... The problem is here, we're not studying liberal arts, we don't need to memorize, we need to be able to see in the eyes

Real things ..... We all like the clustered index because it can make the unordered heap table records orderly and play the B-tree ... This reduces the complexity from N to logmn ...

So the logical reading, the physical reading down.

A: Phenomenon

1: Non-indexed case

Or the usual, look at an example to feel, first I have a product table, there is no index, such as:

From this, I saw the tragedy, the physical reading is 9 times, also explained that walked 9 times the hard disk, you can also think that the purpose of the drive is to take data, logic read 1636 times, to pay attention to is here

"Times" is the meaning of "page", that is, in memory walk 1636 data pages, I use DBCC IND to show you, there is not 1636 table data page.

Here are 1637 data pages because the first is an IAM trace page.

2: The case of a clustered index

Here I build a product_idx_productid clustered index in the product table, and then look at the IO situation again, such as:

When you see this "logical reading" for 3 times, you are not already crazy ... Find the target data in up to 1636 data pages, just 3 times .... This is not a God in Literacy's eyes.

The fairy is down to earth??? Of course, this thing is in the sky, and the earth has ... Since there is, there should be a very strong desire to explore .... Let's see how this is played ...

Second: the principle of exploration

1: Explore leaf nodes

Just said, the clustered index play is B-tree, since it is a B-tree, then there are leaf nodes and branch nodes, the professional term is the degree of 0 for the leaf node, the degree of >0 called Branch node ....

I think you've heard that the clustered index is to sort the indexed column data and put it into the B-tree, and to make you see it, I'm going to set up a 3-ID unordered record.

Insert into the person values (2, ' bbbbb ') inserts into the person values (3, ' CCCCC ') inserts into the person values (1, ' AAAAA ')

Then I use the DBCC IND command to see which data page the next 3 records are in,

As you can see, my three records are placed on the 148th data page, and then I export the 148th data page to see what the content is.

DBCC TRACEON (3604) DBCC page (ctrip,1,148,1)

From there, we see that each slot in the data page is pointing to the actual storage record in the table, so let me create a clustered index to see if the actual data is actually in order.

Create clustered index ctrip_idx_id on person (ID)

But here is an interesting question, my number 148th "table Data page" where??? is also a wonderful enough, in exchange for the 173th index page, that in order to ensure data integrity, it should be

The contents of the 148th data page are poured into the 173 index page, right???? It's okay, check it out.

1 DBCC TRACEON (3604) 2 DBCC page (ctrip,1,173,1)

Through the above figure, there is no intuitive feeling to it? The data is now in the AAAAA,BBBBB,CCCCC mode ... Orderly .... 148th data is also saved in the index page

page field values, such as id,name information, take the following slot0 slots for example:

So far, I think you have a general understanding of the content of the leaf node, at least not to let you memorize it ~ ~ ~

2: Explore Branch nodes

In order for you to see the branch node, I have to fill in some more data, at least to let the data burst an index data page, so that the branch node index data page is out, see the following example:

As you can see, when I insert 1000 data, there is a branch node (120th index data page), three leaf nodes (173,121,126), leaves

Data page content of a node I've said it, and now I'm curious what's in the branch node??? I'm so excited, I'm going to export the index data page 120th ...

1 DBCC TRACEON (3604) 2 DBCC page (ctrip,1,120,1)

Simple analysis of the contents of slot0:06000000 00ad0000 000100

00000000: The minimum key value in the Leaf index page (a bit special here, except that a row of records is not the minimum value, the rest is the same), and the conversion to decimal is 0.

ad000000: The page number of the leaf index page, converted to decimal is 173.

0100: Leaf index page file number, converted to decimal is 1.

However, through the analysis, we see that, in fact, there are two values in the branch node, a childpage Minkey, a childpage PageID, similarly, the other slots are the same.

Let's change the parameter command to make the results more intuitive, and the record is to keep "PageID" and "Minkey".

In this case, I have a picture in my mind, I do not know if you have now????

The above analysis, in addition to the first row of records not to save the value of the smallest key in the Sub-index page, the other records are extracted from the sub-index page of the smallest index key value, it is important to note ...

Perhaps for the SQL Server team, as long as the decision is less than 449 directly to the (1:173) data page, less than 889 directly to (1:121) data page can be ...

When you see this, do not know if you already understand, why the table can only have a clustered index it??? Well, the mess of the chicken pulled a lot, I hope you have some help.

DBCC IND,DBCC TRACEON,DBCC page data page internal exploration clustered index