We help IT Professionals succeed at work.

SQL Profiler - need to capture SQL behind "sp_execute" items

SDyer
SDyer asked
on
3,517 Views
Last Modified: 2010-08-05
I am running SQL Profiler looking for queries taking longer than 1 second.  The Trace file contains many items of the form:

Event Class: RPC Completed
Text Data: exec sp_execute ####

where "####" is a number that seems to increas as the trace runs - the numbers are two digits at the beginning, and into the thousands at the end.

How do I find out what the SQL was that was executed?  

I tried capturing "prepare" events, but those are cryptic as well - they tell me that something was prepared, but not what that something was.
Comment
Watch Question

Author

Commented:
Also - there are no "sp_prepare" statements at all in the trace file.  I've read in other forums that there should be an sp_prepare with the same id number as the sp_execute, but my trace files don't show them.

Commented:
what version of ms sql are you using?

I usually use the SQL:BatchCompleted and SQL:BatchStarting events in the TSQL section.

Author

Commented:
SQL Server 2000.

Commented:
You'd probably also want to look at SQL:StmtCompleted and SQL:StmtStarting under the TSQL events and SP:StmtCompleted and SP:StmtStarting under the Stored Procedures events.  Let us know if that helps.

Author

Commented:
I just added those events, cdaly33, and what I'm seeing are many many SQL:StmtStarting items in the profiler.  None of the other events are showing up.  Also, I've set a filter so that I see only items over 1000 ms (1 second), but I seem to be getting a SQL:StmtStarting for every query sent to the db - all of them have a blank Duration column.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hmm.  I removed the Start events from the trace.  Now I'm seeing what look like pairs of SQL:StmtCompleted  and RPC:Completed items, all with durations over 1000 ms, so it looks like they are indeed filtered.  If you could confirm that these are indeed pairs, then I would know that the SQLSQL:StmtCompleted  shows the sql text behind the original RPC:Completed items I was seeing, and we can wrap this up.

Commented:
The connection is probably being reset before each of the SQL statements which is common in some apps.  Below is a link to the MS definition of RPC which might provide some more insight.

http://msdn2.microsoft.com/en-us/library/aa378651.aspx
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.