Read the query plan: Stair SQL Server Index Level 9

through David Durante , 2011/10/05

the series

This article is part of the stair series: Ladder index for SQL Server

The basis of the index database design tells the developer to use the intent of the Database Designer. Unfortunately, the index is often followed by a performance problem that arises. Finally here is a simple series of articles that should allow any database professional to quickly "accelerate"

Throughout the staircase, we often say that the query must be executed in some way; We refer to the generated query plan to support our claims. Management Studio estimates and actual query plans can help you determine the benefits, or lack, of your index. Therefore, the purpose of this level is to give you enough understanding of the query plan that you can:

• Verify our assertion that you read this staircase.
• Determine the benefits of your index query.

There are many articles that read the query plan, including some in the MSDN library. This is not our intention to extend or replace them. In fact, we will provide links/references to many of them at this level. A good starting point is to display a graphical execution plan (http://msdn.microsoft.com/en-us/library/ms178071.aspx). Other useful resources include the grant Fritchey book,SQL Server execution plan (free ebook form), and The various operator query plan outputs in the Simple-talk article of the pellet-in Amorim series ( http://www.simple-talk.com/author/fabiano-amorim/).

graphical query plan

Query the scheduled instruction set for SQL Server before executing a query. SQL Server Management Studio will display your query plan text, graphics, or XML format. For example, consider the following simple query:

Choose Last Name ,FirstName,middlename, title &#;  From person.contact where suffix ="small". Command title                 

The plan for this query may be considered as 1.

Or, it may be treated as text:

|--Sort by: [AdventureWorks]. [People]. (Contact). Title ASC))

