Shulou(Shulou.com)05/31 Report--

How to in-depth understanding of table scanning in Sql Server, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

A long time ago, when we were writing sql, one of the things we feared most was that sql was inexplicably super slow. The slow thing was to roll a tube back, and the ball was still spinning. This anxiety is understood only by the parties concerned. Later, I heard that there was an "evaluation implementation plan", and later I realized that table scanning should be avoided.

One: table scan

1. Phenomenon

"Table scan" sounds very simple. It's just a scan line by line. If you want to say "execute the plan", I will also play. In order to be more impressive, I build a table and insert three rows of data, as shown in the following figure:

I didn't build an index of the Person above, and then where it to see what the table scan looks like.

Sure enough, we saw the evil word "table scan". Since it is an evil thing, we must have a deep understanding of it before we can think of ways to avoid it. So we must understand the essence, then the question is, how on earth did it sweep? How do you break it? This also has to start with the data page.

Second, a deep understanding of table scanning

1: data page

This sqlserver student has no reason not to know that our records are stored in the form of data pages, and we should also know that the size of the data pages is 8k. Where is the data page? I can

To make you believe what you see.

At first glance, I have painted a lot, do not be afraid, do not think that too many paintings, think that the profound. Let me make a simple analysis.

: dbcc ind command

If you want to see the relevant information on the data page, sqlserver does provide a special command dbcc to satisfy you, you may ask sqlserver whether there are parameters to provide the ind command? I'm telling you, there is.

Yes, but this needs to turn on 2588 tracking, like the following.

: PageFID,PagePID,IAMFID

As I just said, there are many kinds of data pages. By default, there are table data pages. In fact, there are IAM data pages. It is not surprising that IAM is used to track table data pages. So in the above figure, the record with the IAMFID field Null is the IAM page, and the PagePID=78 below is the table data page.

two。 View the data page

In order to avoid confusion, let me first talk about what the internal structure of the data page looks like, so as to give you an overall impression.

As you can see from the figure, there are many slots at the end of the data page, which point to the actual recorded addresses in the Data area, so table scanning is actually scanning these Slot slots.

Or take the three records in the above Person table, they are all stored in data page 78, now out of curiosity to export data page 78, just do it. It's simple. You need

There are two things to do:

Enable 3604 tracking: dbcc traceon (3604)

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

Data header (PAGE HEADER):

Data content (Page Data):

Data slot (Page Slot):

Have you seen the three slots above, and all have corresponding offset addresses (0x7e 0x92recovery0xba). This address points to the offset address actually recorded in the Data area.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.