Analyze SQL Server execution plans

--> Title: SQL Server execution plan
--> Author: wufeng4552
--> Date: 15:08:24

Preface:

Recently, I have written some of my understanding about online planning. I have made the following arrangement to help students better understand their impressions and convenience.

I am not welcome to raise this question and correct it.

How to view the execution plan:

(1) dishes:

(1.1) display the actual execution plan

(1.2) display the estimated execution plan

The two types are located in the "query" drop-down menu. The difference between the two is that when a query is run, the calculation on the current server will also be taken into account. In most cases, the execution plans produced by the two methods produce similar results.

(2) command Method

Set showplan_text on

After this command is executed, all queries executed in the current query analyzer session will not run, but a text-based execution plan will be displayed.

Note: When executing a query that uses a temporary table, you must run the set statistics profile on statement before executing the query, for example:

Go

If not object_id ('tempdb .. # t') is null

Drop table # T

Go

Create Table # T ([date] datetime, [name] nvarchar (2 ))

Insert # T

Select '2014-10-01 ', N 'zhang san' Union all

Select '2014-10-01 ', n' Li si' Union all

Select '2014-10-02 ', N 'zhao 6'

Go

Set statistics profile on

Go

Select * from # T

Set statistics profile off

Result 1:

 

Limit 1

For the convenience of further discussion, the following uses the [Order Details] table in the northwind tables as an example (I have already divided the primary keys)

Use northwind

Go

Set showplan_text on

Go

Select productid, sum (Quantity) quantity from [Order Details]

Group by productid order by productid

Go

Set showplan_text off

/*

Stmttext

| -- Sort (order by :( [northwind]. [DBO]. [Order Details]. [productid] ASC ))

| -- Hash match (aggregate, hash :( [northwind]. [DBO]. [Order Details]. [productid]) define :( [expr1004] = sum ([northwind]. [DBO]. [Order Details]. [quantity])

| -- Table scan (Object :( [northwind]. [DBO]. [Order Details])

*/

Use northwind

Go

---- Create a clustered Index

Create clustered index index_productid on [Order Details] (productid)

Go

Set showplan_text on

Go

Select productid, sum (Quantity) quantity from [Order Details]

Group by productid order by productid

Go

Set showplan_text off

/*

Stmttext

---------------------------------------

| -- Stream aggregate (group by :( [northwind]. [DBO]. [Order Details]. [productid]) define :( [expr1004] = sum ([northwind]. [DBO]. [Order Details]. [quantity])

| -- Clustered index scan (Object :( [northwind]. [DBO]. [Order Details]. [index_productid]), ordered forward)

(Two data columns are affected)

*/

If you see any of the following items in the execution plan, each of the items shown below is unsatisfactory in terms of performance.

Index or table scans (index or table scan): it may mean better or additional indexes are required.

Bookmark lookups: you can modify the current clustered index and overwrite the index to limit the number of fields in the SELECT statement.

Filter: Remove any function used in the WHERE clause. do not include a view in an SQL statement. Additional indexes may be required.

Sort: Do data really need to be sorted? Can indexes be used to avoid sorting? Will sorting on the client be more efficient?

The more you want to avoid, the faster the query performance.

NOTE: If temporary tables are used in stored procedures or other T-SQL batch code, you cannot evaluate a query using the show estimated execution plan option in the query analyzer or management studio. You must actually run the stored procedure or batch processing code. This is because when a query is run using the "show estimated execution plan" option, it is not actually run, and the temporary table is not created. Because the temporary table is not created, the code for referring to the temporary table will fail, and the estimated execution plan cannot be created successfully. On the other hand, if you are using Table variables rather than temporary tables, you can use the "show estimated execution plan" option.

Use northwind

Go

Select a. * from [orders] A, [Order Details] B

Where a. orderid = B. orderid

 

Limit 2

Remember the following points when viewing the execution plan:

(1) very complex execution plans are divided into multiple parts, which are listed on the screen separately. Each part represents a single process or step that the query optimizer must perform to get the final result. Each step of the execution plan is often split into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you have to scroll to the rightmost side of the graphic execution plan to see where each step starts.

(2) Each step is connected to the sub-step through an arrow to display the query execution path.

(3) Finally, all the queried parts are summarized on the left of the top of the screen. If you move the cursor over the arrow of the connection step or substep, a pop-up window is displayed, the above shows how many records are moved from one step or sub-step to another step or sub-step (such as step 3) if you move the cursor over any execution plan step or sub-step, A pop-up window is displayed, showing more detailed information about the step or sub-step, such as step 2.

 

Limit 3

