Sp_prepare and sp_execute

Question

I have been advising Google for some time, but he could not give me a satisfactory answer ...

In SQL Server 2005 tracing, I have many exec sp_execute statements. I know that they are connected to the corresponding exec statement sp_prepare, which indicates the actual SQL.

But...

One: Is it possible to learn SQL for sp_execute without finding sp_prepare?

Two: What type of construct is usually hidden behind sp_execute? That is, is this a stored procedure? Is this just a line in code? Or what?

Three: Should I be afraid of poor performance by watching them in the trace?

Any input is appreciated

Answer 1

Using

select * from sys.dm_exec_query_plan(PlanHandle) 

to create an XML document that indicates that it uses sql sp_execute.

Answer 2

These are API server cursors that are most likely used by an old (or not so old, but poorly designed) application.

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

Read this , it helped me understand how server cursors work.

Answer 3

Late answer, but I recently got an application with poor execution of sp_prepare and sp_execute.

One : answered before

Two : it can be anything, stored procedures, any valid sql query basically.

Three . I was having trouble SQL Server was unable to create good execution plans when the application used sp_prepare. Basically, SQL Server parses the input parameters to create a good execution plan, but with sp_prepare, parameter values ​​are not provided, since they are only added when sp_execute is executed. Thus, at the same time, SQL Server applies common costs for different operators and can very well generate a suboptimal plan.

If you look at your reads / cpu usage for your traces, you can determine if your queries are doing poorly or as expected.

Also see http://blogs.infosupport.com/speeding-up-ssis-literally