[quoted text, click to view]

Though not supported directly in T-SQL, sp_Execute is a deprecated procedure
which is exposed by other data access APIs ADO, OLE DB, and ODBC. It is an
extended procedure wrapper around certain functionalities which support the
prepare/execute model & it returns a handle for the invoked batch. It is
similar to sp_ExecuteSQL in the sense that auto-parmeterization and adhoc
caching are applicable. Explicitly running DBCC DROPCLEANBUFFERS & DBCC
FREEPROCCACHE clears the cache and hence the delay.

--
Anith

[quoted text, click to view]

You cannot, since it is invoked internally & you have no control over it.
However you can look to other considerations which may speed up your
application.

If you are calling multiple stored procedures for retrieving data for a
single interface, consider consolidating them into one; you can handle
multiple resultsets both in ADO as well as with ADO.NET ( assuming you use
one of them ). This can multiple trips to the server by reducing network
traffic. Cache reuse for frequently used stored procedures can be a good
thing; in most cases since the execution plan can be reused directly from
the procedure cache. So you need not run the DBCC statements for each time.
Also, look into the stored procedures itself to see if there is any
bottlenecks. In many cases, retrieving thousands of rows to load a grid or
combobox on a client interface can be useless. Use proper WHERE clauses to
retrieve reasonable number of rows to the client. Also the usual checks, see
if the underlying tables are optimally indexed etc.

--
Anith

Hi,
My application was slow on loading a particular form. I call some SPs in
load event, then decided to use Profiler to track it.
There was a considerable delay on this statement(which of course I don't
issue that, but SQL Server itself does):
SET FMTONLY ON SP_EXECUTE 2 SET FMTONLY OFF
What does SP_EXECUTE do and why SQL Server calls that?
The next time I loaded the form, that statement did not have any delay. When
I use:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
on server and then load the form, it delays again.
Also sp_reset_connection is called(by SQL Server) before that lengthy
process.
Any help would be greatly appreciated.
Leila

Thanks Anith,
Can I prevent SQL Server from running sp_Execute? Becauase it slows my app.
Thanks,
Leila


[quoted text, click to view]