(4) The arrows connecting each icon on the graph execution plan have different thicknesses (such as limit 3 ). The arrow width indicates the number of data rows to move between icons and the relative size required for moving data rows. The thicker the arrow, the higher the relative cost. You can use this indicator to quickly measure a query. You may pay special attention to the rough arrow to learn how it affects the query efficiency. For example, the line header should be on the right side of the graphic execution plan, not the left side. If you see them on the left, it means that too many data rows are returned, and this execution plan is not the best execution plan.

(5) Each part of the Execution Plan is allocated with a cost percentage (for example, cost 2 and 3 ). It indicates the amount of resources consumed by the execution plan. When analyzing an execution plan, you should focus on those parts with a high cost percentage. In this way, you can find the most likely problem in a limited time, and return your investment in time.

(6) You may notice that some parts of an execution plan have been executed more than once. As part of execution plan analysis, you should focus some of your time on any part that has been executed more than once to see if there is any way to reduce the number of times they are executed. The smaller the number of executions, the faster the query speed.

(7) I/O and CPU costs. The query optimizer uses these numbers to make the best choice. They can be used as a reference. Smaller I/O or higher CPU costs use less server resources.

Use northwind

Go

-- Distinct clustered Index

Drop index [Order Details]. index_productid

-- Create clustered index index_productid on [Order Details] (productid)

Set statistics Io on

Select * from [Order Details] Where productid = 42

Set statistics Io off

Data Table 'order details '. The metric data is 1. The metric values are 11, the actual metric values are 0, the previous metric values are 0, the lob metric values are 0, and the lob metric values are 0, take the lob cursor from the front iterator to 0.

Use northwind

Go

-- Distinct clustered Index

-- Drop index [Order Details]. index_productid

-- Create a clustered Index

Create clustered index index_productid on [Order Details] (productid)

Go

Set statistics Io on

Select * from [Order Details] Where productid = 42

Set statistics Io off

Data Table 'order details '. Metric 1, metric 2, actual metric 0, initial metric 0, lob metric 0, and LOB metric 0, take the lob cursor from the front iterator to 0.

The preceding figure shows that logical reads differ greatly. You can use set statistics Io on to view logical reads and complete different SQL statements of the same function. The smaller the logical reads, the faster the query speed.

(8) move the cursor over the table name (and its icon) in the graphic execution plan, and a window is displayed, showing some information. This information allows you to know whether indexes are used to obtain data from tables and how they are used. The information includes:

(8.1) Table scan: if this information is displayed, it indicates that no clustered index is found on the data table, or the query optimizer does not use the index for searching. That is, each row of the data table is checked. If the data table is relatively small, table scanning can be very fast, sometimes even faster than using indexes. Therefore, when a table scan is performed, the first thing to do is to check the number of data rows in the data table. If not, table scanning may provide the best overall performance. However, if the data table is large, it is very likely that the table scan will take a long time to complete, and the query efficiency will be greatly affected. In this case, you need to carefully study and add an appropriate index for the data table for this query. Suppose you find that a query uses a table scan and has a suitable non-clustered index, but it is not used. What does this mean? Why is this index useless? If the data size to be obtained is very large relative to the data table size, or the data selectivity is not high (meaning there are many duplicate values in the same field), table scanning is often faster than index scanning. For example, if a data table has 10000 data rows and the query returns 1000 rows, if the table has no clustered index, the table scan is faster than using a non-clustered index. Or if the data table has 10000 data rows and the same field (where condition is useful to this field) has 1000 million duplicate data records, the table scan will be faster than using non-clustered indexes. When viewing the pop-up window on the data table on the graph execution plan, note that "the estimated number of data rows (estimated row count )". This number is the best estimate of the number of rows returned by the query optimizer. If a table scan is performed and the "Estimated number of data rows" value is very high, it means a large number of records are returned. The query optimizer considers that executing a table scan is faster than using an available non-clustered index.

(8.2) index seek (index search): Index Search means that the query optimizer uses non-clustered indexes on the data table to search for data. Performance is usually very fast, especially when only a few data rows are returned.

(8.3) clustered index seek (clustered index search): this means that the query optimizer uses clustered indexes on the data table to search for data, and the performance is very fast. In fact, this is the fastest index search type that SQL server can do.

(8.4) clustered index scan: clustered index scan is similar to table scan. The difference is that clustered index scan is performed on a data table with clustered index. Like normal table scans, clustered index scans may indicate performance problems. In general, there are two reasons for this clustered index scan. The first reason is that, compared with the total number of data rows on the data table, too many data rows may need to be obtained. Check "Estimated number of data rows (estimated row count)" to verify this. The second reason may be that the fields used in the WHERE clause are not highly selective. In any case, unlike standard table scans, clustered index scans do not always search for all data in a data table. Therefore, clustered index scans are generally faster than standard table scans. Generally, you need to change clustered index scan to clustered index search. The only thing you can do is rewrite the query statement to limit the statement and return less data rows.

