SQL-execution plan for Performance Analysis)

I have been looking for some authoritative references on SQL statement performance debugging, but I may not be able to do a good job of debugging. I believe that the experience gained in practice is the most precious, and book knowledge is just a guide. This article comes from "Inside Microsoft SQL Server 2008", although experienced experts make bricks.

 

This section describes some performance analysis tools that focus on execution plans.

 

Cache execution plan

SQL Server 2008 provides some Server objects to analyze execution plans.
Sys. dm_exec_cached_plans:Contains cached execution plans. Each Execution Plan corresponds to one row.
Sys. dm_exec_plan_attributes:This is a system function. Each Execution Plan corresponds to some attributes that are included in this system function.
Sys. dm_exec_ SQL _text:This is a system function that returns the execution plan in the text format.
Sys. dm_exec_query_plan:This is a system function that returns an execution plan in xml format.
SQL Server 2008 also provides a Compatibility View sys. syscacheobject, which stores information about all execution plans.

　　

Clear Cache 

During performance analysis, you sometimes need to clear the cache for the next analysis. SQL Server provides some tools to clear cache performance data. Use the following statement to complete these tasks.

Use the following statement to clear the global cache:

Dbcc dropcleanbuffers;

 

Clear the execution plan from the global cache and use the following statement:

Dbcc freeproccache;

 

Clear the execution plan in a database and use the following statement:

Dbcc flushprocindb (<db_id> );

 

Clear a specific execution plan and use the following statement:

Dbcc freesystemcache (<cachestore> );

 

You can use 'all', pool_name, 'object plan', 'SQL Plans', and 'bound trees' as input parameters. The 'all' parameter indicates that ALL caches are to be cleared, and the value of pool_name indicates the name of a cache pool to be cleared. 'Object Plans 'clears Object Plans (such as stored procedures, triggers, user-defined functions, and so on ). 'SQL Plans' is used to clear the statements to be executed immediately. 'Bound trees' defines cache for clear views and constraints.

Note: You must be clear before using these statements to clear the cache, especially in the production environment. These have a great impact on performance. After clearing the cache, SQL Server needs to re-read data from the data page. In addition, SQL Server needs to generate a new execution plan. Therefore, you need to be clear about the impact on the production or test environment before cleaning.

　　

Dynamic management object

SQL Server 2005 introduces dynamic management objects, such as DMV and DMF. SQL Server 2008 adds a new object and a new attribute. These are very useful information that can be used to monitor SQL Server, diagnose problems, and monitor performance. It takes a lot of time to study these objects. Here we only list some common ones.

　　

Statistics IO

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

Run the following code to enable this option:
Set statistics io on;
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo. Orders
WHERE orderdate> = '123'
AND orderdate <'20140901 ';

 
Finally, we can get information 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 read-ahead reads 0.

From the output information, we can see the number of times in the execution plan to get the table (Scan count); the number of times to read the cache (logical reads ); read-ahead reads (physical reads); read-large objects (lob physical reads, log read-ahead reads ).
Use the following statement to disable this option:
Set statistics io off;

 

Count running time 

Statistics time is a session option used to return the CPU clock TIME. It returns the time for syntax analysis, compilation, and execution. To use this option, you should first clear the execution plan cache.

Dbcc dropcleanbuffers;

Dbcc freeproccache;

Run the following statement to open the corresponding options:

Set statistics time on;

Run the following statement:
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo. Orders WHERE orderdate> = '20160301' AND orderdate <'20160301 ';

Obtain the following information:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 4 ms.

SQL Server Execution Times:

CPU time = 46 ms, elapsed time = 544 ms.

Obtain the CPU clock time, Compilation Time, and running time when the statement is executed. Run the following statement to disable this option:

Set statistics time off;

This option is useful when you need to analyze the performance of a separate statement. When you use the batch processing mode to run a statement, the measurement is different. Save the value of the SYSDATETIME function before the query and write it into a table. 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 operating system version. This function will call the WindowsAPI GetSystemTimeAsFileTime. When you need to calculate the time, you can run the request statement repeatedly and record the time required.

　

Analyze execution plan 

