SQL Server Introduction Order: Level Nineth, read execution plan

Original address:

Stairway to SQL Server Indexes:level 9,reading Query Plans

This article is part of the SQL Server cable Introduction order series (Stairway to SQL Server Indexes).

In this series, we often execute specific queries in a specific way. We cite the generated execution plan to support our argument. SQL Server Manager displays the estimated and actual query plans that can help us determine the benefits of the index, as well as the flaws in it. Therefore, the main purpose of this article is to give you a good understanding of the implementation plan:

• Verify the assertions that you read in the series.
• Determine if your index is good for your query.

There are many articles on reading the execution plan, including many on MSDN. Our purpose today is not to extend them, nor to replace them. In fact, many of us can link to their references. Displaying Graphical execution plans (SQL Server Management Studio) is a good introductory article, and another helpful resource is Grant Fritchey's book SQL Server Execution plans ", this book has a free pdf download. There are some articles of Fabiano Amorim http://www.simple-talk.com/author/fabiano-amorim/.

Graphical execution Plan

An execution plan is a series of instructions for SQL Server based on a query. SQL Server Manager can display execution plans in the form of text, graphics, or XML. Take a look at the following simple query.

SELECT from WHERE = ' Jr. ' ORDER   by

The execution plan for the above query is as follows.

The text display is.

|--sort (ORDER by: ([adventureworks].[ Person]. [Contact]. [Title] ASC))

|--clustered Index
Scan (OBJECT: ([adventureworks].[ Person]. [Contact]. [Pk_contact_contactid]),
WHERE: ([AdventureWorks]. [Person]. [Contact]. [Suffix]=n ' Jr. ')]

In the XML display is.

View execution plans in various formats:

• View a graphical execution plan. In the SQL Server Manager toolbar, there are two buttons: "Show estimated execution plan" and "include Actual execution plan". The show estimated execution plan option is used to immediately display the graphical execution plan of the TSQL statement without executing the query. Include actual execution plan displays the actual execution plan in the new tab after you execute the query.
• View the execution plan for the text. Use SET SHOWPLAN_TEXT on. When you open the text display, the graphical display is turned off and your query is not executed.
• View the execution plan in XML format. Right-click in the graph's execution plan and choose Show Query plan XML from the context menu. (This menu is not seen in the Chinese version of SQL Server 2005 that I installed, as you can see in the SQL Server 2008 Manager.) ）

This paper is based on the graphical execution plan, which can quickly understand the execution plan. For the execution plan, a picture is better than 1000 words.

Read the graphical execution plan

Graphical execution plan, usually read from right to anticipatory, the rightmost icon is the first step to get the data. Normally, a heap or index is accessed. You don't see the word "table" here, instead, you see "Clustered Index Scan" and "Heap scan". Each icon in the graphics execution plan represents an action. For more information about icons, see graphical execution Plan Icons.

The arrow for the connection icon represents the number of rows for an action to the next operation.

Put your mouse on an icon or an arrow to display detailed information.

Do not think of an operation as a step, meaning that the previous operation must be completed before the next operation can begin. There are exceptions. For example, when the WHERE clause is evaluated, a filtering operation is performed, one row at a time, not a one-off evaluation. When the next line is filtered, the previous line has entered the next operation. On the other hand, the sort operation must be completed before entering the next operation.

Use some additional information

The graphical execution plan removes information that shows the plan itself, and shows two helpful information: the recommended index and the relative consumption of each operation.

In the above example, the green display is the recommended index (green information in the manager of SQL Server 2008, which is the recommended index), it is recommended to establish a nonclustered index in the suffix column of the Contact table, and contains the title, FirstName, MiddleName and LastName columns.

The related consumption shows that the sorting takes up 5% of the overall consumption, and the table scan takes up 95% of the work. So, if we want to improve query performance, we keep the table scan and get rid of the sort, which is why we are prompted to index. If we create the recommended index.

CREATE nonclustered INDEX  on

Execute the query again, the logical read from 569 to 3 times, the following is the new execution plan.

In the new nonclustered index, suffix is the index key, where stuffix= ' Jr. ' records are aggregated, thus reducing the number of reads required to get the data. The sort operation, which takes up more than 75% of the consumption, is not 5% previously seen. As a result, the original plan required 75/5=15 times

Because our WHERE clause is just an equal operation, we can improve our index by adding the title column to the index key as follows.

IF EXISTS(SELECT *  fromsys.indexesWHERE object_id = object_id(N'Person.Contact')   andName=N'Ix_suffix') DROP INDEXIx_suffix onPerson.ContactCREATE nonclustered INDEXIx_suffix onperson.contact (Suffix, Title) INCLUDE (FirstName, MiddleName, LastName)

