Hello. And welcome to the ApexSQL Plan overview video. ApexSQL Plan is a simple but powerful, free tool used for query execution plan analysis and query optimization. ApexSQL Plan enables users to review actual or estimated query execution plans and optimize SQL queries.
Other than that, ApexSQL Plan can be used to export various query execution plan results to HTML and XML reports. ApexSQL Plan consists of two main components-- one, a standalone Windows application used as a manual query execution plan viewer and for query optimization, two, a SQL Server Management Studio add-in that allows opening and viewing query execution plans in ApexSQL PLAN directly from SSMS.
In order to start the execution plan analysis, let's create a new session and open a SQL file from a desired location. It is possible to choose a SQL file from the recently-used file list which orders used files chronologically. Once opened, the query script can be checked for errors. At the bottom of the window, the error list panel shows if there are errors and where they can be found.
The error list refreshes in real-time while typing inside the Query Editor. This panel can be disabled or enabled from the context menu of the Query Editor. After checking for errors, it's time to view the query execution plan. In ApexSQL Plan, there are two main options related to viewing query execution plans-- estimated and actual plan.
An estimated execution plan generates SQL server query plan in the form of a simulation and doesn't affect the database or the targeted SQL server. It uses estimates to predict execution behavior. To view the estimated execution plan of the open query, click the Estimated button and the database connection dialog will appear where the desired database needs to be selected.
Select the required SQL server and a database, which will be used for further query execution plan analysis. Click the OK button and wait for the estimated query execution plan to be created. Although, it is useful for analyzing query behavior, because it is not actually being run, it might not be accurate in some scenarios.
An actual execution plan is the resulting SQL server query plan when the query is executed. It has greater reliability, because it doesn't use estimates as it is based on actual execution. To view the actual execution plan of the open query, click the Actual button.
Once the actual execution plan is created, the execution plan tab will be shown by default. The actual execution plan is more useful in query analysis and troubleshooting. Because it quantifies execution flow and provides more accurate statistics and information. The Execution Plan tab offers the main view of the created actual or estimated execution plans or with individual parts of an execution plan such as nodes and connectors.
On the top of the Execution Plan tab, the statement grid is shown. If queries with multiple statements are executed, their plans will be listed in the same section-- one below another separated as statements. Right below the statement grid is where the execution plan view is located.
This section is used for viewing the execution plan by reviewing the highest cost operations within the execution plan and viewing high rows data size usage with connector lines of varying thickness. In the case where there is a missing index issue with the desired query, a message will appear in the execution plan header. Hovering the mouse cursor over the message will show a tooltip with a missing index creation icon on it.
Clicking on the icon will open the create index window with statements for dropping or creating indexes, which can be toggled by clicking on drop index and create index checkboxes. Statements can be further edited. And after creating the desired statement, it can be executed by clicking the Execute button. And the new index will be created.
One very interesting feature of ApexSQL Plan is the execution plan comparison feature. Let's execute a query with one version of some active project. The Execution Plan tab will become active. Right-clicking on the execution plan view, the context menu will appear showing the save for comparison option.
To compare the first version with another version of an active project, execute the second version for the new resulting execution plan. Right-clicking the context menu and choosing compare with saved option will put the previously-saved and current execution plans side by side for comparison.
Clicking on the corresponding nodes on both plans, the Property tabs for both nodes will open and show values for observation and comparison. There is also the node search option called with Control F keyboard combination within the execution plan panel. This search option includes selection of available nodes for quick finding.
Using ApexSQL Plan, it is possible to view live execution plans that allow monitoring long queries during execution in real time. After the live execution plan is activated, an animated execution plan will show progress of the query execution with lines showing data flow and values changing with the flow. The whole execution process will be recorded. So there is no need to monitor the process for the execution analysis, use of timeline slider can set plan view to any moment of the process.
In case of multiple query executions, it is possible to compare statistics of several executions of actual or live plans using the History tab. Open execution history by clicking the History icon in the main toolbar. The Execution History tab will show up with a list of executions. Check the items from the list and confirm with compare. The statistics comparison will be shown in the main view.
The Next tab is commonly-used as the I/O Reads tab. This tab is used for reviewing the number of logical reads including LOB, physical reads including read ahead and LOB, and how many times a database table was scanned. Next, is the Weights tab.
In this tab, weights for executed queries can be analyzed with a