In-depth understanding of table scanning in SQL Server and SQL

In-depth understanding of table scanning in SQL Server and SQL

A long time ago when we were writing SQL, the most feared thing was that SQL was inexplicably super slow. The slow thing was that the ball was still turning back... It was only the client who was anxious to understand this. Later I heard of an "evaluation Execution Plan", and later I realized that table scanning should be avoided...

I. Table Scan

1. symptom

"Table scan" sounds very simple. It's not just a row or a row scan. If you want to say "Execution Plan", I will also play. To make it more impressive, I build a table, insert three rows of data, for example:

 

I have not created any index for the Person above. Then, let's look at where to see what table scanning looks like ???

 

 

We can see the word "Table scan". Since it is an evil thing, we must have a deep understanding of it. Then we can find a way to avoid it... Therefore, we must understand the essence. The question is, how is it scanned ??? How can this problem be solved? This must also start with the data page...

Ii. deep understanding of table Scanning

1: Data Page

I have no reason to say that I don't know. Our records are stored in the form of data pages, and we should also know that the size of the data page is 8 k .... Where is the data page? I can

Let you see the truth.

At first glance, I have painted a lot of pictures. Do not be afraid. Do not think that there are too many paintings, so I think they are advanced... I will analyze it briefly.

<1>: dbcc ind command

If you want to check the data page, sqlserver actually provides the dedicated Command dbcc to meet your requirements. May you ask sqlserver if there are parameters that provide the ind command? Let me tell you.

However, to enable the 2588 trail, as shown below.

<2>: PageFID, PagePID, IAMFID

As I mentioned earlier, there are many types of data pages. By default, we talk about table data pages. In fact, there are also IAM data pages, which are nothing special. IAM is used to track table data pages, in the above figure, the record with the IAMFID field being Null is the IAM page, and the following PagePID = 78 is the table data page.

 2. view the data page

To avoid confusion, let me first talk about the internal structure of the data page, so that everyone can have an overall impression.

We can see that there are many slots at the end of the Data page. These slots point to an actual recorded address in the Data area, it is actually scanning these Slot slots,

Take the three records in the Person table above for example, they are all stored on the 78 data page. Now, out of curiosity, we export the 78 data page. Just do it .... Simple. You need

There are two things to do:

<1> enable 3604 tracking: dbcc traceon (3604)

<2> use the dbcc page command to export the data page 78 (pageFID: pagePID) = (1: 78) under file 1, as shown below...

Page header ):

 

Data content (Page Data ):

 

Page Slot ):

 

 

Have you seen the three slots above (0, 1, 2) and all of them have corresponding offset addresses (0x7e, 0x92, 0xba ), this address points to the offset address actually recorded in the Data area.

Okay, so far. I can't talk about it any more. I have to wash and sleep.