Written By: Matan Yungman 01/10/2014
Were you ever in a situation where you ran a query, it ran for a long time and you didn’t know what it was doing? Sure you were..
What if you had a visual indication of where things are stuck in your execution plan, while the query is running?
A few months ago, I published a post about a tool I developed on top of the StackExchange Data Explorer tool. It took me some time, but today it’s finally ready as a utility I can share.
Disclaimer: I’m a DBA, not a developer. Please treat it as a beta version as the utility still needs some work in order to be a real production tool. It’s still a little buggy and not state of the art code-wise and visualization-wise. However, it does the job. In addition, I treat it as an inspiration for the person who can really write code and make it a cooler tool for the community.
When you open the utility, you’ll have two options for monitoring: Using CXpacket waits or using SQL Server 2014’s sys.dm_exec_query_profiles.
In a nutshell, CXPacket waits allow you to see which subtree of a parallel (won’t work for serial) execution plan is currently being processed, and sys.dm_exec_query_profiles allow you to see which specific iterators in the plan are currently being processed.
For more details:
When you use the CXPacket way and type the session id you want to monitor, this is what you’ll see:
What we can understand from this plan is that the the Index Scan of the bigTransactionHistory table is currently working, because it’s under the right Parallelism operator marked in red.
When you use the sys.dm_exec_query_profiles way (remember you need to include the actual execution plan when running the query or collect it using Profiler or Extended Events), this is what you’ll see:
Once you understand how it works and get used to it, you can use this tool to help understand what’s going on with your running queries.
After downloading, you’ll need to put the extracted HTMLQueryPlan directory under your c:temp directory.
As written above, with the help of Justin Dearing, the code is also up on GitHub, so we can make the utility better as a community. If you want to help and make the tool better (please do), join us there!