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}