SQL bit 27-execution plan for performance analysis

Original: SQL Drip 27-execution plan for performance analysis

Always want to find some of the authoritative reference for SQL statement performance debugging, but there is no reference may not be able to do the debugging work. I am convinced that the experience gained in practice is the most precious, and that book knowledge is only a guide. This article is from "Inside Microsoft SQL Server 2008", the experienced master despite the shooting brick.

This section explains some of the performance analysis tools that are focused on the execution plan.

Cache Execution Plan

SQL Server 2008 provides a number of server objects to analyze the execution plan
Sys.dm_exec_cached_plans: contains the cached execution plan, one row for each execution plan.
sys.dm_exec_plan_attributes: This is a system function, and each execution plan corresponds to a number of attributes, which are included in this system function.
sys.dm_exec_sql_text: This is a system function that returns the execution plan for the text format.
sys.dm_exec_query_plan: This is a system function that returns an XML-formatted execution plan.
SQL Server 2008 also provides a compatibility view of Sys.syscacheobject, which holds all the information about the execution plan.

　　

Clear Cache

Sometimes you need to clear the cache for the next analysis when performing profiling. SQL Server provides a number of tools to clear cached performance data. Use the following statement to accomplish these tasks.

Clear the global cache using the following statement:

DBCC dropcleanbuffers;

To clear the execution plan from the global cache, use the following statement:

DBCC Freeproccache;

To clear the execution plan from a database, use the following statement:

DBCC flushprocindb (<db_id>);

Clear a specific execution plan using the following statement:

DBCC Freesystemcache (<cachestore>);

You can use ' all ', Pool_name, ' Object Plan ', ' SQL plans ', ' Bound Trees ' as input parameters. The ' all ' parameter indicates that you want to clear all caches, and Pool_name values indicate the name of a cache pool to be purged. ' Object plans ' clears the object plan (for example, stored procedures, triggers, user-defined functions, and so on). ' SQL plans ' is used to clear the statement to be executed immediately. ' Bound Trees ' defines the cache of clear views, constraints, etc.

Note: be clear before using these statements to clear the cache, especially in production environments. These have a great impact on performance. When these caches are cleared, SQL Server needs to re-read the data from the data page. and SQL Server needs to regenerate the new execution plan. It is therefore clear that these effects on the production or test environment are to be understood before removal.

　　

dynamic management of objects

SQL Server 2005 introduces dynamic management objects, such as DMV,DMF. New objects are added in SQL Server 2008, new properties. These are very useful information that can be used to monitor SQL Server, diagnose problems, and perform performance monitoring. It can be time-consuming to study these objects carefully. Here is just a list of some common ones.

　　

Statistics IO

Statistics IO is a session option. It returns the I/O information related to the statement currently executed by the domain. To use this option, you prefer to clear the data cache:
DBCC dropcleanbuffers;

Then run the following code to open this option:
SET STATISTICS IO on;
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler
FROM dbo. Orders
WHERE OrderDate >= ' 20060101 '
and OrderDate < ' 20060201 ';

Finally, you can get a message similar to the following:
(21226 row (s) affected)
Table ' Orders '. Scan count 1, logical reads 537, physical reads 3, Read-ahead reads 549, LOB logical reads 0, LOB physical reads 0, LOB re Ad-ahead reads 0.

From the output information we can see how many times the table was fetched (Scan count) in the execution plan, how many times the cache was read (logical reads), how many times the hard disk was read (physical reads my read-ahead reads) ; How many times to read large objects (LOB physical reads, log read-ahead reads).
Use the following statement to turn off this option:
SET STATISTICS IO OFF;

Statistic Run Time

STATISTICS time is a session option to return CPU clock times. It returns the parsing, compilation, and execution time. To use this option, you prefer to clear the execution plan cache.

DBCC dropcleanbuffers;

DBCC Freeproccache;

Run the following statement to open the appropriate option:

SET STATISTICS time on;

Run the following statement:
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler from dbo. Orders WHERE OrderDate >= ' 20060101 ' and OrderDate < ' 20060201 ';

Get the following information:

SQL Server parse and compile time:

CPU time = 0 ms, Elapsed time = 4 Ms.

SQL Server Execution times:

CPU time = ms, Elapsed time = 544 Ms.

From this information can be obtained when executing this statement CPU clock time, compile time, run time. You can turn off this option by running the following statement:

SET STATISTICS time OFF;

This option is useful when you need to analyze the performance of a single statement. Metrics are different when you need to use batch mode to run statements. The value of the Sysdatetime function is saved and written to a table before the query. Note that the time format returned by this function is DATETIME2, which can be accurate to 100 nanoseconds. The accuracy of this function depends on the computer hardware and the operating system version. Because this function calls Getsystemtimeasfiletime () this windowsapi. You can run the request statement repeatedly when you need to count the time, and then log the time that is required.

　

