Track Query Tuning Progress and History in Plan Explorer
As you tweak and test queries, it’s awfully useful to see how the performance has changed.
Plan Explorer has a way to do this, and it allows you to make notes as you go. This used to be a ‘Pro’ feature which cost money, but SQL Sentry has now made Plan Explorer free — good news, y’all! This is a client tool and there is no need to change the SQL Server itself to use it.
Here’s a little animated gif showing how it works:
Don’t lose your work! To save all versions in your history with plans, notes, etc. click ‘File’ and save, and save a whole .pesession file.
And to be clear, I could have run both of these statements in one go and compared the results of both statements in a single pane. I just wanted to simulate changing the query slightly to test how it ran afterward, without having to make the viewer wait while I typed out that whole index hint.
Some other observations I made:
- Ctrl + E keyboard shortcut will run ‘Get Actual Plans’.
- The ‘A’ in the type column probably stands for ‘Actual Plans’. If you do an estimated plan, you get an ‘E’ there.
- If you don’t see the History panel by default, open it up using the ‘View’ menu.
- The ‘Total Time’ column is the same as ‘Duration’ in the results pane. It’s handy to have it there!
- You can also add ‘Actual Rows’ to the history pane if you want to see that. Right click on the headers and get the Column Chooser.
- You can delete items in the history panel, just right click and choose ‘Delete’.
- You can also save individual history items by right clicking and choosing ‘Save’.
- If you notice the moving lines in the query plan the second time the query runs, that’s because I left the ‘Live Query Profile’ feature on, which is enabled by default. You can toggle that on and off under the ‘Get Actual Plan’ button (hit the arrow to the right).
- I haven’t notice a measurable difference in duration when running queries with ‘Live Query Profile’ on and off in my test environment so far. (Doing extra work is going to have more impact on the server no matter what you’re doing. I’m just saying it doesn’t seem to skew my measurements.)
This is not a sponsored post, I just think this is a cool feature and I’m glad it’s now free. If you’d like to check out Plan Explorer, it’s over here.