(9) In most cases, the query optimizer analyzes connections and uses the most efficient connection type to connect data tables in the most efficient order. But not always. In the graphic execution plan, you can see icons that represent different connection types used in the query. In addition, each connection icon has two arrows pointing to it. The arrow above the connection icon indicates the External table of the connection, and the Arrow below indicates the internal table of the connection. The other end of the arrow points to the name of the connected data table. Sometimes in Multi-table join queries, the other end of the arrow points to not one data table, but another connection. If you move the cursor over the Arrow between the external connection and the internal connection, you can see a pop-up window that shows you how many data rows are sent to this connection for processing. The External table should always contain fewer data rows than the internal table. If not, the connection sequence selected by the Query Optimizer may be incorrect.

(10) When you view the graphic execution plan, you may find that the text of an icon is displayed in red rather than black in general. This means that some statistical data of the relevant table is lost. The statistical data is necessary for the query optimizer to generate a good execution plan. You can right-click this icon to view the missing statistical data, and select "create lost Statistics" to create. The "create lost statistical data" dialog box is displayed, through which you can easily create lost statistical data. This should always be done when you can choose to update the lost statistics, because it is very likely to benefit the efficiency of the query statements you are analyzing.

(11) Sometimes you will see the "assert" icon on the graphic execution plan. This means that the query optimizer is verifying whether the query statement violates the integrity or condition constraints of the reference. If no, there is no problem. However, the query optimizer will not be able to create an execution plan for the query, and an error will occur.

(12) You will often see the "bookmark lookup" icon on the graphic execution plan. Searching bookmarks is quite common. The essence of bookmarksearch is to tell you that the query processor must search for the required data rows from a data table or clustered index, rather than directly reading from non-clustered indexes. For example, if all the fields in the select, join, and where clauses of a query statement do not exist in the non-clustered index used to locate the data row that meets the query conditions, then the query optimizer has to do additional work to find the fields that meet the query statement in the data table or clustered index. Another reason for bookmarked search is that select * is used *. In most cases, it will return more data than you actually need, so you should never use select *. In terms of performance, the bookmarked search is not ideal. Because it will request additional I/O overhead to search in the field to return the required data rows. If you think that the bookmarked search does not affect the query performance, there are four options to avoid it: you can create a clustered index that will be used by the WHERE clause and take advantage of the index intersection, create a covered non-clustered index, or (if it is SQL Server 2000/2005 Enterprise Edition) Create an index view. If none of these are possible, or any of them will consume more resources than the bookmarks, then the best choice is to search for bookmarks.

(13) Sometimes the query optimizer needs to create a temporary worksheet in the tempdb database. If this is the case, it means that there are icons marked as index spool, row count spool or table spool in the graph execution plan. Using a worksheet at any time may affect performance, because additional I/O overhead is required to maintain the worksheet. Ideally, do not use worksheets. Unfortunately, you cannot always avoid using worksheets. Sometimes, when using a worksheet is more efficient than other options, it actually improves performance. Regardless of the situation, worksheets in the graphic execution plan should alert you. You should carefully check such query statements to see if you can rewrite the query to avoid using worksheets. There may be no way. However, if there is one, you will step forward in improving the query performance.

(14) The stream aggregate icon on the graphic execution plan means that a single input is aggregated. When a distinct clause or any aggregate function is used, such as AVG, Count, Max, Min, or sum, stream aggregation is quite common.

(15) The query analyzer and Management studio are not the only tools that can generate and display query execution plans. SQL Server Profiler can also display execution plans in text format. One advantage of using SQL Server Profiler to display an execution plan is that it can generate an execution plan for a large number of actually running queries. If you use the query analyzer and Management studio, you can run only one

(16) if the option fast prompt is used in the query, you must be careful that the execution plan result may not be what you expected. In this case, the execution plan you see is based on the results with the fast prompt, rather than the actual execution plan of the entire query statement. The fast prompt is used to inform the query optimizer to return the data rows of the specified number of rows as quickly as possible, even if this will prevent the overall performance of the query. The purpose of this prompt is to quickly return records of a specific number of rows, which leads to the illusion that the speed is very fast .. When the data rows of the specified number of rows are returned, the remaining data rows are returned at their normal speed. Therefore, if the fast prompt is used, the generated execution plan is only based on the data rows returned by the fast, instead of all data rows to be returned by the query. If you want to view the execution plan of all data rows, you must remove this fast prompt.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wufeng4552/archive/2009/10/20/4703976.aspx