[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