New in version 2.3 is the extended cached execution plan view. This screen has been redesigned to allow for drilling down from execution plans to their constituent operators and their details. This workflow exists with 1 task in mind – performance tuning.
This screenshot displays an average performance tuning workflow – execution plans are ordered by their execution time in the top pane. The plans operators are listed in the middle grid and then the operator details in the bottom pane:
The green arrows indicate the drilling down from a list of execution planes to their operators and details of each.
The 4 tabs at the top of the screen let you start by selecting to see execution plans that are either the slowest, most recent, have expensive CPU or expensive IO/Disk cost.
The middle pane’s list of operators may be the biggest time saver on this page as the operators are ordered by estimated cost %. This is the same as SQL Management Studio’s operator cost but instead of being displayed in an unordered hierarchy tree view they are listed here by cost. No more having to follow graphical execution plan trees by the thickness of their line back to the offending leaf node:
Each grid row when combined with the details pane at the bottom of the screen display the equivalent data to what Management Studio displays in it’s large tooltips.
Obviously taking corrective action based on the data presented above is not done for you but once you modify your schema or code to attempt to correct the problem you can just hit refresh at the top of the screen and see what difference your changes have made.
The new version of this screen also offers several other new features outside of the core performance tuning workflow. The T-SQL statement is fully available along with it’s XML execution plan and a button to automatically start sql management studio and load the execution plan into the graphical explorer.
Each operator’s XML execution plan can also be viewed by opening the ‘operator XML’ tab.
Other tools that miniDBA has to offer compliment this screen when it comes to performance tuning, such as the server and database level healthchecks which will notify you of problems in your T-SQL and the index useage screen to help with index design.