|--Clustered Index
Scan (object: [adventureworks],[person]. [Contact]. [Pk_contact_contactid]),
Location: ([adventureworks],[people]. (Contact). (suffix) = N ' Jr. ‘))

Or as an XML document, the beginning is this:

The requirements for displaying the query plan are as follows:

• Requires a graphical query plan, using the SQL Editor toolbar of the Administration studio, the show estimated execution plan and the include actual execution plan buttons. The "estimate Show execution plan" option displays the query plan diagram that is selected immediately by the TSQL code, without executing the query. The "Include Actual execution Plan" button is the switch, and once you select this option, the bulk execution of each query will show you the query plan graph, results, and messages in the New tab page. This option can be seen in 1.
• Request a text query plan, using the SET SHOWPLAN_TEXT declaration. The text version will close the graphical version and no queries will be executed.
• To view the XML version, right-click the graphics version and choose Show Execution Plan XML from the context menu.

In the remainder of this level, we focus on the graphical view, which usually provides the quickest plan for understanding. For a query plan, one is usually more than 1000 words.

read the graphical query plan

graphical query plans usually read from right to left; the correct majority of icons (s) represent the first step in the data acquisition process. This is usually a heap or index of access. You will not see this word table used here; instead, you will see a clustered index scan or a heap scan . This is the first place to look at the index, if any.

each icon represents a graphical query plan Operation . Additional information on the possible icons, see graphic icon execution Plan http://msdn.microsoft.com/en-us/library/ms175913.aspx

The Arrow connection action represents the line that flows from one operation.

Placing the mouse over an icon or an arrow will cause additional information to be displayed.

do not think of an action step, which means that an operation must complete the next operation before it can begin. This is not necessarily true. For example, when a WHERE clause evaluates, that is, when a filter operation is performed, the line evaluates once; A row can be moved to the next action before the subsequent line arrives before the filter operation. A sort operation, on the other hand, must be completed before the first row can be moved to the next operation.

use some additional information

A graphical query plan shows two potentially useful information, not part of the plan itself; The recommended index and the relative cost of each operation.

In the example above, the recommended index, To display and truncate space requirements in green, it is recommended that the nonclustered index contact suffix caption for the column FirstName , middlename , Last name

Relative cost per operation This plan tells us that this surgery is 5% of the total cost, while 95% of the worksheets are scanned. So if we want to improve the performance of this query, we should fix the table scan, not the kind; That's why an index is recommended. If we create the recommended index, as follows:

Create nonclustered index ix_suffix in person.contact( suffix ) including ( title ,FirstName,  MiddleName, surname )              

Then rerun the query, we read from 569 down to 3; And the new query plan, as shown below, shows why.

index key for new nonclustered index suffix, "small suffix = '." "The articles are gathered together; Therefore, reducing the body needs to retrieve data. So, the sort operation, the same operation, which is in front of the plan, now represents more than 75% of the total cost of the query, rather than just 5% of the cost. So the required amount of work is 75/5 = 15 times times as the current schedule collects the same information.

Because our WHERE clause contains only one equality operator, we can improve our exponent by moving the Header column index key as follows:

The plan now shows that the operation is no longer needed. At this point, we can put a highly beneficial coverage index. This recovery contact form is when we start; This is the state we want when we go to the next topic.

View Parallel streams

If the rows of two streams can be processed in parallel, they appear graphically above and below each other. The arrows indicate the relative width of how many rows are processed through each stream.

For example, after joining, the previous query expands to include sales information:

A quick look at the plan tells us a few things:

• Two table scans at the same time.
• Most of the work is on the scan table.
• more rows or SalesOrderHeader than the table Contact table.
• These two tables are not clustered into the same sequence; SalesOrderHeader all right. Contact additional effort is required. In this case, the hash Match operation is used. (The hash will be described in more detail later.)
• The required effort is negligible for the selected row.

Even individual flow can be broken down into separate streams with fewer lines each utilizing parallel processing. For example, if we change the WHERE clause of the above query to the suffix is null.

will return multiple lines, with more and more 95% Contact Line has a 0 suffix. This is reflected in the new query plan, shown in Figure 4

The new plan also shows us that the increase in quantity Contact row causes the match and sort operation to become the critical path for this query. If we need to improve their performance, we must first attack these two operations. Once again, it is helpful to include indexed columns.

Like most connections, the relationship of two table primary keys is connected through a foreign key/our example. One of the tables,Contact, sequencingContactID, which happens to be its primary key. In another table,Saleorderheader,ContactIDis a foreign key. FromContactIDis a foreign key that requestsSaleorderheaderData AccessContactID, such as the example we have added, may be a common business requirement. These requests will benefit from an indexContactID.

whenever you index a foreign key column, always ask yourself what, if any, you should add columns, including the index of the column. In our case, we have only one query, not a family-supported query. Therefore, we will only include columns to the database . Support a family ContactID query-oriented saleorderheader table, we will include more Saleorderheader the columns in the index, supporting those additional queries as needed.

sorting, pre-categorization, and hashing

Many query operations require actions that can be performed before data is grouped. Include different, federated (which means different), organization (and its various aggregation functions), and join. Typically, SQL Server uses three methods to implement this grouping, and the first one requires your assistance:

• Happy to find data series that have been pre-categorized by the component group.
• by executing a set of data Hash operation.
• Sorts the order in which the data is grouped.

Pre-classification

The index of the pre-categorized data is your way, that is, a frequent sequence of SQL Server that provides the required data. This is why nonclustered indexes are created, each containing included columns, which we benefit from earlier examples. In fact, if you put your mouse merge connection icon in the nearest query, this phrase matches the line from two appropriate sort input streams, using their sort order . will appear. This tells you that the row/index of both tables uses the absolute minimum memory and processor time. the appropriate sort of input is a wonderful phrase to see the icon when the mouse moves to the query plan to verify the index of your choice.

Hash

If the incoming data is an undesirable sequence, the SQL Server may use a hash operation to group the data. Hashing is a technique that can use a lot of memory, but is often more efficient than sorting. When performing different federation, connection operations, and hash advantage sorting, individual rows can be passed to the next operation without having to wait for all incoming row hashes. However, any aggregation value that must be read before the calculated grouping aggregates all input rows can be passed to the next operation.

Sort

If the data is not pre-categorized (indexed), SQL Server sorts the data if SQL Server considers the hash not to be effective. This is usually the ideal choice. So if a sort icon appears early in the plan, see if you can improve your index. If The icon for the sort plan appears near the end, this may mean that SQL Server sorts the final output into the request sequence of the ORDER BY clause, which is different from the connection, group, and union used to resolve the query. Usually, you can avoid this.

Conclusion

The query plan shows how SQL Server methods are intended to be used, or used to execute a query. It is done through detailed operations that will be used, the operation of the line, and the parallelism involved.

• You display this information as text, graphics, or XML.
• The graph shows the relative workload schedule for each operation.
• A graphical plan might suggest that indexing can improve query performance.
• Understanding the query plan will help you evaluate and optimize your index design.

Read the query plan: Stair SQL Server Index Level 9