Typically when data is queried, the database will utilize indexes or find the data through a full table scan. But if you need the data stored in the database is not continuous or need to find more records, then the effect of the index will be greatly reduced. In this case, the database query optimizer may use a full table scan instead of an index. However, it is well known that the efficiency of full table scan is relatively low. For this reason in the Enterprise Edition of the SQL Server database, an advanced scan is proposed to handle it. Simply put, advanced scan allows multiple query tasks to *** enjoy full table scan. I will give you an example first, and then talk to you about the secrets hidden behind it.
As in the above figure, there are more records in a table with 40,000 pages. User A needs to query the records in this table, assuming that it uses a full table scan. When the database query reaches 20000 pages, user B also needs the data in this table, so another full table scan is triggered. At this point, if the advanced scanning technique is not used, then user B's SQL statement must wait until user A's execution is complete before it will be executed. If advanced scanning techniques are used, then the database will split the results of the full table scan from page 20,000 into two copies for user A and user B, respectively. Then when page 30,000 is reached, user C is also involved. Similarly the database engine will divide the result of the scan starting from page 30000 into three copies for each of the three users. When the entire table scan is complete, the database engine returns the results to user A. Then start scanning from the beginning again. When the scanning reaches 20000 pages, it will merge the results of the last scanning from 20000 pages to 400000 pages and then return to user B. When the scan reaches 300000 pages, it will merge the results with the last scan and return to user C.
As you can see, if you have different advanced scanning features, you may need to scan a table three times for different user queries at different times. In the above case, it is known that the table needs to be scanned less than two times. For this reason, advanced scanning tools can significantly improve database performance when multiple full table scans are performed on the same table.
Second, the secrets of advanced scanning implementation.
It can be seen that advanced scanning is mainly achieved by *** enjoying full table scanning technology. That is, when the SQL statement execution plan needs to scan the table data page (that is, full table scan), and the database engine detects other query execution plan is scanning the table (such as the above example in the user B, C to participate in), then the database engine will be in the second scan of the current position of the second scan is inserted into the first scan (at this time, the database engine will be the result of the scan). generates a copy of the scan). The database causes it to read one page at a time and pass each page's rows to multiple execution plans until the end of the current scan.
At this point, the first scan (user A) is completely finished, and the database engine passes the results of the scan to user A's process. However, at this point, Database B cannot return the results to User B, because in the middle of User A's query to User B's SQL statement, there may be a user who modifies the data on the previous pages. For this reason, the database engine needs to rescan the previous pages to prevent misinterpretation of the data. For this reason the second query plan must initiate a second full table scan to retrieve the pages of data read before the second execution plan joins the first scan in progress. That is, the second execution plan's scan will wrap back around to the first data page and scan from there until it joins where it was at the time of the first scan. The database engine will then return the scanned results to the second query plan, and so on. In practice, any number of scans can be combined in this way. In fact, this kind of scanning is very much like a walk-through, for which we also jokingly refer to advanced scans as full table scans. It can be seen that in this case, if multiple users query the same table during a full table scan, the number of full table scans can be reduced. In the absence of an advanced scan, users A, B, and C above would all have to contend for buffer space and therefore hard disk or memory contention, etc. The database engine would then perform the scan for each of these users separately. The database engine would then read the same page for each user in turn, rather than having multiple users ****ing around with the results of each read. Obviously, this is much less efficient than advanced scanning.
Third, the disadvantages of advanced scanning and the solution.
Although advanced scanning improves the query performance of the database, this processing mechanism also has the disadvantage of causing confusion in the order of the query result records. As in the example above, if all three users use the same query statement, the final results returned are the same, but the order of the records is different (assuming no sorting statement is used). This may give the user a misunderstanding that each of them to find different content. Why does this happen? In order to say we the cause of this problem, the author will simplify the contents of the table. Assuming that a table has three records, the serial number of 1, 2, 3.