SQL Execution Plan parsing (1)-execution plan BASICS (2)

Getting started

If you are not SysAdmin, dbcreator, or db_owner, you need the permission to view the execution plan.

GRANT SHOWPLAN TO [username]

The first example is very simple. Open Management studio and type the following query

SELECT *
FROM [dbo].[DatabaseLog];

Click the display estimated execution plan button on the action bar to view our first estimated execution plan.

Figure 1-1

We will explain it later. Now we have another actual execution plan. Unlike the estimated execution plan, the actual execution plan does not represent the computing result of the optimizer, it represents what actually happens during query execution. The two are usually the same, but sometimes different, depending on the storage engine's modifications to the execution plan. Click the include actual execution plan button to make it active and click execute.

In this example, the estimated execution plan is consistent with the actual execution plan. You will see a lot of select icons on the left in the future, which can be ignored. It indicates the final result and format (formatting) from the relational engine ).

The table scan icon on the right is the first and easiest icon to search. We usually read a graph execution plan from right to sit and from top to bottom. Icons mean data transmission between operators.

In this example, only one table scan operator produces a result set. The width of the arrow indicates the size of the data volume. The wider the arrow indicates the data of more rows. This visual method may give you a wrong impression. You can hover your mouse over the arrow to view the specific number of data lines it represents. There is a percentage under each icon, which represents the overhead of the operator relative to the entire query. Note that this ratio is not an actual number, and even 0% has a small overhead. A Percentage indicates the overhead percentage of a query in a batch. You can use a batch query to execute multiple queries and obtain the execution plan.

ToolTips

Each icon and arrow have a pop-up window associated with it, that is, tooltips. You can see it by hovering over it. You can see 1-3 when hovering over the select icon in the prediction execution plan.

The optimizer generates the following numbers:

1. Cached plan size -- the number of memory occupied by the Stored Procedure cache for the execution plan generated by the query. This is a useful number when you investigate the cache performance, it can be used to view which execution plans occupy more memory.
2. Estimated Operator cost -- we can see this overhead percentage in Figure 1-1.
3. Estimated subtree cost -- it represents the overhead accumulated from the previous step to this step. Remember to read from right to sit. This number is meaningless to the real world. It is just the arithmetic evaluation value that the query optimizer uses to determine the operator overhead. It represents how much time the optimizer considers the operator to spend.
4. Estimated number of rows-calculated based on statistical data
5. The preceding information includes the SQL statement.

The tooltips of table scan is 1-4.

Figure 1-4

Logical operation represents the computing result of the optimizer, that is, what operations should be performed when the optimizer considers the query to be executed, and physical operation represents what operations actually occurred. In general, the two operations are the same, but there are also exceptions. The second chapter has more discussions.

Then there is the estimated overhead of I/O, CPU, operator, and subtree. subtree is the part of the execution tree until now. All estimates are based on the statistical data of columns and indexes. I/O cost and CPU cost are not actual operators, but overhead numbers allocated by the optimizer in computing. These numbers help determine whether the overhead is I/O-intensive or CPU-intensive. You may notice that operator cost and subtree cost are the same, because table scan is our only operator. The Boolean value of ordered indicates whether the data is in the sorting status. Nodeid node sequence number, which ranges from left to right, although the best way to read is from right to sit.

Operator attributes

Right-click the icon and select Properties to see more information. Most of the information we have encountered, but there are still some new ones.

Figure 1-5

Defined values displays the information appended to the process by this operation. This information may be part of the basic query. In this example, it is the selected column, it may also be the internal value created during the query process. For example, some tags that determine the integrity of internal references may also be placeholders for aggregate function counts. Estimated rebinds and rewinds are the number of times Init () is called in the execution plan. Foreced index, which is true when a query hint is used to force an index. SQL Server provides some functions through query hint, allowing you to control how to execute a query. The concept of noexpandhint is basically the same as that of forced index, but it is used for index view.

Text execution plan and XML execution plan

To obtain the text execution plan, add a command at the beginning of the query.

SET SHOWPLAN_ALL ON;

Close the text plan and use

SET SHOWPLAN_ALL OFF;

Obtain the actual text plan and use

Set statistics profile on
And
Set statistics profile off

Obtain the actual and estimated XML execution plan, and use

Set showplan_xml on
And
Set showplan_xml off

PS: more details about the text and XML execution plans are skipped.

Use SQL Server Profiler to automatically capture execution plans

In the development phase, we can capture execution plans in the above several ways. However, we may not be allowed to do this on the testing server or running server. in SQL server2005, we can use profiler to capture the XML execution plan while running the query.

Profiler is a powerful tool that allows you to capture event data. Profiler events can be manually tracked through the GUI, you can also define your own tracking, and run automatically at a specific time. The trace information can be viewed on the screen or sent to a file or database table.

Execution Plan event

• Showplan text: this event is triggered during each query. The generated plan type is the same as the showplan_text Declaration and is a subset of the content generated by showplan XML.
• Showplan text (unencoded): Same as above. However, this event displays information in string format rather than binary mode.
• Showplan all: this event is triggered during each query. The generated plan type is the same as the showplan_all statement.
• Showplan all for query compile: This event generates the same data as showplan all, but this event is only triggered during query compilation.
• Showplan statistics profile: This event generates the actual execution plan in the same way as the T-SQL command statistics profile. The information it generates is a subset of the statistics XML command or the showplan XML statistics profile event.
• Showplan XML: this event is triggered during each query and generates an estimated execution plan, which is the same as showplan_xml.
• Showplan XML for query compile: Same as above, triggered only during query compilation.
• Performance Statistics: similar to showplan XML for query compile, this event not only captures execution plans but also performance measurement data. This event only captures the XML output of the Child event class defined in it. This event is triggered when the cache is started for the first time, during compilation, during recompilation, and when it is removed from the cache.
• Showplan XML statistics profile: This event generates its actual execution plan when each query is running.

Using showplan XML or showplan XML statistics profile to capture all execution plans will undoubtedly increase the burden on the server. These are not lightweight events. Even if showplan XML for query compile is not commonly used, it may cause small performance degradation. Exercise caution.

Capture the showplan XML trace

Start profiler-> Create a trail and connect to the server-> switch to the events selection tab-> check the show all events check box-> click the plus sign to expand the Performance Department-> check the showplan XML event

Now we can capture showplan XML events in profiler. It is useful to capture other basic events at the same time, such as RPC: Completed, SQL: batchstarting, and SQL: batchcompleted.

Figure 1-6

After the showplan XML event is selected, the third tab appears, where you can specify to send the output XML to an independent file.

Click Run to start the trail. Similar results can be obtained when a query is executed.

Figure 1-7

The textdata column is the actual XML execution plan. When selected, a graphical execution plan is displayed in the window below. You can right-click and select extract event data to save the plan to another file.