How to Extract SQL info from a thread stack

In the post about troubleshooting Asp.Net poor performance I showed you how to identify one possible cause for high response times when processing Asp.Net pages – the cause we explored on that post was a database server taking too long to respond. We were able to verify all the information about the SQL side of the scenario through analyzing the Asp.Net thread stack and using the SOS.dll specific commands. So the question now is: What about when I’m processing a Classic Active Server Page (ASP) page instead of a Asp.Net one?

Well in this case, as we already know, the SOS.dll won’t help, so let’s see how to do it manually.

First step is to identify the thread and look at it call stack as below:


0:016> kb10

ChildEBP RetAddr Args to Child

00f2e1dc 7c822124 77e6baa8 000006b8 00000000 ntdll!KiFastSystemCallRet

00f2e1e0 77e6baa8 000006b8 00000000 00f2e224 ntdll!NtWaitForSingleObject+0xc

00f2e250 77e6ba12 000006b8 00004e20 00000000 kernel32!WaitForSingleObjectEx+0xac

00f2e264 74cd2e3f 000006b8 00004e20 00004e20 kernel32!WaitForSingleObject+0x12

00f2e290 6d56db96 00c1d2f8 01ab4270 00000009 DBmsLPCn!ConnectionRead+0xaf

00f2e2b0 6d56871b 01ab6290 01ab4270 00000009 DBNETLIB!WrapperRead+0x2c

00f2e308 4e252d91 01ab6290 01ab4270 01ab4270 DBNETLIB!ConnectionRead+0x519

00f2e33c 4e252df0 01ab6290 01ab4270 00000009 SQLOLEDB!CDataSource::ConnectionRead+0x35

00f2e388 4e2522b6 01ab403e 00000001 00000000 SQLOLEDB!CDBConnection::GetBytes+0x269

00f2e3d4 4e259f63 015c4e08 00000088 0000001e SQLOLEDB!CDBConnection::ProcessTDSStream+0x157

00f2e490 4e25a030 015c5a00 00000014 015c4970 SQLOLEDB!CStmt::ExecDirect+0x786

00f2e4a8 4e2596d1 015c5a00 00000014 00000000 SQLOLEDB!CStmt::SQLExecDirect+0x28

00f2e4d8 4e259b1c 00000000 4e2590b4 00000014 SQLOLEDB!CCommand::ExecuteHelper+0x157

00f2e55c 4e2595ea 015c5a68 00000000 4bbe9658 SQLOLEDB!CCommand::Execute+0x76b

00f2e594 4bbe952e 015c4d60 00000000 4bbe9658 SQLOLEDB!CImpICommandText::Execute+0xdd

00f2e5d4 4bc0c9db 015c12b0 015c4a04 015c1638 msado15!CConnection::Execute+0x9d

00f2e7a4 4bbe9388 015c5660 00000003 015c48a8 msado15!_ExecuteAsync+0x19f

00f2e7b8 4bbe90e0 015c5660 00f2e988 00000000 msado15!ExecuteAsync+0x23

00f2e8a0 4bbe9303 00000003 015c1a00 00000000 msado15!CQuery::Execute+0xa5e

00f2e90c 4bbe6128 015c48a8 00000003 015c1a00 msado15!CCommand::_Execute+0x153

00f2e9a0 4bbe6400 ffffffff 015c1a00 00f2eb34 msado15!CRecordset::_Open+0x30f

00f2eb18 11004397 005c1638 00000008 00000000 msado15!CRecordset::Open+0x5dc


So, that’s a classic OLEDB thread stack. To obtain the SQL command being send to the SQL server identify the function call SQLOLEDB!CStmt::SQLExecDirect above. Grab the first parameter and run the following command:

0:016> du 015c5a00


Of course you still want to know which SQL server this thread holds a connection with. So how about checking the connection string? Just identify the the function call msado15!CConnection::Execute, grab the first parameter and execute the following:

0:016> du poi(015c12b0+64)

015c1aa8 "Provider=SQLOLEDB.1;Integrated S"

015c1ae8 "ecurity=SSPI;Persist Security In"

015c1b28 "fo=False;Initial Catalog=pubs;Da"

015c1b68 "ta Source=(local)"

So now you can call the DBA in charge of the server “local” and ask him to collaborate with you on this low performance problem your ASP app has been unfairly blamed about J

Have fun!