SQL Server Tour-second stop understanding the vicious table scan

A long time ago when we write SQL, the most feared one thing is the SQL inexplicable wonderful super slow, slow is a pipe back, the small ball is still turning ... This worry is only for the parties

Understand, later heard that there is a "evaluation of the Implementation Plan", later only to understand that should avoid the table scan ...

One: Table scan

1. Phenomena

"Table Scan" sounds very simple, not just a line of sweep, you have to say "execution plan" words, I will play, in order to be more impressive, I build a table, and then insert three rows of data, such as:

The person above I am an index is not built, and then where to look at the table scan is what kind of???

Sure enough to see the evil of the "table scan" three words, since it is the evil things, we must have a deep understanding of the next, and then we can find ways to avoid it ... So we must understand that this

Quality, the question came, and how did it sweep??? How do you break it? This must also be said from the data page ...

Second: A deep understanding of table scanning

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.

SQL Server Tour-second stop understanding the vicious table scan