Two methods for obtaining execution plans in SQLSERVER

There are two ways to get the execution plan:
1. One is to enable some switches in front of the command to put the execution plan information in the result set. This method is suitable for tuning a single statement in a test environment.
These switches are most commonly used
Copy codeThe Code is as follows:
SET SHOWPLAN_ALL ON
SET SHOWPLAN_ALL ON -- (whether an execution plan is reused, does SQSERVERL feel that the index is missing?), which can only be seen in the XML output
SET STATISTICS PROFILE ON

If SSMS is used, you can press Ctrl + L in lower case to execute your statement and display the execution plan, but no result set is returned.
 
2. Another method is to use event tracking in SQL Trace to track the statement execution plan.
Common events include showplan all, showplan statistics profile, and showplan xml statistics profile.
The advantage of this method is that you do not need to turn on any switch and can be used to directly track application statement execution.
The disadvantage is that he will capture all the statements. If SQLSERVER is busy, the output will be large and may affect the performance. Therefore, use it with caution.
--------------------------------------------------------------------------------
Most of the information collected by the two methods is the same. The XML method returns the results in XML structure. In SQL Trace, the results can display the execution plan and related information graphically.

This output method may be more friendly for simple statements in execution plans. However, for complex execution plans, if they are displayed in graphs, it is difficult for a single screen to be fully placed. Statistics for each step can only be displayed with the mouse clicked, I personally think it is not as easy as text output format.