Read the query plan: SQL Server index Level 9

David Durant,2011/10/05

Original link:http://www.sqlservercentral.com/articles/Stairway+Series/72441/the series This article is“StairwaySeries:SQL Serverpart of the ladder of the index indexes 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. Here at the end is a simple series of articles that should enable them to quickly make any database professionalFastThroughout this phase, we often say that a query executes in some way, and we refer to the generated query plan to support our statement. Management StudioThe estimated and actual query plans that are displayed 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 the query plan, includingMsdnsome articles in the library. We are not going to expand or replace them here. In fact, we will provide many of these links at this level/Reference. Displays the graphical execution plan (http://msdn.microsoft.com/zh-cn/library/ms178071.aspx) is a good start. Other useful resources includeGrant Fritcheythe book,SQL ServerImplementation Plan (available free of charge in ebook format) andFabiano Amorimabout the various operators found in the query plan output .Simple-talkArticle series (http://www.simple-talk. COM/writer/Fabiano- Amorim/). graphical query plan the query plan isSQL Servera set of instructions that execute a query. SQL Server Management Studiowill be in text, graphic, orXMLThe format displays the query plan. 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 a XML document, starting with this:

the display of the query plan can be requested as follows:?to request a graphical query plan, use theManagement Studioof theSQLThe Editor toolbar, which has the show estimated execution plan and include actual execution plan buttons. The show estimated execution plan option immediately displays the selectedTSQLthe query plan diagram for the code 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 diagram1seen in the. ?to request a text query plan, use theSET Showplan_text onstatement. Opening a text version closes the graphical version and does not execute any queries. ?to viewXmlversion, 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 the graphical query plan graphical query plans typically read from right to left;The rightmost icon represents the first step in the data collection stream. This is usually the access heap or index. You won't see the list of words used here;instead, 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, seeHttp://msdn.microsoft.com/zh-cn/library/ms175913.aspxgraphical execution plan icon on the 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 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, whenWHEREWhen a clause is evaluated, that is, when aFilterwhen the operation is executed, the row is evaluated one at a time;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;includes the title, first name, middle name, and last name of the column. The relative cost of each operation of this plan tells us that the sort operation is the total cost of5%, while the table scan is the% of the work. So if we want to improve the performance of this query, we should fix the table scan instead of sorting;That's why the index is recommended. If we create the recommended index, like this:

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; the new query plan shown below shows the reason.

new nonclustered index (index key = Suffix where Suffix = ' Jr. "entries are clustered together Therefore, retrieving the data requires the io reduction. Therefore, the same sort operation as the previous scheduled operation now accounts for the total cost of the query 75 % above, not just the original cost of 5 %. As a result, the initial plan required 75/5 = The same amount of work to collect the same information as the current plan. because our where clause contains only an equals operator, so we can title column move index key to improve our index, As shown below:

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 is shown.

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 .

Figure3-jointhe query plan Quick View plans tell us something:?Two tables are scanned at the same time. ?Most of the work is used to scan tables. ?out of more rows orSalesOrderHeadertable compared to the Contacttable More. ?Two tables are not clustered into the same sequence;As a result, eachSalesOrderHeaderThe 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 individual row streams can be decomposed into separate, less-row streams to take advantage of parallel processing. For example, if we use the above queryWHEREclause to change toWHERE Suffixto beNULL. more rows will be returned,95% of ContactLine hasNULLsuffix. This is reflected in the new query plan,4is shown.

Figure4-a parallel query plan The 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 passes through a foreign key/A primary key relationship connects two tables. One of the tables Contact(Contact person) PressContactIDto sort,ContactIDalso happens to be its primary key. In the other table,Saleorderheader,ContactIDis a foreign key. BecauseContactIDis a foreign key, soContactIDaccess toSaleorderheaderdata requests, such as our join example, are likely to be common business requirements. These requests will benefit fromContactIDon the index. 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 columns we contain will beOrderDate. To support targetedSaleorderheadera series of table-orientedContactIDquery, we will include more in the index as neededSaleorderheadercolumns to support these additional queries. of ourCREATE INDEXstatement is:

CREATE nonclustered INDEX Ix_contactid on Sales.SalesOrderHeader

(

ContactID

)

INCLUDE (OrderDate)

and the implementation of our SalesOrderHeader and The contact Information link are shown in the new program 5 .

Figure5-plan to use a supported index on each tableJOINEnquiry because two of the input streams are now listed by the JOIN predicateContactIDto sort;of the queryJOINsection can be done without splitting the stream, and does not require hashing;The workload is thus+ 5 + 3 =% reduced to the workload of4%. sort, push and hash Many query operations require that data be grouped before the operation is performed. These includeDISTINCT,UNION(meaning different),GROUP by(and its various aggregate functions) andJOIN. Usually,SQL Serveryou will use one of the following three methods to implement this grouping, and the first method needs your help:?happily found that the data has been pre-categorized into the grouping sequence. ?the data is grouped by performing a hash operation. ?classify the data into a grouping order. Pre-classification indexes are the way you predict data;in the order that they are often neededSQL Serverprovide data. 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 will inform you of two tables/the rows of the index 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 Serveryou might 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. In the executionDISTINCT,UNIONand theJOINoperation, Hashing has an advantage over sorting, that 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

will lead to a large number of groups, each needs their own memory space;may consume too much memory, hashing becomes a bad technique for resolving queries. For more information about query plan hashing, go toHttp://msdn.microsoft.com/en-us/library/ms189582.aspx. Sort If the data is not pre-categorized (indexed), and ifSQL Serverthat the hash cannot be done efficiently,SQL ServerThe data is sorted. 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. IfSorticonappears near the end of the plan, which may meanSQL ServerPress the final outputORDER byordered order of the clauses requested;and the sequence that is used to parse the queryJOIN,GROUP byand theUNIONthe sequence is different. Usually, there are things you can do to avoid this. Conclusion Query plan displaySQL Serverthe method that you intend to use or have 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, graphics, orXMLdisplay. ?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