SQL Server Performance Forum – Threads Archive
Can profiler show SQL for prepared statements?
Is there a way to use SQL Server Profiler to show the SQL for prepared statements that are being traced? Essentially there are two things we need during development: 1) To see what queries are being run, and 2) re-run the query standalone using query analyzer (or studio) to see the resultset and/or any errors. Most of the time we’re troubleshooting the business logic and errors rather than performance tuning. Our queries are submitted through a JDBC driver. Some are prepared statements with parameters, others are unprepared SQL that must be parsed and executed. The unprepared SQL is displayed by SQL Server Profiler and is no problem. The prepared statements however are difficult to identify. What are the tricks to working with prepared statements in SQL Server Profiler? The ideal solution would be a way to reconstruct the original SQL statement with the parameters substituted.I don’t know if this is the correct forum to post this question. Is there a better forum to post it?
1. if you are SQL 2005, then its close enough 2. yes but you must catch the statement on the call to prepare,
after which it get a handle, for later use 3. no matter what other idiots tell you
do no use prepared statements unless you enjoy suffering horrible pain, or for your users to do so do use parameterized sql these are not the sames
prepared sql may seem good in dev, but not prod, especially on a 32-bit sql with lots of physical memory
quote:Originally posted by joechang
1. if you are SQL 2005, then its close enough
2. yes but you must catch the statement on the call to prepare,
after which it get a handle, for later use 3. no matter what other idiots tell you
do no use prepared statements unless you enjoy suffering horrible pain, or for your users to do so do use parameterized sql these are not the sames
prepared sql may seem good in dev, but not prod, especially on a 32-bit sql with lots of physical memory
after which it get a handle, for later use 3. no matter what other idiots tell you
do no use prepared statements unless you enjoy suffering horrible pain, or for your users to do so do use parameterized sql these are not the sames
prepared sql may seem good in dev, but not prod, especially on a 32-bit sql with lots of physical memory
Thanks, I’ll look into that parameterized vs. prepared (any links?). Our code runs with Oracle as well, do you know if that statement applies to Oracle? As for tracing the prepared statements, would you describe more about how you would get and use the handle to display the SQL? Or is is just a matter of mentally plugging in the values to the SQL that you capture during the prepare? Ideally you’d like to be able to do a query that reconstructs the SQL with the parameters from some system table, but is it true that SQL Server does not hold onto the original SQL when it prepares a query? -SB
]]>