Read the query plan: SQL Server index Level 9

This article is "Stairway Series:Part of the ladder for SQL Server indexingIndexes are the basis of database design and tell developers to use the database with regard to the designer's intentions. Unfortunately, when performance problems arise, indexes are often added as an afterthought. The end here is a simple series of articles that should enable them to quickly make any database professional "fast"Throughout this phase, we often say that a query executes in some way, and we refer to the generated query plan to support our statement. The estimated and actual query plans displayed by Management Studio can help you determine the benefits or lack of an index. Therefore, the goal of this level is to give you a full understanding of the query plan, which you can:? When you read this ladder, verify our assertions.? Determine if your index is beneficial to your query.There are many articles about reading query plans, including some in the MSDN library. We are not going to expand or replace them here. In fact, we will provide many of these links at this levelReference Displays the graphical execution plan (http://msdn.microsoft.com/zh-cn/library/ms178071.aspx) is a good start. Other useful resources includeGrant Fritchey's book,SQL Server execution plan (available as a free ebook) andFabiano Amorim about the various operators found in the query plan outputSimple-talk Article series (http//www.simple-talk. COM/writer /Fabiano - Amorim /). a graphical query plan query plan is a set of instructions that SQL Server executes a query. SQL Server Management Studio Displays the query plan in text, graphics, or XML format. For example, consider the following simple query:

SELECT LastName, FirstName, MiddleName, Title

From Person.Contact

WHERE Suffix = ' Jr. '

ORDER by Title

The plan for this query can be seen as 1.

Figure 1- graphical format of the actual query plan or, it can be treated 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 an XML document, start like this:

The display of the query plan can be requested as follows:To request a graphical query plan, use the Management StudioThe SQL Editor toolbar, which has the show estimated execution plan and include actual execution plan buttons. The show estimated execution plan option immediately displays the selectedThe TSQL code queries the plan diagram without executing the query. The Include actual execution plan button is a switch, and once you select this option, each query batch that you perform displays the new query plan chart along with the results and messages. This option is available in the diagramSeen in 1.To request a text query plan, use the SET SHOWPLAN_TEXT on statement. Opening a text version closes the graphical version and does not execute any queries.To view the XML version, right-click the graphics version and choose Show Execution Plan from the context menuXML ". For the rest of this level, we will focus on the graphical view, as it usually provides the quickest understanding of the plan. For a query plan, a picture is usually better than words.Read Graph query plan graphical query plans are usually read from right to left, and the rightmost icon represents the first step in the data collection flow. This is usually the access heap or index. You won't see the list of words used hereInstead, you'll see a clustered index scan or a heap scan. This is the first look at which indexes, if any, are being used.Each icon in the graphical query plan represents an action. For additional information about possible icons, see graphical execution plan icons on http://msdn.microsoft.com/zh-cn/library/ms175913.aspxThe 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 additional information to be displayed.  do not treat the operation as a step because it means that an operation must be completed before the next operation begins. This is not necessarily true. For example, when the WHERE clause is evaluated, that is, when a When the filter operation is executed, the row is evaluated once for a ; not all at once. Before the next line reaches the filter action, the row can be moved to the next operation. On the other hand, the sort operation must complete before the first line moves to the next operation.   use some additional information   The graphical query plan shows two potentially useful information that is not part of the plan itself, the recommended metrics, and the relative cost of each operation.   in the example above, the recommended index (shown in green and truncated by space requirement) recommends using a nonclustered index on the suffix column of the Contact table, including the header, first name, middle name, and last name columns.   the relative cost of each operation of this program tells us that the sort operation is 5% of the total cost, while the table scan is 95% 's work. So if we want to improve the performance of this query, we should fix the table scan instead of sorting

CREATE nonclustered INDEX Ix_suffix on Person.Contact

(

Suffix

)

INCLUDE (Title, FirstName, MiddleName, LastName)

Then rerun the query, our readings dropped from 569 to 3, and the new query plan shown below shows the reason.

New nonclustered index (index key suffix) has " WHERE Suffix = ' Jr. ' Articles gathered together ;  Therefore, retrieving data is required io reduction. Therefore, the same sort operation as in the previously scheduled operation now accounts for the total cost of the query 75% above, not just the original cost 5%. Therefore, the original plan required 75/5 = 15 times times the amount of work to collect the same information as the current plan.   because our WHERE clause contains only an equals sign operator, we can title column is moved into the index key to improve our index as follows:

IF EXISTS (SELECT * from sys.indexes

WHERE object_id = object_id (N ' person.contact ')

and name = N ' Ix_suffix ')

DROP INDEX Ix_suffix on Person.Contact

CREATE nonclustered INDEX Ix_suffix on Person.Contact

(

Suffix, Title

)

INCLUDE (FirstName, MiddleName, LastName)

Now, the required entries are still clustered within the index, and within each cluster, they are in the order of request; as shown in the new query plan,2 shows.

Figure 2-query plan after rebuilding a nonclustered index

The plan now shows that the sort operation is no longer required. at this point, we can abandon our very favorable coverage index. This will restore the way the Contact table is, and when we get started, when we get into our next topic, this is what we want.

View parallel streams

