See what sp_execute is doing

Question

In my MS SQL Profiler i'm seeing lots of these small queries.

exec sp_execute 1,@p0=15954

I know this works, in theory, that this is executing a previously created query and passing in a specific parameter. But the problem is that i am not sure about what causes these queries.

Is there a way to see the TSQL contents of these queries?

Solution 1

This looks like it's running a prepared query. One thing to do would be to look through the profiler trace to see if you can find the sp_prepare queries that generating this particular handle.

The other option, would be query system views to find the underlying text.

This will give you the query if it's currently running

select text
    from sys.dm_exec_requests
    cross apply sys.dm_exec_sql_text(plan_handle)
    where session_id = <SPID FROM PROFILER>

Solution 2

In Sql profiler I use Event StoredProcedure>>SP:CacheHit. I read about it in link. Capture SQL:StmtCompleted and RPC:Completed DIDNT catch the parameter of sp_execute