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 collection of all weight statistics with correct weight time and also signal time. When clicking on weight item, a tooltip will pop up with the description of the cause of weight time. A recommendation for resolving the issue and optimizing performance, and with links to a suggested solution center regarding that issue.
Columns tab-- this tab is used for reviewing all columns used in the plan and their associated operations and indexes. The Operations Tree tab is used for analyzing query operation tree details of operations that are used in the selected query such as cost, subtree cost, actual and estimated rows, and CPU cost.
If a query contains a joint statement, the corresponding diagram will be generated in the Join Graph tab. Join graphs are used to graphically display relationships between database tables involved in the execution plan.
Next, is the XML View tab. The XML View tab is used to display the query execution plan in XML format. When it comes to XML, almost every grid in ApexSQL Plan can be exported into HTML and XML formats for later use. The export options are available for every grid by right-clicking and selecting the desired option in the context menu.
Another optional execution results tab can be included from the options menu by checking the show query execution results field. This tab shows the actual result for each chosen statement from executed query like the resulting table from select statements. Other than all the previously-shown tabs, ApexSQL Plan has two more special tabs that are used for query analysis-- Query Performance and Query Store tabs.
To activate the Query Performance tab, click the Performance button in the main ribbon bar. Using the Query Performance tab, it's possible to get a list of recently-executed queries with longest average execution times by chosen criteria from SQL Server query collection with all necessary details provided.
Right-clicking on a statement in the Performance tab will show a context menu with options to save plan for comparison directly from this tab and to view execution plan where all the related information to the selected statement will be shown in a new top tab of the main application window. The query store is another feature in ApexSQL Plan which is placed under the Query Store tab.
With this feature, the execution data collection can be reviewed. Activating the Query Store tab can be done by clicking the Query Store button in the main ribbon bar and selecting the Start Query Store button. The basic requirement for the Query store feature is SQL Server 2016 version and higher.
With implementation of Open Query store, backward compatibility was established for all SQL server versions from 2008 to 2016. SQL query collection in the Query Store tab is displayed in rows. Each row represents a SQL query with a select statement.
While hovering the mouse over a row, the tooltip will show up with the whole query text inside which can be copied then with the Copy button in the lower right corner of the tooltip. Right-clicking on a statement in the Query store will show the context menu with options to save the plan for comparison directly from the store and to view the execution plan where all the related information to the selected statement will be shown in a new top tab of the main application window.
Another item with the Query store feature is the Dashboard tab. This tab contains graphic representations of query execution statistics with the possibility to set up to 24-hour intervals for each graph separately. The third view in the query store is the property's view, which contains settings like data flush interval, maximum plans per query, and statistics collection interval to help manage and organize data in the store.
At the bottom of this view are buttons to apply changes, clear collected data, flush data to disk, restart query store, and restore default settings. In case of the Open Query store usage, apply flush and restore are not available.
This concludes this video introduction to ApexSQL Plan. Thanks for watching. For more information, please visit ApexSQL.com.