If two rows can be processed in parallel, they will be displayed up and down in the graphical display. the relative width of the arrows represents the number of rows that are being processed in each stream. For example, add the following to extend the previous query to include sales information:

SELECT C.lastname, C.firstname, C.middlename, C.title

, H.salesorderid, H.orderdate

From Person.Contact C

JOIN Sales.SalesOrderHeader H on h.contactid = C.contactid

WHERE Suffix = ' Jr. '

ORDER by Title

As shown in query plan 3.

Figure 3-join's query planQuick View plans tell us something:? Two tables are scanned at the same time.Most of the work is used to scan tables.? More rows or SalesOrderHeader out of the table than outContact Form more.Two tables are not clustered into the same sequence;  so each salesorderheader line with its contact line will require additional effort. In this case, the hash match operation is used. (For more information on hashing.)  ? The amount of work required to sort the selected rows is negligible.   even a separate line flow can be decomposed into separate, less-row streams to take advantage of parallel processing.   for example, if we change the WHERE clause in the above query to where suffix for null.   more rows will be returned, 95% contact line has null suffix. The new query plan reflects this, 4.

Figure 4-A parallel query planThe new plan also shows us the increase in the number of contact lines, which makes the match and sort operations a critical path for this query. If you want to improve performance, you must first attack these two actions. Again, the index containing the column will help.Like most connections, our example connects two tables through a foreign key/primary key relationship. One of the tablesContacts (contact person) PressContactID are sorted,ContactID also happens to be its primary key. In the other table,Saleorderheader,ContactID is a foreign key. BecauseContactID is a foreign key, soContactID access to Saleorderheader data requests, such as our join example, can be a common business requirement. These requests will benefit from the index on the ContactID. Whenever you index a foreign key column, always ask yourself, if any, that the column should be added to the index as a containing column. In our case, we only have one query, not a series of queries to support it. Therefore, the only column we contain will be OrderDate. To support a series of contactid-oriented queries against the Saleorderheader table , we will include more saleorderheader columns in the index to support these additional queries as needed. Our CREATE INDEX statement is:

CREATE nonclustered INDEX Ix_contactid on Sales.SalesOrderHeader

(

ContactID

)

INCLUDE (OrderDate)

and the new program 5 that performs our SalesOrderHeader and contact information connection is shown.

Figure 5-Plan to use a join query that supports indexes on each tableBecause two input streams are now sorted by the JOIN predicate column ContactID, the join portion of the query can be completed without splitting the stream, and no hashing is required, thus reducing the workload's 26 + 5 + 3 = 34% to 4% of the workload.Sort, push and hashMany query operations require that data be grouped before the operation is performed. These include distinct,union (which means different), GROUP by (and its various aggregate functions), and joins. Typically, SQL Server uses one of the following three methods to implement this grouping, and the first method requires your help:happy to find that the data has been pre-categorized into a grouping sequence. ? Group the data by performing a hash operation. ? Classify the data into a grouping order. a pre-categorized index is the way you predict data, that is, providing data to SQL Server in the order that is often needed. This is why creating non-clustered indexes (each containing columns) has benefited our previous examples. In fact, if you place the mouse over the merge connections icon in the most recent query, the rows are matched with two appropriately sorted input streams, and their sort order is used. will appear. This informs you that two table/index rows are connected using the absolute minimum value of memory and processor time. The appropriate sort input is a great phrase that validates the index of your choice when the mouse hovers over the query plan icon. Hash If the order of incoming data is inappropriate, SQL Server may use a hash operation to group the data. Hashing is a technique that can use a lot of memory, but is usually more efficient than classification. When performing distinct,union and join operations, hashing has an advantage over sorting, that is, a single row can be passed to the next operation without having to wait for all incoming rows to be hashed. However, when grouping aggregations are computed, all input rows must be read before any aggregated values can be passed to the next operation. The amount of memory required to hash information is directly related to the number of groups required. Therefore, hashing is required to resolve:

SELECT Gender, COUNT (*)

From Newyorkcitycensus

GROUP by Gender

It only takes a little memory, because there are only two groups; women and men, regardless of the number of input lines. On the other hand:

SELECT LastName, FirstName, COUNT (*)

From Newyorkcitycensus

GROUP by LastName, FirstName

Can lead to a large number of groups, each group needs its own memory space, may consume too much memory, hash becomes the bad technique to solve the query.For more information about query plan hashing, visit http://msdn.microsoft.com/en-us/library/ms189582.aspx.SortIf the data is not pre-categorized (indexed), and if SQL Server considers the hash not to be effectively completed,SQL Server will 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. IfSorticon appears near the end of the plan, which may meanSQL Server outputs the final output asOrder ordered by clause requested, and the sequence of GROUP by and union is different from the JOIN used to parse the query. Usually, there are things you can do to avoid this. Conclusion The query plan shows the methods that SQL Server intends to use or has already used to execute the query. It is implemented by detailing the operations that will be used, from the operation to the line of action, and the parallelism involved. You treat this information as text, graphic, or XML display. ? The graphics plan shows the relative amount of work for each operation. ? The graphics plan might suggest an index to improve the performance of the query. Understanding the query plan will help you evaluate and optimize your index design

Read the query plan: SQL Server index Level 9