sp_prepare and sp_execute

Question

I've been consulting google for some time now, but it hasn't been able to give me a satisfactory answer...

In a SQL Server 2005 trace, I've got lots of "exec sp_execute" statements. I know they are connected to a corresponding "exec sp_prepare" statement which specifies the actual SQL.

But...

One: Is it possible to find out the SQL behind the sp_execute without finding the sp_prepare?

Two: What type of construct would typically hide behind the sp_execute? That is, is it a stored procedure? Is it just a string in code? Or what?

Three: Should I fear bad performance seeing these in the trace?

Any input is appreciated

Answer 1

Use

select * from sys.dm_exec_query_plan(PlanHandle)

to generate an xml document that indicates what sql sp_execute is using.

Answer 2

Those are API Server Cursors, most probably used by an old (or not so old, but badly developed) application.

99% of the times, cursors affect performance on your server. Disk and network I/O are the potential victims.

Read this, it helped me understanding how server side cursors work.