An execution plan is a work plan generated by the SQL optimizer to process a given request. It contains the operator used in this request for traditional Chinese medicine. Some operations may be performed multiple times. Some planning branches may be executed in parallel. In this work plan, the optimizer determines the order of the tables involved in the statements, the indexes used, the query methods used, and the algorithms used. In fact, the optimizer selects an optimal among multiple execution plans, with the least resource consumption. Frequent generation of execution plans also takes time. Therefore, SQL Server estimates the threshold time required to generate an execution plan based on the data volume. The execution plan generation time does not exceed the estimated threshold time. Another threshold value is calculated based on the consumed resources. If the resource consumption of a work plan is lower than this threshold, the optimizer considers it good enough to stop optimizing the use of the plan.

　　

Graphic execution plan

SSMS allows us to view a graphical execution plan (shortcut: Ctrl + L ). Note that the query is not running when you view an execution plan. Some metric values can only be obtained after running (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> = '20160301' AND orderdate <'20160301'

Group by cube (custid, empid, shipperid );

 

This statement is used to query all possible aggregate values. The aggregation attributes are custid, empid, and shipperid. 1

 

Figure 1

　　

Note: When this execution plan occupies a large screen space, you can click the button "+" in the lower right corner and drag the mouse to view the desired area.

An execution plan is a tree structure composed of some operations. Data flows from the suboperation to the parent operation. The order of this structure is from right to left, from top to bottom. In this example, the computation first starts from the clustered index, followed by the Operation winding operation-Table Spool

Note that there is a percentage next to each operator. This table value represents the percentage of resources occupied by the operation throughout the execution process. This value is only the value estimated by the optimizer. The optimization of SQL statements should be performed on those operations with a large percentage. When you move the mouse up, there will be a color change prompt box. One value is Estimated Subtree Cost. At the top, the resource overhead of the entire operation during the most workshop operation. 2

Figure 2

　　

Note that these values are only the value estimated by the optimizer. The optimizer will use this value to compare with other estimates and then select an optimal execution plan.

In another better case, you can generate execution plans for multiple statements at the same time to compare them. For example, the following statement:

--1
SELECT custid, orderid, orderdate, empid, filler
FROM dbo.Orders AS O1
WHERE orderid =
(SELECT TOP (1) O2.orderid
FROM dbo.Orders AS O2
WHERE O2.custid = O1.custid
ORDER BY O2.orderdate DESC, O2.orderid DESC);
--2
SELECT custid, orderid, orderdate, empid, filler
FROM dbo.Orders
WHERE orderid IN
(
SELECT
(SELECT TOP (1) O.orderid
FROM dbo.Orders AS O
WHERE O.custid = C.custid
ORDER BY O.orderdate DESC, O.orderid DESC) AS oid
FROM dbo.Customers AS C
);
--3
SELECT A.*
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (1)
O.custid, O.orderid, O.orderdate, O.empid, O.filler
FROM dbo.Orders AS O
WHERE O.custid = C.custid
ORDER BY O.orderdate DESC, O.orderid DESC) AS A;
--4
WITH C AS
(
SELECT custid, orderid, orderdate, empid, filler,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY orderdate DESC, orderid DESC) AS n
FROM dbo.Orders
)
SELECT custid, orderid, orderdate, empid, filler
FROM C
WHERE n = 1;

Copy code

 

Their query results are the same, but their execution plans are different. A percentage at the beginning of each Execution Plan indicates the percentage of overhead of this statement for all statements. In this example, we can see that the proportion of the first statement is 37%, the proportion of the second statement is 19%, the third statement is 30%, and the fourth statement is 14%. From this result, we can roughly determine that the efficiency of the fourth statement is higher.

When you place the cursor over the operator, a yellow prompt box is displayed.

Figure 4

　　

The following measurements are displayed in the prompt box:

• Operator name and brief introduction
• Physical Operations:Physical operations inside the computer
• Logical operation:Logical operators that match physical operators, such as the Inner Join operator. Logical operators are listed behind physical operators, both of which are at the top of the tooltip.
• Number of returned rows:Number of rows returned by the Operation
• Estimated I/OOverhead, estimated CPUOverhead:This data can be used to estimate whether this operation causes a high CPU or I/O overhead. Generally, Sort operations will cause a high I/O overhead.
• Estimated number of executions and number of executions:Estimate the number of executions and the number of actual executions of the operation. This data helps you find better statements to execute.
• Estimated execution Overhead:Overhead of the query optimizer used to perform this operation
• Estimated subtree Overhead:Total overhead of all operations performed by the query optimizer before this operation and in the same subtree
• Number of rows generated by the operation:Estimate the number of rows generated by the operator. In some cases, the difference between the actual number of rows and the estimated number of rows can be used to determine the advantages and disadvantages of an SQL statement.
• Estimated data size:The estimated size (in bytes) of the row generated by the operator ). You may wonder why the actual number of rows is not displayed in the execution plan because there are variable-length data types in the data rows.
• Actual rebinding and rewinding:The sum of the data is related to some specific operations (non-clustered winding, remote requests, number of rows winding, sorting, table winding, Table value functions, assertions, filtering, etc ). Measurement information is collected only during nested queries at the internal layer. Otherwise, Rebinds is 1 and Rewinds is 0. The data indicates that the inner Init method is called. The combination of rebinding and rewinding should be the sum of the number of rows obtained from external connections. Re-binding means that one or more related connection parameters have changed and need to be re-estimated. Rewinding means that the related parameters are not changed, and the previous internal result set can be reused.
• Bottom information:Displays related object names, outputs, parameters, and so on.

　　

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

　　

Execution Plan in text format 

You can view the execution plan in text format by setting. You can set the SHOWPLAN_TEXT option to achieve this goal as follows:
SET SHOWPLAN_TEXT ON;
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dB. Orders WHERE orderid = 280885;

View the Execution Plan (CTRL + L) to get the following results:
(1 row (s) affected)
StmtText
Bytes -----------------------------------------------------------------------------------------------------
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dB. Orders WHERE orderid = 280885;

(1 row (s) affected)

StmtText
Zookeeper -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| -- 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 the execution plan, from the inner branch to the outer branch, from top to bottom. But here we can only see the Operator name and parameters. Run the following statement to disable this option:
SET SHOWPLAN_TEXT OFF;
If you want more detailed execution plan information, use the SHOWPLAN_ALL option to view the execution plan, and statistics profile option to view a specific execution plan. SHOWPLAN_ALL writes the execution plan information to a table. The estimated values include StmtText, javastid, NodeId, Parent, PhysicalOp, LogicalOp, Argument, Defi nedValues, and EstimateRows, estimateIO, EstimateCPU, AvgRowSize, TotalSubtreeCost, OutputList, Warnings, Type, Parallel, and EstimateExecutions.
Open this option using the following statement:
SET SHOWPLAN_ALL ON;
Run the following statement:
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dB. Orders WHERE orderid = 280885;
The result is as follows:

Figure 5

　　

Run the following statement to disable the option:
SET SHOWPLAN_ALL OFF;
The statistics profile option generates an actual plan. When this option is set to ON, the displayed result is similar to when SHOWPLAN_ALL is set to ON. However, there are two additional attributes Rosw and Executes, indicating the actual number of rows and the number of running rows.

The statement is as follows:
Set statistics profile on;
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo. Orders WHERE orderid = 280885;

Unset:
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 customers or colleagues, you will find it inconvenient to use text format information. SQL Server 2008 allows the return of execution plan content in XML format, which is very conducive to application code processing. The execution plan in xml format generated using SQL Server 2008 is displayed as a graphical result with the suffix ". sqlplan.

 

The code for enabling this option is as follows:

SET SHOWPLAN_XML ON;

 

Running statement

SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo. Orders WHERE orderid = 280885;

 

The running result is as follows:

Figure 6

Click the xml file. The execution plan in the graphic format is shown in Figure 7:

Figure 7

Use the following statement to disable the option:
SET SHOWPLAN_XML OFF;

In order not to affect the output results of other statements, we recommend that you use code similar to the following to view the results:
Set statistics xml on;
GO
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo. Orders WHERE orderid = 280885;
GO
Set statistics xml off;

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