Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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