Getting Started with the execution engine in SQL Server

Brief introduction

When the query optimizer (queries Optimizer) parses a T-SQL statement and selects the least-consumption execution plan from the execution plan, the specific execution is performed by the execution engine (Execution Engine). This article is intended to categorize information about each of the operations in the execution plan.

Data access Operations

First, the most basic operation is to access the data. This can be done either by directly accessing the table or by accessing the index. The data in the table is organized into heaps (HEAP) and B-trees, when there is no clustered index in the table, the data is organized by the heap, which is unordered, and the data in the table after the clustered index and the nonclustered index are organized in B-tree mode, which is stored sequentially. Typically, a nonclustered index contains only a subset of the entire table, and for a filtered index, only some of the rows are included.

Except that the data is organized differently, access data is divided into two ways, scanning (Scan) and lookup (seek), scanning is all the data that scans the entire structure, and the lookup simply looks for some data in the entire structure. It can be seen, therefore, that because the heap is unordered, it is impossible to find (seek) operations on the heap, and that it is possible to find it in the B-tree relative to the order of the B-tree. Heap scans are performed when data is accessed against a table that is organized as a heap, as shown in Figure 1.

Figure 1. Table Scan

As you can see, the table scan icon clearly indicates the nature of the table scan, scanning through the table in an unordered organization.

For both clustered and nonclustered indexes of the B-tree structure, it is also possible to scan, usually by a clustered index scan in order to get all the data in the index table or to get the index row tree to occupy most of the data so that the cost of the scan is less than the lookup. As shown in Figure 2.

Figure 2. Clustered Index Scan

The icon of the clustered index Scan can also clearly indicate the nature of the clustered index scan, and after finding the leftmost leaf node, scan all leaf nodes sequentially to scan the entire structure. Of course, the same concept is true for nonclustered indexes, as shown in Figure 3.

Fig. 3. Scanning of nonclustered indexes

For only some of the data in the B-tree structure, index lookup (seek) makes the B-tree meaningful. Depending on the key value you are looking for, you can make a single path down from just the root of B, thus eliminating the need for scanning unnecessary pages, Figure 4 is an index lookup in the query plan.

Figure 4. Clustered Index Lookup

Index lookup icon is also very vivid, you can see the icon that line from the root node down to the leaf node. Which is to find the page where the data is being sought, it's not hard to see that if we need to look up multiple data and spread it across different pages, the lookup operation needs to be repeated many times, and when the number is large to a certain extent, SQL Server chooses to consume a lower index scan rather than repeat the index lookup. For nonclustered index lookup, the concept is the same, no longer on the picture.

Bookmark Lookup (Bookmark lookup)

You might think that a nonclustered index can quickly find the data, but unfortunately, nonclustered indexes do not contain the required columns. SQL Server then faces two choices, directly accessing the base table to fetch data or finding the data in the nonclustered index, and then the base table to obtain the columns that are not covered by the nonclustered index. This choice depends on statistics such as the estimated number of rows. The Query Analyzer chooses the one that consumes less.

A simple bookmark lookup is shown in Figure 5.

Figure 5. A Simple bookmark lookup