In the next version of ApexSQL Plan 2017 we will be adding lots of new features and improvements related to Query store support and Stored procedure analysis along with other minor features.
Stored procedure analysis
This feature will allow users to load a specific stored procedure from a database and perform detailed analysis for potential issues with that procedure.
The selection of the desired stored procedure, will be possible in the by selecting “Profile” or “Lazy profile” buttons from the Stored procedure analysis group in the main ribbon bar.
After pressing the OK button in Stored procedure connection window, a new dialog window will appear where the user needs to enter the values for all the following parameters:
- Parameter (Parameter name taken from the stored procedure or function)
- Data type (Data type taken from the stored procedure or function)
- Value (Specify the parameter value or select the default value)
- Use default (Select to use the default value as defined in the profiled object. If the default value has not been defined, it must be entered manually)
Once the parameters are added the stored procedure analysis results will be displayed in the following tabs:
The Summary tab will show the slowest statements and any child objects that were called. The min/max parameter values and where they occurred will also be shown in the Summary tab in the separated grid with the column names “Parameter information”, “Minimum value information” and “Maximum value information”:
The Script view tab will show the profiled object and any child objects that were called as well as a visual indication of how long each statement execution took compared to the entire execution time. It will also show how many times a particular statement was executed.
The Detail view tab will show every statement that was executed, how long each execution took and the number of times a statement was executed:
The Parameter history tab will show a list of every parameter that had a value change and where that change occurred along with the previous value:
Along with the aforementioned tabs, additional Stored procedure options will be added in the Options window:
- Generate lazy profiling report (Generate a lazy profiling report when generating profiling information)
- Show only statements with variable modifications (Suppress variables which values don’t change during execution from the report)
- General profiling information (Show general profiling information)
- Step into child objects (Include profiling information from called objects)
- Rollback after execution (Rollback any changes caused by the execution of the procedure)
Among all the aforementioned options is the Generate lazy profiling report option. Using this option, the profiling results will be exported after the code has been profiled. Profiling results will be available for exporting in MS Excel or PDF file formats.
History statistics comparison
With this new feature analyzing execution history of a query becomes a lot easier. This feature will allow comparing previously executed queries in the Execution plan history grid. The comparison process will be possible by selecting the desired version of previously executed queries and clicking the Compare button.
Once the comparison process is done, data by I/O statistics will be shown as a result.
Query store optimization
This feature will be an improvement to the already existing Query store functionality in ApexSQL Plan. Using this feature, the user will be able to get specific Query store results by defining the preferred condition. This means that the Query store data will be available in the Query store tab but as a separated tab that can opened by selecting the Data button.
The main enhancement of this feature is that this it will be available for users that have not yet migrated to SQL Server 2016. Meaning that besides SQL Server 2016 it will also have support for SQL Server from 2008 to 2014 for all editions (Express, Standard, Enterprise).
Query store optimization will enhance the Query store with another tab containing the following Query store properties:
- Data flush interval (minutes)
- Statistics collection interval
- Max size on disk (MB)
- Query store capture mode
- Size based cleanup mode
- Stale query threshold (days)
- Max plans per query
Beside the mentioned enhancements the Query store will be improved with actions that will allow the user to:
- Restart Query store
- Restore Query store settings to default
- Clear Query store content
- Flush data to disk
The aforementioned Query store properties and actions will be shown in a separated tab by clicking the Properties button in the optimized Query store.
Query store dashboard
The Query store dashboard feature will provide deep insight, visualization, and analysis of the data inside the Query Store
The following graphical representation elements of the Query store dashboard will be implemented:
- Top 10 queries based on average duration in the last hour
- Top 10 queries based on execution in the last hour
- Number of unique queries executed in the last hour
- Execution plan diversity in the last hour
- Worker time consumption last 8 hours grouped by intervals
- Logical reads & writes last 8 hours grouped by intervals
- Elapsed query time last 8 hours grouped by intervals
- Queries with multiple execution plans
This feature will be available in the Query store window as a separate tab and will be accessible by clicking the Dashboard button.
The displayed visual diagrams will be available for exporting in JPG, BMP and PNG output formats.
Save and open XML plans
With this feature, query execution plans can be saved in XML format or opened in the same format for performing further query execution plan analysis.
The Profile bars feature will provide a faster and easier insight to the user and their results that are related to cost columns (Total cost, CPU cost, I/O cost). Tooltips will be included for each bar that will show the percentage value of the newly implemented bars. This addition will be available in the Statements grid.
July 7, 2017