SQL Server Execution Plan those things (2)--Find and scan

The next article is to record their own blind spots, but also reveal their own development process (may not be developed, can only be said to be blind). Of course, some blind spots are also in the process of work and exploration slowly some, now also willing to carry forward the dedication of the blog park, to come out and share with you.

Open the door and go straight into the question.

Have you ever had the same doubts as me when looking at the execution plan? Find and scan what exactly it is, and how they differ in query performance. Below to share my understanding.

Scanning and lookups are iterators used by SQL Server to read data from a table or index, and because they are often seen in the execution plan, understanding the differences between them is important to us in optimizing queries.

The table scan is done on the entire table, and the index scan is processed at the entire page level, but the index lookup is done on one or several pages on the lookup predicate, so he typically has less IO overhead for index lookups.

Because the scan is on a row or page in the entire table, regardless of whether the condition is met, one at a. Therefore, its query overhead is fixed, regardless of the return result set. Index lookups occur on several pages of the predicate, and typically, the more result set is returned, the greater the overhead.

However, through the above description, we can not think that the search is good, and the scan is bad (because of the impact of scanning performance and parallelism), but in most cases, especially the amount of data is relatively large, and the returned result set is relatively young, the search is better than the performance of the scan, In addition, not all scans can be optimized.

Below, we use examples to understand the difference in performance between query and scan.

We also use the example in SQL Server execution Plan (1) (delete the previously established index).

Let's do the following query

1. In the case of no index

Select ID, amount,discount,buydate from Headers

Select ID, amount,discount,buydate from Headers where buydate= ' 2008-09-15 '

Result: In the case of a table scan, although there is only one data in the result set, the IO overhead is not reduced, and it is still the same as returning all the result sets.

2. In an indexed (nonclustered index, to overwrite the return column, this will cause a RID lookup, or a key-value lookup, which will be described in a later article. ）

Create an index

Create nonclustered index index_headers_buydate on headers (buydate)
Include (ID, Amount,discount)
Go

Select ID, amount,discount,buydate from Headers

Select ID, amount,discount,buydate from Headers where buydate= ' 2008-09-15 '

Result: In the case of index scans and index lookups, the cost used is closely linked to the returned results

Summarize

1. When the data in the table is few or the rows that satisfy the predicate are relatively long, the scanning operation is more effective.

2. If the amount of data in the table is larger or less than the rows that satisfy the predicate, using a scan will read more pages or perform more I/O operations to get the data, which is not the most efficient method.

3. Lookup is not always good compared to scanning, and scanning is not bad because SQL Server may allocate multiple threads (degrees of parallelism) to the query.

SQL Server Execution Plan those things (2)--Find and scan