[WinDbg Script] Displaying Queries/Stored Procedures from Threads Running Managed Code

There’s another script that gives you all queries/stored procedures from SQL Server or Oracle that are stored in the managed heap.

This script is more specific because it gives you the query/stored procedure running in a specific thread. It has the option to scan all threads and to display the queries/sp for each thread.

I could’ve modified the old script; however, this script is simpler and more didactic, illustrating the techniques presented in my previous post. Although it’s simple, it’s very useful. Whenever I’m debugging ASP.NET or SharePoint applications, I see myself digging the dump file to extract this information. Not anymore! With this script I’m saving a good amount of time during the debugging session.

The script is pretty straightforward.

To get the query from the thread you’re debugging, run it:

$$>< path\QUERY_BY_THREAD.txt

To scan all threads use any of these options:

$$>a< path\QUERY_BY_THREAD.txt 1

Or:

~* e $$>< path\QUERY_BY_THREAD.txt

Screenshots:

Source code for QUERY_BY_THREAD.TXT:

$$

$$ =============================================================================

$$ QUERY_BY_THREAD.TXT

$$

$$ Shows the query or stored procedure from a thread running managed code.

$$

$$ Compatibility: Win32/Win64.

$$

$$ Usage: $$><myscripts\QUERY_BY_THREAD.txt

$$ $$>a<myscripts\query_by_thread.txt 1 <-- All threads.

$$

$$ Example: ~* e $$><myscripts\QUERY_BY_THREAD.TXT

$$

$$ Roberto Alexis Farah

$$ Blog: https://blogs.msdn.com/debuggingtoolbox/

$$

$$ All my scripts are provided "AS IS" with no warranties, and confer no rights.

$$

$$ =============================================================================

$$

$$

$$ Check if there's an argument being used. It doesn't matter what the argument is.

.if(${/d:$arg1})

{

    .printf /D "<b>Scanning all threads...</b>"

    $$ Separates the alias definition. Otherwise the next line would be considered part of the alias.

    .block

    {

        as ${/v:ScriptName} myscripts\\QUERY_BY_THREAD.txt

    }

    .block

    {

        ~* e $$><${ScriptName}

   }

}

.else

{

    $$ Gets the SqlCommmand objects from the stack and then parses the output to get the object address only and

    $$ saves it into the obj variable.

    .foreach /pS 1 /ps 2 (obj {.shell -i - -ci "!dso" FIND "System.Data.SqlClient.SqlCommand"})

    {

        $$ If the first line has ".shell: Process exited" we're going to get the word Process.

        $$ It means there're no SqlCommand on this thread, so we can exit.

        .if(0 == $sicmp("${obj}", "Process"))

        {

            .printf /D "<b>\nThread id %x has no SQLCommand object.\n</b>", @$tid

            .break

        }

   

        .printf /D "<b>\nQuery/stored procedure for thread id %x is below:\n\n</b>", @$tid

        $$ OK, now we discard the repeated occurrences and use just the first occurrence.

        $$ We want the _commandText field. The $ptrsize below is for 32/64 bits compatibility.

        $$ Important! Do not use spaces between the operators; otherwise, you'll get an error.

        !do poi(${obj}+(4*@$ptrsize))

  

        $$ We don't need to use the other results.

       .break

    }

}

ad ${/v:ScriptName}

Comments

• Anonymous
  March 04, 2009
  How can I just print the string instead of doing a DumpObject?

• Anonymous
  March 05, 2009
  You must modify the script a little bit in this line: !do poi(${obj}+(4*@$ptrsize)) I don't have a dump file with queries right now, but the idea is to use one more offset and du instead of !do. Example: 0:003> !do 0x027a3a78 Name: System.String MethodTable: 64d508ec EEClass: 64b0d64c Size: 38(0x26) bytes GC Generation: 0 (C:WindowsassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll) String: C:Windows Fields:      MT    Field   Offset                 Type VT     Attr    Value Name 64d52b38  4000096        4         System.Int32  1 instance       11 m_arrayLength 64d52b38  4000097        8         System.Int32  1 instance       10 m_stringLength 64d515cc  4000098        c          System.Char  1 instance       43 m_firstChar 64d508ec  4000099       10        System.String  0   shared   static Empty    >> Domain:Value  00535708:027a1198 << 64d5151c  400009a       14        System.Char[]  0   shared   static WhitespaceChars    >> Domain:Value  00535708:027a1944 << We want to get just the contenct of offset 0xC above. That is: 0:003> du 0x027a3a78+0xc 027a3a84  "C:Windows" Better for Win64 compatibility: 0:003> du 0x027a3a78+(3*@$ptrsize) 027a3a84  "C:Windows"

• Anonymous
  October 29, 2009
  The comment has been removed