Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 990
Tys Avatar asked Apr 30 '12 16:04

Tys


2 Answers

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

like image 142
lison Avatar answered Sep 24 '22 02:09

lison


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>
like image 24
Dennis Avatar answered Sep 20 '22 02:09

Dennis