Analyze Execution Plan

The execution plan is a work schedule that the SQL optimizer generates to handle a given request. It contains the operator used to request Chinese medicine. Some operations may be performed more than once. Some planned branches may be executed in parallel. In this work plan, the optimizer determines the order of the tables involved in getting the statements, using those indexes, using those query methods, using those algorithms, and so on. In fact, the optimizer chooses an optimal number of execution plans and consumes the least amount of resources. It is also time consuming to generate execution plans frequently, so SQL Server also estimates the threshold time required to generate execution plans based on the size of the data volume. The time to generate the execution plan does not exceed this estimated threshold time. There is also a threshold that is calculated based on the cost of the resource. If a work plan's resources are consumed below this threshold, it is considered good enough that the optimizer will stop optimizing the use of the program.

　　

Graphical execution Plan

SSMs allows us to view a graphical execution plan (shortcut key ctrl+l). Note When you view an execution plan, the query does not run. Some measures can only be obtained after they have been run (the number of rows actually queried).

Use the following statement to view the execution plan:

SELECT CustID, Empid, ShipperID, COUNT (*) as numorders from dbo. Orders WHERE OrderDate >= ' 20080201 ' and OrderDate < ' 20080301 '

GROUP by CUBE (CustID, Empid, ShipperID);

This statement queries for all possible aggregated values, and the aggregation property is Custid,empid,shipperid. 1

Figure 1

　　

Note that when the execution plan takes up a lot of screen space, you can click on the button "+" at the bottom right, then drag the mouse to view the area you want to see.

An execution plan is a tree chart that consists of some operations. The data flows from the child operation to the parent operation. The order of this structure is from right to left, from top to bottom. In this example, the operation is preferred from the clustered index, followed by the manipulation of the winding operation-table Spool

Note that there is a percentage next to each operator, and this value table value is the percentage of the resource that this operation occupies during the entire execution, which is only the value of the optimizer estimate. The optimization of SQL statements should be placed on those operations that account for the larger percentage. When you put the mouse on the time, there will be a color change the prompt box. There is a value of estimated subtree cost. At the top, the resource overhead of the entire operation when the most workshop operation. 2

Figure 2

　　

Note that these values are just the values that the optimizer estimates, and the optimizer uses this value to compare with the other estimates to select an optimal execution plan.

Another good place to compare is when you can generate execution plans for multiple statements at the same time. For example, the following statement:

--1
SELECTCustID, OrderID, OrderDate, Empid, filler
 fromDbo. Orders asO1
WHEREOrderID=
(SELECT TOP(1) O2.orderid
 fromDbo. Orders asO2
WHEREO2.custid=O1.custid
ORDER  byO2.orderdateDESC, O2.orderidDESC);
--2
SELECTCustID, OrderID, OrderDate, Empid, filler
 fromDbo. Orders
WHEREOrderIDinch
(
SELECT
(SELECT TOP(1) O.orderid
 fromDbo. Orders asO
WHEREO.custid=C.custid
ORDER  byO.orderdateDESC, O.orderidDESC) asOid
 fromDbo. Customers asC
);
--3
SELECTA.*
 fromDbo. Customers asC
 CrossAPPLY
(SELECT TOP(1)
O.custid, O.orderid, O.orderdate, O.empid, O.filler
 fromDbo. Orders asO
WHEREO.custid=C.custid
ORDER  byO.orderdateDESC, O.orderidDESC) asA
--4
 withC as
(
SELECTCustID, OrderID, OrderDate, Empid, filler,
Row_number () Over(PARTITION byCustID
ORDER  byOrderDateDESC, OrderIDDESC) asN
 fromDbo. Orders
)
SELECTCustID, OrderID, OrderDate, Empid, filler
 fromC
WHEREN= 1;

Their query results are the same, but the execution plan is different. At the beginning of each execution plan there is a percentage that indicates the percentage of overhead that the statement occupies in all statements. In this example we can see that the ratio of the first statement is 37%, the second statement is 19%, the third is 30%, and the fourth is 14%. From this result we can roughly determine the efficiency of the fourth statement is higher.

When you put the mouse on top of the operator there will be a yellow cue box 4

Figure 4

　　

Some of the following metrics are available in this prompt box:

