At my house, the holiday season is generally a time to slow down a bit and focus on friends and family. No matter how or what you celebrate, time spent with loved ones is time well spent.
In this post, time is going to be a recurring theme. Time is a resource that you can never get back. That is just a simple immutable truth. Your time is the most valuable resource you have, and I hope to help bring that into focus for you. In fact, I am honored and humbled by the fact that you’re choosing to spend your time reading this post right now.
What does all this have to do with Plan Explorer? That’s an easy one. At the root of the thousands of hours poured into building Plan Explorer, time is also a recurring theme. How much time does a query take to execute? How much CPU time did it use? How much time can you shave off by tuning it? Most importantly, how much of your time can be saved by using it?
With the tone set, let’s move on to what we’ve been up to for this release of Plan Explorer.
Live Query Profiling
With SQL Server 2014, Microsoft delivered a way to let us understand the execution of a query at runtime at the individual plan operation level. At SQL Sentry, we immediately started thinking of ways that we could visualize a running plan in Plan Explorer.
Microsoft further validated the need for that visualization with a feature they’ve added to SSMS for SQL Server 2016. Most of you have likely heard about or even used this already. If you haven’t, you can learn about it here.
With Plan Explorer 2.8, we’re ready to release the live plan execution support that we’ve been working on, and we’re providing it with the FREE edition of Plan Explorer, as well as PRO.
To enjoy the feature, you’ll need to be running SQL Server 2014 (preferably SP1 or better) or SQL Server 2016. You can enable the setting in your preferences:
Note that you can change the polling interval here as well. The default is 500ms, and the minimum is 100ms.
Or you can enable the setting when you collect an actual plan from the toolbar:
If you have live query profiles enabled, and you happen to be querying against SQL server 2012 or earlier, there is no need to worry. Things will simply behave the way they did before this feature was added. Plan Explorer knows when it isn’t able to gather the profile data at runtime based on the SQL Server version.
As always, we’re including some additional details to help save you even more time as you’re using Plan Explorer to watch a query plan execute live.
- Progress is tracked at the statement level
- Actual rows processed over estimates are highlighted
- Note in the above image that we’re continuing to increase the percentage of completion beyond the estimate as well
Being able to watch a plan execute like this can be a great way to learn more about how SQL Server processes queries. Just in UA testing the feature I’ve learned a lot about how plans behave at runtime that I wouldn’t get to see if I only had the post execution (actual) plan. It was really great to see similar functionality in SSMS as well, showing that Microsoft agrees with us on the value of this ability.
As we’ve often heard in the office as this was in development, it’s also really cool to watch!
Query Profile Replay
But it gets even better. When I walked into Brooke’s office and asked “can we record the plan profile, and play it back like a movie?”, two things happened. I got a “thousand yard stare” for a minute, and then work began on one of the coolest things we’ve built.
As we collect profile data for a plan run, we store it in a format that can be persisted, and streamed back. This creates the stage for what I have nicknamed “PlanTube”.
You can pause, play, skip back, skip forward, speed up, slow down, and move the run to anywhere in the timeline. Here’s a little video I made of me working with a PE session via replay:
Any Plan Explorer session that includes live profile data can be replayed without any connection to the SQL Server instance. This creates some great time-saving opportunities. If you have a long running query, and you don’t have time to sit and watch it, you can let it run, and come back later to watch how it ran – at a faster speed, like skipping the commercials. If you’re working with someone else, you can save the session, and send it to them. They can watch your query run with no presence or VPN required.
It’s All About Time
As I considered the true reason that we build our software, I kept coming back to the late nights on the phone or driving in to a data center in order to resolve an issue or meet a deadline. I thought about my children, who are all now adults themselves, and the time I missed while they were growing up due to inefficient methods for solving problems at work.
Cool though they may be, at their core these features are designed to save you time, and improve your quality of life. If I can close out 2016 knowing that someone out there got to go home instead of waiting on a set of queries to finish, I’ll call this year a massive success.
Just for Fun
Finally, in an effort to paint a clear picture. I put this little parody together for you. Some of you might recognize the tune, and some of you might not, but as data management professionals, I think we can all relate to the message.
Until next time,
-JRH
