Welcome back to another edition of T-SQL Tuesday. This month’s edition is hosted by Mikey Bronowski (b|t) who asks us to write about a tool that has helped you the most over the years.
I’m a query tuner at heart, so for me the answer is an easy one: SentryOne Plan Explorer. Disclaimer: Yes, I work for SentryOne now, but I’ve been a devoted Plan Explorer’s user for most of its 10 year history, having discovered it back in its early days.
This blog isn’t going to tell you all about how awesome Plan Explorer is. There’s tons of existing resources out there to help you get up to speed (like this Beginners Resources PDF). Instead, I’m going to share a more esoteric tidbit that I only recently learned.
In Plan Explorer, you can use it as a query performance tuning environment (rather than a query development environment), iterate through multiple changes, and go back in history to compare and contrast. Then if you want, you can save your working session out as a .pesession file. This allows you to share everything you’ve done, give it to a colleague, or whatever.
For this blog post, I’m going to be using the .pesession file from my Advanced Plan Explorer Usage presentation. First, here’s the history from the master file and you can see that I have 8 iterations.
So What’s The Big Reveal?
So here’s the tidbit that I only recently just learned, that frankly kind of blew my mind but didn’t surprise me at the same time.
.pesession files are really zip files.
This means you can extract the contents!
Once you’ve extracted everything, you can open up individual queryanalysis files inside Plan Explorer.
In the above example, now you see that I have the 1st, 4th, and 7th entries open from my original .pesession file.
How Is This Useful?
Honestly? I’m not so sure that this is really that useful to the everyday query tuner.
Best use case I can think of is if you’ve been working on something, have like 30 iterations, but only want to send the 23rd one over to your colleague, because that’s the one that worked best in the end. Like I said earlier, this is certainly an esoteric tidbit. But hopefully was entertaining enough.
Thanks for reading!