Deep understanding of table scans in SQL Server _mssql

A long time ago, when we were writing SQL, one of the things we feared most was the super slow of SQL, the slow way to get a pipe back, the ball was still spinning ... This worry only the parties understand, and later heard that there is a "evaluation of the Implementation Plan", later later understand should avoid table scan ...

One: Table scan

1. The phenomenon

"Table Scan" sounds simple, not just a line of sweep, you have to say "implementation plan", I will play, in order to more impressive, I build a table, and then insert three rows of data, the following figure:

The person above I am an index is not built, and then where, to see what the table scan is like???

Sure enough to see the evil "table scan" three words, since it is the evils of things, we must understand deeply, then we can how to find ways to avoid it ... So we must understand the essence, the question comes, how exactly does it sweep??? How do you break it? This also has to start with the data page ...

Two: A deep understanding of table scans

1: Data page

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

Make you see the truth.

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

<1>:d bcc ind Command

If you want to look at the data page, SQL Server really provides a dedicated command DBCC to meet you, you may ask SQL Server to provide IND command parameters? I'm telling you, there is.

, but this is going to turn on 2588 tracking, just like the following.

<2>:pagefid,pagepid,iamfid

I just said, there are many kinds of data pages, the default is the table data page, in fact, there are IAM data pages, nothing strange, IAM is used to track table data pages, so the above figure, Iamfid field is a null record is an IAM page, the following pagepid=78, is the table data page.

2. View data page

To avoid confusion, I would like to say that the internal structure of the data page is probably what it is, so that everyone has an overall impression.

As you can see from the picture, there are a lot of slots in the tail of the data page, which points to the address of the actual record in the section of the dataset, so the table scan is actually sweeping these slot slots,

Or take the three records in the person table above, they are all stored in the number 78th data page, now out of curiosity to export the number 78th data page, say dry ... It's simple, you need

Two things to do:

<1> open 3604 Tracking: DBCC traceon (3604)

<2> Use the DBCC PAGE command to export the number 78th data page (PAGEFID:PAGEPID) below the 1th file (1:78), just like the following ...

Data page Header (page header):

Content (Page data):

Data slot bit (Page Slot):

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

Well, that's it, no more down, no more, no more, just a wash and sleep.