Contact us

Madeira Data Solutions

Your Data, Our Solutions

Track My Query Utility

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.

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.

TrackMyQueryMainMenu

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:

CXPacketMonitoring

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:

SysDMExecQueryProfilesMonitoring

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:

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.

14 responses to “Track My Query Utility”

  1. Nelson says:

    Nice. Very nice…

  2. Idan says:

    Sounds usefully.
    Great job Matan.

  3. Ami Levin says:

    AWSOME!!!
    Nice job

  4. Srikanth says:

    Awesome.

  5. Bill Bergen says:

    Looks like a great tool but….and there is always a but….I have no idea what to do with the “plan” component which I am supposed to download (a single download of all components would be really helpful) and since I did not know what to do about the images component the tool consistently fails with the following message …. also will the tool work on a sql server fail over cluster?
    on a 2008R2 sp2 cluster (test connection worked) … clickingon cxpacket and showplan (spid number not included)
    Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01: trackmyquery.exe
    Problem Signature 02: 1.0.0.0
    Problem Signature 03: 542bc6ba
    Problem Signature 04: mscorlib
    Problem Signature 05: 4.0.0.0
    Problem Signature 06: 50484bd7
    Problem Signature 07: e4c
    Problem Signature 08: 5a
    Problem Signature 09: System.FormatException
    OS Version: 6.1.7600.2.0.0.256.4
    Locale ID: 1033
    Additional Information 1: 0a9e
    Additional Information 2: 0a9e372d3b4ad19135b953a78882e789
    Additional Information 3: 0a9e
    Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

    Read our privacy statement online:
    http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

    If the online privacy statement is not available, please read our privacy statement offline:
    C:WINDOWSsystem32en-USerofflps.txt

    thanks for your time and your help

  6. Matan Yungman says:

    Hi Bill,
    Thanks for the compliment!

    As the tool is currently written, you need to download the “HTMLQueryPlan” directory and put it under “c:temp” on your computer. That’s where the utility executes an external component which visualizes the execution plan.
    I continue developing the tool with a few other guys on GitHub, and hopefully we will be able to overcome this issue soon.

    Regarding working against a Failover Cluster, The tool doesn’t have a problem with that.

    Let me know if you have any other questions.
    Matan

  7. Mike Holbrook says:

    Can’t download the utility or source code. Could you send to my email?

  8. John Frum says:

    Chrome blocks download of the exe, says its malicious.. false positive, or dodgy file?

    • Matan Yungman says:

      Hi John,
      This is definitely a false positive.
      I compiled the code and created the file myself.

      Matan

  9. Bill Bergen says:

    Hi Matan
    Is there a more recent version…no matter what I put in cluster or stand-alone the test works but when I say use either the packet or dmv I always get the same error which is shown below

    Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01: trackmyquery.exe
    Problem Signature 02: 1.0.0.0
    Problem Signature 03: 542bc6ba
    Problem Signature 04: mscorlib
    Problem Signature 05: 4.0.0.0
    Problem Signature 06: 50484bd7
    Problem Signature 07: e4c
    Problem Signature 08: 5a
    Problem Signature 09: System.FormatException
    OS Version: 6.1.7600.2.0.0.256.4
    Locale ID: 1033
    Additional Information 1: 0a9e
    Additional Information 2: 0a9e372d3b4ad19135b953a78882e789
    Additional Information 3: 0a9e
    Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

    Read our privacy statement online:
    http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

    If the online privacy statement is not available, please read our privacy statement offline:
    C:WINDOWSsystem32en-USerofflps.txt

  10. Bill Bergen says:

    Please see my most recent comment….any help would be appreciated….thanks in advance….

Leave a Reply

Your email address will not be published. Required fields are marked *