I am trying to figure out how to detect issues with queries via SQL Server
Profiler and trace files. Because of the way we utilize JDBC with our
application, most of our queries appear in the trace files as prepared
statements. My understanding is that when the statement is “prepared� it is
assigned a handle. And the later this handle is used in sp_execute statement.
If I am incorrect please let me know.

My question is, how do tie the sp_execute statement back to the prepared
query, and how would you determine if the query is taking a long to time to
execute?

Thanks,
Mark

Mark Price (MarkPrice (AT) discussions (DOT) microsoft.com) writes:
Your description seems familiar to me.

The only technique I've used is to backtrack in the trace, but if the
statement was prepared before the trace started, that is not going to
help.

I searched around the DMV:s to see if there was anyplace where you could
look up a spid and handle to get the query text, but now.

However, you can add the column sql_handle to the trace. Then you can
use this handle to look up the query text with sys.dm_exec_sql_text.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx