See what sp_execute is doing

问题:

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?

回答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>

回答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

回答3:

The answer in SQL Server 7.0 and higher was "just turn on tracing in SQL Profiler".

Maybe you have SQL Profiler. If so - use it.

Or maybe you have a newer version of MSSQL Express (which no longer bundles SQL Profiler). If so, try this:

• http://sites.google.com/site/sqlprofiler/