Can't find what is in sp_execute | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Can’t find what is in sp_execute

We are using SQL 2k5 with sp2. In the past few days, from the profiler I track down quite a few sp_execute statements which causes huge IO and high CPU (when a few of these statements run together), and their duration time was ranging from 30 seconds to 3 minutes. A sample of such query is as follows:<br /><br />declare @p2 int<br />set @p2=0<br />exec sp_execute 44,@p2 output,-1,179665,9,2<br />select @p2<br /><br />I am trying to find out what is being executed with this sp_execute and have turned on the tracking events in profiler as listed below.<br /><br />-RPC_Completed<br />-Exec Prepared SQL<br />-Prepare SQL<br />-SQL:batchCompleted<br />-SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tmtcompleted<br /><br />I was hoping I can see some information from the event “Exec Prepared SQL” because Prepare SQL and Sp_execute works together and Prepare SQL is supposed to show the details. As it turns out, the ‘Prepare SQL#%92 event does show up in the profiler but it shows blank in the textData field. What else can I do to find out what the sp_execute is doing? Any help on this is very much appreicated.<br /><br />Wingman<br />
Try with using fn_get_sql in thsi case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
sp_execute is an internal sp that is used in the prepare/execute model. These system stored procedures support the prepare/execute model of executing Transact-SQL statements in ADO, OLE DB, and ODBC Piyush
..but with FN_GET_SQL you can get more information the offending SPID. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>