[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}