Now, the information you need is still coming together, and the new execution plan looks like this.

The schedule shows that the sort operation is gone.

To view parallel data streams

If two data streams can be processed in parallel, they will appear in the graph's execution plan as a last-minute form. The width of the arrows indicates the number of rows per data stream processed.

For example, the following query expands the previous query while querying some sales information.

 SELECT from JOIN on = WHERE = ' Jr. ' ORDER  by

The execution plan looks like the following.

The above execution plan tells us something:

• Both tables are scanned at the same time.
• Most of the work is spent on table scans.
• Most of the data comes from the SalesOrderHeader table.
• The order in which the two tables are clustered is not the same, so each row in the SalesOrderHeader satisfies the condition, and the Contact table requires additional work. In this case, a hash match operation is required.
• The need for heap sequencing is negligible.

Each data stream can be divided into smaller data streams to be processed in parallel. For example, we will change the where in the above query to where suffix is NULL.

More rows are returned because the suffix column of 95% of the data row in the Contact table is null. The new execution plan is shown below.

The new execution plan tells us that increasing the contact line leads to matching and sorting operations that are key to this query. If we need to improve performance, let's start with these two operations. The index with the included column helped us once again.

As with many connection queries, our example connects two tables through the primary foreign key. The Contact table is sorted by ContactID and is also the primary key for the table. SalesOrderHeader table, ContactID is a foreign key. Because it is a foreign key, accessing the data of the SalesOrderHeader table through ContactID is common in business requirements. Indexing on ContactID can be a great help.

When you create an index on a foreign key, you often ask yourself if you need to add some columns as the included column of the index. In our case, it's just a query that doesn't need to support a lot of queries. Therefore, we only include the OrderDate column. The SalesOrderHeader table supports ContactID-centric queries and, if needed, contains more columns in the index.

The statement that creates the index is as follows.

CREATE nonclustered INDEX  on

The new execution plan is shown below.

Because all input streams are sorted by ContactID, with no grouping and hash matching, the workload is reduced from 26+5+3=34% to 4%.

Sorting, pre-ordering, and hash matching

Many operations want the data to be well-divided before the operation. This type of operation includes: Distinct,union,group by and join. Normally, SQL Server will use one of the following three methods to complete the grouping:

• It is gratifying to find that the data has been pre-sorted into the grouping sequence.
• The data is grouped by a hash match operation.
• Sorts the data in the sequence that is about to be grouped.

Pre-order

An index is the way you pre-sort your data, giving SQL Server The order it typically needs. This is why creating a nonclustered index with a containing column facilitates the query. In fact, if you put your mouse on the "merge Connection" icon of the graphical execution plan, you will receive a "match from two input tables that have already been sorted, using their sort order." This tells us that the two tables/indexes use the minimum memory and processor time when connecting.

Hash Match

If the input data is not in the order you want, SQL Server may be grouped with a hash match operation. Hash matching is a technique that consumes a lot of memory, but is more efficient than sorting. When performing distinct,union and join operations, the hash match has an advantage over the ordering because the row is processed to the next operation without waiting for all rows to be hashed. However, when grouping aggregations are computed, it is necessary to read all rows before entering the next phase.

The amount of memory required for hash matching is directly related to the number of packets produced.

SELECT COUNT (*fromGROUP by

The above packet consumes less memory because there are only two groupings: female and male. is not related to the number of rows in the input data.

SELECT COUNT (*fromGROUP by

This grouping takes up a lot of memory because it produces a large number of groups. Such a large amount of memory consumption, resulting in hash matching at the time of the query becomes an unwelcome technology.

Sort

SQL Server sorts the data if the data is not ordered (no indexes), and SQL Server considers the hash match not to be efficient. Normally, this is probably the last thing you want to see. So, if the sort icon appears early in the execution plan, check to see if you can improve your index. If the sort icon appears late in the execution plan, it most likely means that SQL Server sorts the final output result because of the ORDER BY clause in the request, which differs from the order in Join,group by and union. This time the sort is probably no way to avoid.

Conclusion

The execution plan tells you that SQL Server executes the query, is going to be used, and has used the method. The information is executed in parallel by displaying the details of each operation, from one operation to another, to the data flow of the operation.

• You can query this execution plan in text, graphics, or XML format (available in SQL Server 2008 manager).
• The graphical execution plan shows the amount of work related to each operation.
• A graphical execution plan gives the recommended index, which can improve the performance of the query. (Available in SQL Server 2008 manager)
• Understanding the execution plan will help you evaluate and optimize the index of your design.

SQL Server Introduction Order: Level Nineth, read execution plan