Is SQL Profiler useful with ODBC

When using SQL Profiler with ODBC, all I ever see are invocations of sp_prepexec, sp_prepare, and sp_execute. I know that these system stored procedure support the ODBC layer by preparing and executing statements.

I can't find any options in SQL Profiler for inspecting such statements, though. If the statement has already been prepared, we'll only see sp_execute with the handle that sp_prepare returned, plus the parameter list. This gives us no information about what statement is actually executing.

Can SQL Profiler query the execution environment in the engine to find the statement associated with the integer returned from sp_prepare and passed to sp_execute?

If not, how can SQL Profiler be effectively used with applications that use ODBC?I haven't received any responses, so let me be clearer.

When I run Profiler against SQL Server while my app is running using ODBC, I see streams of "sp_execute" statements. These statements invoke a particular statement ID which was previously compiled with "sp_prepare".

But since I haven't been tracing against the server for the whole life of all these connections, I don't know what specific statements the statement IDs refer to. As such, I can't tell which statements my application is executing, how long they're taking, and so on.

Is there a way to make use of SQL Profiler in this situation? Is this just another reason to not use ODBC?|||

You could try capturing those statement using ODBC trace, but mind you that might get the performance down a bit.

Review http://www.sqlteam.com/item.asp?ItemID=24658 for more information.

|||Thanks for the link, Satya. I know exactly what the stored procedure statements are doing. I just want to find a way to be able to monitor the server -- and I thought SQL Profiler was the preferred tool for that.

I've heard conflicting advice about the load that profiling puts on the server. It wouldn't be too hard to write a C# application which lets us monitor the state of the server (though--why should we have to?). What load does running that profile at all times place on the machine? I've read some posts that say it shouldn't be done at all on production boxes; other references say it can be done for short periods with care; and my team was told by a Microsoft consultant that it puts no load whatsoever on the machine.

What's the real answer?