[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: http://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 (3)

  1. Gorden says:

    How can I just print the string instead of doing a DumpObject?

  2. rafarah says:

    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”

  3. nothize says:

    Hello, a nice script that help to have a quick start!

    I’m not sure whether my Windows version or Windbg differ, the script above needs some small changes to work out perfectly.

    ——————

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

       $$ aS would terminate on comma

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

       .block

       {  

           ~* e $$><${ScriptName}

       }

       $$ ad on non-existing alias result in strange output, so move it to here

       ad ${/v:ScriptName}

    ——————