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.
How does it work?
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:
- Watch my SQLBits session, which explains both methods
- Read my post about using CXPacket waits to track running queries
- Read about sys.dm_exec_query_profiles
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.
Enough Bla Bla
You can:
- Download the utility here
- Download the source code here
- Join us on GitHub and make the utility better for the SQL Server community
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!
Known bugs:
- The app crashes sometimes. Just run it again.
- When you click “show plan” for the first time, the app might show the execution plan of the previous plan it showed. Just wait two seconds and click it again.
The post Track My Query Utility appeared first on .
