A way to retrieve SQL statement from SharePoint native call stack

In some situations of SharePoint slow is caused by SQL SERVER or poor SQL statements. To resolve issue, we usually catch a hang dump and figure out what exactly SharePoint doing at that hanging moment. If the performance issue caused by customized code. There are too much easy way to display SharePoint callstack like !clrstack/!dumpstack etc in windbg. Another way is that you can analysis automatically hang dump by DebugDiag 2.0 If you want to enum all SQL statements from hang dump, you can also execute windbg command like this:

 .foreach(s {!dumpheap -type System.Data.SqlClient.SqlCommand
 -short}){r $t1 = poi(${s}+20);!do -nofields $t1;}


All of above ways are dedicated on managed callstack in SharePoint. As we know, SharePoint service has some COM objects. That's why we have to dispose some SharePoint objects after using. Inappropriate using SharePoint objects would cause memory leak and let your SharePoint site slowly down. For more information please visit: http://msdn.microsoft.com/en-us/library/office/ee557362(v=office.14).aspx

Meanwhile these COM object in SharePoint also access database by OLE DB. We usually can get callstack from SharePoint hang dump like this:

 0:035> kpM
 # Child-SP          RetAddr           Call Site
00 00000000`0e8a9f28 000007ff`76f15280 ntdll!NtWaitForSingleObject+0xa
01 00000000`0e8a9f30 000007ff`7705173e mswsock!WSPRecv+0x66b
02 00000000`0e8aa070 000007ff`76ff3518 ws2_32!WSARecv+0x166
03 00000000`0e8aa120 000007ff`72729c0e wsock32!recv+0x38
04 00000000`0e8aa180 000007ff`60761ae7 dbnetlib!ConnectionRead+0x4fe
05 00000000`0e8aa260 000007ff`60762f2d sqloledb!CDataSource::ConnectionTransact+0xf7
06 00000000`0e8aa2e0 000007ff`607bac34 sqloledb!CDBConnection::SendPacket+0x25d
07 00000000`0e8aa3a0 000007ff`60754b69 sqloledb!CStmt::SQLExecRPC+0x4d4
08 00000000`0e8aa4b0 000007ff`60755df3 sqloledb!CCommand::ExecuteHelper+0x2a9
09 00000000`0e8aa550 000007ff`60756cc7 sqloledb!CCommand::Execute+0xc73
0a 00000000`0e8aa640 000007ff`63f3e4cc sqloledb!CImpICommandText::Execute+0x187
0b 00000000`0e8aa6c0 000007ff`63f3f65b oledb32!CCommandText::DoExecute+0x4fc
0c 00000000`0e8aa900 00000000`108159ca oledb32!CCommandText::Execute+0x8ab
0d 00000000`0e8aabb0 00000000`3561ccb1 STSWEL!Voledb::ExecQuery+0x37e
0e 00000000`0e8aad10 00000000`3561dbee OWSSVR!GetExtensionVersion+0x62c5
0f 00000000`0e8ad5c0 00000000`35622540 OWSSVR!GetExtensionVersion+0x7202
10 00000000`0e8ad650 00000000`35650e0a OWSSVR!GetExtensionVersion+0xbb54
11 00000000`0e8adda0 00000642`7f600887 OWSSVR!GetExtensionVersion+0x3a41e


In this case you should watch this line: sqloledb!CStmt::SQLExecRPC.

 0:035> .frame /r 7 
 07 00000000`0e8aa3a0 000007ff`60754b69 sqloledb!CStmt::SQLExecRPC+0x4d4
 rax=0000000000000105 rbx=0000000010a311f0 rcx=0000000000000000
 rdx=000000000ddcac78 rsi=0000000000000000 rdi=00000000173c5be0
 rip=000007ff607bac34 rsp=000000000e8aa3a0 rbp=0000000010a22e30
 r8=000000000ddcac7e r9=000000ffffffffff r10=0000000000000000
 r11=0000000000000139 r12=00000000ffffffff r13=000000000e8aa5b8
 r14=0000000000000045 r15=00000000173cd390
 iopl=0 nv up ei ng nz na pe cy
 cs=0033 ss=002b ds=002b es=002b fs=0053 gs=002b efl=00000283
 000007ff`607bac34 83f8ff cmp eax,0FFFFFFFFh

The address of SQL statement is located in register 15.

 0:035> du 00000000173cd390
 00000000`173cd390 "{?=call proc_GetTpWebMetaDataAnd"
 00000000`173cd3d0 "ListMetaData(?,NULL,?,NULL,1,?,?"
 00000000`173cd410 ",18)}."

Hope it would be useful :)