• The name of the operator and the simple introduction
• Physical operations: physical operations within a computer
• logical operations: logical operators that match physical operators, such as the Inner Join operator. The logical operators are listed after the physical operators, both at the top of the ToolTip.
• number of rows returned: number of data rows returned by the operation
• Estimating I/O overhead, Estimating CPU Overhead: This data can be used to estimate whether this operation is causing significant CPU or I/O overhead, and the general sort operation will cause significant I/O overhead
• estimated number of rows and rows executed: estimate how many times the operation was performed and how many times it was actually executed. This data can help you find better execution statements.
• Estimated execution cost: the cost of the query optimizer to perform this operation
• Estimating subtree Cost: The query optimizer does this and the total cost of all operations that precede this operation in the same subtree
• Number of rows generated by the operation: estimated number of rows generated by the operator. In some cases, the merits and demerits of an SQL statement can be judged by the difference between the actual number of rows and the estimated number of rows.
• Estimated Data size: The estimated size (in bytes) of the rows generated by the operator. You may wonder why this actual line number is not shown in the execution plan because there are variable-length data types in the data rows
• actual re-binding and rewinding: This data is related to some specific operations (non-clustered entanglement, remote request, line number winding, sorting, table winding, table-valued function, assertion, filtering, etc.). This will only be counted when the inner layer is nested within the query, otherwise rebinds is 1,rewinds 0. These data represent the internal layer of the Init method being called. The combination of the heavy and heavy wrap should be the sum of the number of rows that the outer join gets. Re-binding means that one or more of the associated connection parameters have changed and need to be re-evaluated. The re-winding means that the relevant parameters have not changed and can reuse the previously obtained internal result set
• information at the bottom: display related object names, outputs, parameters, etc.

　　

Select an operator and press the F4 key to see more detailed information.

　　

execution plan in text format

You can view the execution plan in text format by setting it. To do this, set the SHOWPLAN_TEXT option as follows:
SET showplan_text on;
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler
FROM dbo. Orders WHERE orderid = 280885;

Review the execution plan (CTRL+L) to get the following results:
(1 row (s) affected)
Stmttext
-----------------------------------------------------------------------------------------------------
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler
FROM dbo. Orders WHERE orderid = 280885;

(1 row (s) affected)

stmttext
--------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------
  |--nested Loops (Inner Join, OUTER REFERENCES: ([Uniq1002], [performance].[ DBO]. [Orders]. [OrderDate]))
       |--index Seek (OBJECT: ([performance].[ DBO]. [Orders]. [Pk_orders]), SEEK: ([Performance]. [dbo]. [Orders]. [orderid]=[@1]) ORDERED FORWARD)
       | -clustered Index Seek (OBJECT: ([performance].[ DBO]. [Orders]. [Idx_cl_od]), SEEK: ([Performance]. [dbo]. [Orders]. [Orderdate]=[performance]. [dbo]. [Orders]. [OrderDate] and [uniq1002]=[uniq1002]) LOOKUP ORDERED FORWARD)

(3 row (s) affected)

(1 row (s) affected)

Analyze this execution plan, from the inner branch to the outer branch, from top to bottom. But here we can only see the names and parameters of the operators. Run the following statement to turn off this option:
SET Showplan_text OFF;
If you want more detailed execution plan information, use the SHOWPLAN_ALL option to view the execution plan, STATISTICS the profile option to view a specific execution plan. Showplan_all writes the execution plan information to a table that contains some estimated values: Stmttext, Stmtid, NodeId, Parent, Physicalop, LogicalOp, Argument, Defi Nedvalues,estimaterows, Estimateio, Estimatecpu, Avgrowsize, Totalsubtreecost, Outputlist,warnings, Type, Parallel, and Estimateexecutions.
Open this option by using the following statement:
SET Showplan_all on;
Run the following statement:
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler
FROM dbo. Orders WHERE orderid = 280885;
The results are as follows 5:

Figure 5

　　

Run the following statement to close the option:
SET Showplan_all OFF;
The STATISTICS profile option produces an actual plan. Setting this option to on shows that the result and setting Showplan_all is on almost, but there are two more properties ROSW and executes, which represent the actual number of rows and the number of rows running.

The statements are as follows:
SET STATISTICS profiles on;
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler from dbo. Orders WHERE orderid = 280885;

To cancel the setting:
SET STATISTICS profile OFF;

　　

Execution plan in XML format

If you want to use your own code to describe the execution plan or send the execution plan to the customer or colleague, you will find it inconvenient to use the text format information. SQL Server 2008 allows the execution plan content to be returned in XML format, which is good for application code processing. Opening an execution plan that uses SQL Server 2008-generated XML format is displayed as a graphical result with a suffix of. SQLPlan.

The code to open this option is as follows:

SET showplan_xml on;

Run statement

SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler from dbo. Orders WHERE orderid = 280885;

Running results such as 6:

Figure 6

Click on this XML file, the graphical format of the execution plan such as 7:

Figure 7

Use the following statement to close the option:
SET Showplan_xml OFF;

In order to not affect the output effect of other statements, it is recommended to use code similar to the following to see the effect:
SET STATISTICS XML on;
GO
SELECT OrderID, CustID, Empid, ShipperID, OrderDate, filler from dbo. Orders WHERE orderid = 280885;
GO
SET STATISTICS XML OFF;

You can see that the execution plan in XML format provides the most friendly viewing form.

SQL bit 27-execution plan for performance analysis