Capturing the text of a prepared SQL statement run with sp_execute

Many times when running a trace against SQL Server you'll see RPC:Completed events that look like this:

exec sp_execute 66,4,4

Unfortunately this statement was the one causing me a performance problem and I had no idea what it was doing.  The 66 is the number of the statement to execute and the two 4's are the parameters passed to the statement.  The 66 is specific to a connection -- meaning the SQL is prepared for only that session.  I talked about the sequence of statements that are part of preparing and un-preparing SQL in Examining SQL Server Trace Files

Somewhere earlier there was an sp_prepare statement issued for statement number 66.  Unfortunately I didn't start my trace until it was long gone.  I posted this question on the newsgroup and got an answer back from Erland that worked.  I captured both the CacheHit event and the RPC:Completed event.  The CacheHit event right before the sp_execute had the text of the query in it.

I found it helpful to filter my trace for just a single SPID.  Otherwise there were just too many events all jumbled together.

Show Comments