Shulou(Shulou.com)06/01 Report--

Analyzing plans

Analysis plan

To really understand query plans and really find, fix, or solve problems with query plans, you need to have a solid understanding of the query operations that make up these plans. Overall, there are too many operators who can discuss them in one chapter. In addition, these operations can be matched and incorporated into the query plan in countless ways. Therefore, this section focuses on understanding the most common query operators-the most basic building blocks of query execution, and provides some insight into how SQL server uses them to build various interesting query plans. Specifically, this section looks at scans and finds, joins, aggregates, unions, selects subquery plans, and parallelism. After understanding how these basic operations and plans work, you can break down and understand larger and more complex query plans as much as possible.

Scans and Seeks

Scan and find

Scans and lookups are iterators that SQL server uses to read data from tables and indexes. These iterators are the most basic ones supported by SQL server. They appear in almost every query plan. It is important to understand the difference between scanning and lookup: scans process the entire leaf level of an entire table or an index, while lookups effectively return rows from one or more ranges of the index based on predicates.

First of all, take a look at the example of scanning. Please consider the following query:

SELECT [OrderId] FROM [Orders] WHERE [RequiredDate] = '1998-03-26'

The RequiredDate column does not have an index. Therefore, SQL server must read each row of the Orders table. The predicate that calculates the RequiredDate of each row; if the predicate is true (that is, if the row is eligible), the row is returned.

To maximize performance, SQL server calculates predicates in the scan iterator whenever possible. However, if the predicate is too complex or too expensive (lossy performance), SQL server may evaluate it in a separate filter iteration. Predicates appear in the text plan as WHERE keywords or tags in the XML plan.

The following is the text plan for the previous query:

|-- Clustered Index Scan (OBJECT: ([Orders]. [PK_Orders])

WHERE: ([Orders]. [RequiredDate] = '1998-03-26'))

Figure 10-6 takes scanning as an example.

Because the scan touches whether each row in the table meets the criteria, the cost is proportional to the total number of rows in the table. Therefore, scanning is an effective strategy if the table is small or many rows meet the conditions of the predicate. However, if the table is large and most of the rows do not meet the criteria, the scan touches more pages and rows and performs more unnecessary iUnio

Now let's look at an example of an index lookup. Suppose we have a similar query, but this time the predicate has an index on the OrderDate column:

SELECT [OrderId] FROM [Orders] WHERE [OrderDate] = '1998-02-26'

This time, SQL server can use the index to navigate directly to the row that matches the predicate. In this case, the predicate is called the lookup predicate. In most cases, SQL server does not need to explicitly evaluate the lookup predicate, and the index ensures that the lookup operation returns only rows that meet the criteria.

The lookup predicate appears in the text plan as the SEEK keyword or the tag in the XML plan.

The following is the text plan for this example:

|-- Index Seek (OBJECT: ([Orders]. [OrderDate])

SEEK: ([Orders]. [OrderDate] = CONVERT_IMPLICIT (datetime, [@ 1], 0)) ORDERED FORWARD)

SQL server replaces the parameter @ 1 with the date format by automatically parameterizing the query method.

Figure 10-7 an example of an index lookup.

Because the lookup only touches eligible rows and pages that contain these qualified rows, the cost is proportional to the number of qualified rows and pages, not to the total number of rows in the table. Therefore, when using highly selective lookup predicates, lookup is usually a more effective strategy, that is, if you look up a large part of the predicate exclusion table.

SQL server distinguishes between scans and lookups and scans on the heap (tables without clustered indexes), scans of clustered indexes, and scans of nonclustered indexes.

Table 10-2 display of all valid combinations in the plan output.

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.