Translation (11)--reading Query plans:stairway to SQL Server Indexes level 9

Reading Query plans:stairway to SQL Server Indexes level 9

by David Durant, 2011/10/05

Original link: http://www.sqlservercentral.com/articles/Stairway+Series/72441/

The Series

This article is part of the ladder series: SQL Server The ladder of the index.

Indexes are the basis of database design and tell developers to use a large number of databases about the designer's intentions. Unfortunately, when performance issues arise, indexes are often added as an afterthought. This concludes with a series of simple articles that should allow database professionals to synchronize with them quickly.

Throughout this step, we often declare that a query executes in some way; we refer to the generated query plan to support our statements. The management library displays estimates and actual query plans that can help you determine the benefits or deficiencies of the index. Therefore, the purpose of this level is to give you sufficient insight into the query plan:

When you read this ladder, verify our assertions.

Determines whether the index is beneficial to the query.

There are many articles that read the query plan, including MSDN Library a few in the. We do not intend to extend or replace them. In fact, we will provide links/references for many of them at this level . A good place to start is to display a graphical execution plan. (http://msdn.microsoft.com/en-us/library/ms178071.aspx). Other useful resources include the grant Fritchey book, the SQL Execution plan (available for ebook free), and the Fabiano Amorim series of simple dialogues about the output of each operator discovering your query plan (http:// www.simple-talk.com/author/fabiano-amorim/).

Graphical Query Plans

A query plan is A set of instructions that SQL Server follows when it executes a query. SQL Server will display your query plan in text, graphics, or XML format. For example, consider the following simple query:

SELECT LastName, FirstName, MiddleName, title& #160;

From Person.Contact

WHERE Suffix = ' Jr. '

ORDER by Title

The plan for this query can be 1 is shown.

Figure 1- actual query plan in graphical format

Or, you can treat it as text:

|--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. ')]

or as a XML document, starting from this:

The display of the query plan can be as follows:

to request a graphical query plan, use the management room SQL the Editor toolbar, which has both the show estimate execution plan and the include actual execution plan buttons. The show estimated Execution plan option displays the query plan graph immediately for the selected TSQL code without executing the query. The Include actual execution plan button is a switch, and once you select this option, each batch of queries you perform will display the query plan graph, along with the results and messages, in a new tab. This option can be seen in Figure 1 .

require text query plan, using settings Showplan_text declaration. Opening a text version closes the graphical version and does not execute any queries.

to view XML version, right-click in the graphics version and select Show Execution Plan from the context menu XML ".

For the rest of this level, we focus on the graphical view as it usually provides the fastest understanding of the plan. For a query plan, a picture is usually better than 1000 words.

Reading Graphical Query Plans

Graphical query plans are usually read from right to left, and the right-most icon represents the first step in the data collection stream. This is usually the access heap or index. You won't see the Word table used here; instead, you'll see a clustered index scan or a heap scan. This is the first place to see which indexes, if any, are being used.

each icon in the graphical query plan represents an action. For more information on the possible icons, see Graphics Execution plan in the http://msdn.microsoft.com/en-us/library/ms175913.aspx icon

The arrow of the JOIN operation represents the row, which flows out from one operation and into the next operation.

Placing the mouse over an icon or arrow causes more information to be displayed.

do not think of the operation as a step because it means that an action must be completed before the next operation begins. This is not necessarily true. For example, when a WHERE clause is evaluated, that is , when a filter action is performed, a row is evaluated at a time, rather than a single evaluation. A row can be moved to the next action before the subsequent row reaches the filter action. On the other hand, the sort operation must complete before the first row is moved to the next operation.

Using Some Additional Information

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

in the above example, the proposed indicator, which shows the green and space requirements truncated, suggests a non-clustered index of the suffix column of the Contact table, including the title, FirstName , MiddleName column, and last name.

The relative cost of each operation of this plan tells us that the sort operation accounts for the total cost of 5% While the table scan is working 95% . So if we want to improve the performance of this query, we should deal with table scans instead of sorting, which is why the index is recommended. If we create a referral index, like this:

CREATE nonclustered INDEX ix_suffix on Person.Contact (Suffix) INCLUDE (Title, FirstName, MiddleName, LastName)

then rerun the query, and we read the volume from 569 descend to 3 ; The new query plan is as follows.

The new nonclustered index, with its index key suffix, has the suffix = " Jr. "The entries are gathered together; IO you need to retrieve a data restore. Therefore, the sort operation is the same as the sort operation in the previous plan, and now represents the 75% of the total cost of the query, rather than the original 5%. As a result, the original plan needs to be 75/5 times as much as the current schedule for collecting the same information .

because of our WHERE clause contains only one equality operator, so we can improve the index by moving the header column to the index key:

if&#; EXISTS (SELECT *= object_id (n'person.contact'= n'ix_ Suffix') DROP index ix_suffix on person.contactcreate nonclustered index ix_suffix on person.contact ( Suffix, Title) INCLUDE (FirstName, MiddleName, LastName)

now, the required entries are still clustered together in the index and in each cluster in the requested sequence, as shown in the new query plan. 2 is shown.

Figure 2 after the query plan rebuilds the nonclustered index

The plan now indicates that the sort operation is no longer required. At this point, we can abandon the high-yield coverage index. This will restore the Contact table to the state we started at; This is what we want it to be when we move on to the next topic.

viewing Parallel Streams

If two rows of rows can be processed in parallel, they will appear in the graphical display, both up and down. The relative width of the arrows indicates the number of rows processed by each stream.

For example, the following connection expands the previous query to include sales information:

='Jr.'ORDER by Title

query plan 3 is shown.

Figure 3- Connected query plan

A quick look at the plan will tell us something:

Two tables are scanned at the same time.

Most of the work is spent on scanning the table.

multiple rows out or SalesOrderHeader table exceeds the Contact table.

The two tables are not clustered into the same sequence; SalesOrderHeader contact with line matching will require additional effort. In this case, the hash match operation is used. (Hash is discussed later).

The effort required to sort the selected rows is negligible.

even individual row rows can be decomposed into separate rows to take advantage of parallel processing. For example, if we change the where clause in the above query to a place with a suffix of null .

will return more rows, because 95% 's contact line has an empty suffix. The new query plan reflects this, as shown in4 .

Figure 4- a parallel query plan

The new plan also shows us that an increase in the number of contact rows causes the match and sort operations to become critical paths to the query. If we need to improve its performance, we must first attack both of these operations. Similarly, the index containing the column will help.

Like most connections, our example uses a foreign key / A primary key relationship connects two tables. One of the tables, linked, is first made by ContactID, which is also precisely its main key. In other tables,saleorderheader ContactID is a foreign key. Since ContactID is a foreign key, the saleorderheader ContactID request for data access , such as the example I joined, may be a common business requirement. These requests will ContactID benefit from the index .

whenever you index a foreign key column, you always ask yourself which indexes should be added if the column contains columns. In our case, we have only one query, not a family that supports queries. Therefore, we only include the OrderDate column. Support a family of ContactID directed to the saleorderheader Table query, we will include in the index, more columns Saleorderheader needs to support those additional queries.

Our CREATE INDEX statement is:

CREATE nonclustered INDEX Ix_contactid on Sales.SalesOrderHeader (ContactID) INCLUDE (OrderDate)

and the implementation of the new plan our SalesOrderHeader and contact information to join 5 .

Figure 5- plan to have a connection query that supports indexes on each table

since the input stream is now connected by a predicate, ContactID , the query of the part of the sequencing can be done without splitting the stream without hashing, thus reducing + 5 + 3 = 34% under the workload 4% what the workload is.

Sorting, presorting and Hashing

Many query operations require that data be grouped before the operation is performed. These include explicit, federated (which means different), by group (and its various aggregate functions), and join. Typically,SQL Server uses one of three methods to implement this grouping, and the first method requires your help:

It is gratifying to find that data has been preset as a grouping sequence.

Groups data by performing a hash operation.

Sorts the data into a grouping sequence.

Presorting

The indicator is your pre-existing data, which is often required to SQL Server the supplied data series. That's why nonclustered indexes are created, each containing included columns, thanks to our previous example. In fact, if you place the mouse over the merge connection icon in the most recent query, the phrase will match the rows with two appropriately sorted input streams, taking advantage of their sort order. will appear. This tells you that the two table / index rows are connected using the absolute minimum value of memory and processor time. The corresponding sort of input is a wonderful phrase to see when the mouse queries the plan icon, which validates the indicator of your choice.

Hashing

If the input data is not an ideal sequence, SQL Server You can use hash operations to group data. Hashing is a technique that can use a lot of memory, but is usually more efficient than sorting. When performing a different, federated, join action, hash in a single row can be done by the next operation without waiting for all input rows to be hashed by the advantage. However, when grouping aggregation is calculated, all input rows must be read before all aggregated values are passed to the next operation.

The amount of memory required to hash information is directly related to the number of groups required. So you need to parse the hash:

SELECT Gender, COUNT (*) from Newyorkcitycensusgroup by Gender only requires very little memory because there are only two groups: female and male, regardless of the number of input lines. On the other hand: SELECT LastName, FirstName, COUNT (*) from Newyorkcitycensusgroup by LastName, FirstName

will result in a large number of groups, each of which needs to have its own space in memory, may consume too much memory, and hashing becomes an undesirable technique for resolving queries.

more query plan hashes, Access http://msdn.microsoft.com/en-us/library/ms189582.aspx .

Sorting

If the data does not have a preset (index), if SQL Server that the hash cannot be done efficiently, SQL Server sort the data. This is usually the least desirable option. Therefore, if the sort icon appears earlier in the plan, check to see if the index can be improved. If sorticon appears at the end of the plan, this may mean that SQL Server 's ORDER by The final output of the ordering of the request sequence of the clause, which is used to resolve the query's connection, grouping, and union. Usually, there's something you can do to avoid this sort.

Conclusion

Query plan display SQL Server the method that you intend to use or use to execute the query. It details the operation to be used, the flow from the operation to the operation, and the parallelism involved.

you treat this information as text, graphics, or XML display.

The diagram shows the relative workload for each operation.

A graphical plan might provide an index to improve the performance of the query.

Understanding the query plan helps evaluate and optimize the index design.

Translation (11)--reading Query plans:stairway to SQL Server Indexes level 9