Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 sp_execute in profiler

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-14 : 07:11:38
Hi,

I've been doing some profiling on a 13 GB database that is soon to expand at least 10 times when we are moving all customer data in to it. I've been noticing that there are a lot of sp_execute's going on which I've found out to be the preparing of other sql statement. They look like this:

exec sp_execute 38,67161
exec sp_execute 2,10000,'Jan 14 2009 1:01:32:090PM'
exec sp_execute 6

The problem is that I've never seen these before, and I'm wondering what this actually means. Is it actually a recompile of stored procedures or is it preparations of ad-hoc sql queries from the application? Something else maybe? Is it possible to find out what is actually being prepared?

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 07:20:28
See http://msdn.microsoft.com/en-us/library/aa259564(SQL.80).aspx
and section "API System Stored Procedures"


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 07:21:50
quote:
sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement. See Prepared Execution in BOL.

Unfortunately, MS has not provided us, the DBA's, a good way of actually seeing what SQL is actually being executed by sp_execute. This makes troubleshooting and optimization very difficult at best and sometimes impossible. This reason alone is enough to tell our developers not to use prepared statements.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-14 : 08:04:08
Thanx Peso, I found the link you referred to but it doesn't really say much. But the quote you posted, where is it from?

- Lumbago
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 08:07:52
http://www.google.co.uk/search?hl=en&q=%22This+reason+alone+is+enough+to+tell+our+developers+not+to+use+prepared+statements%22&meta=


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-14 : 08:16:50
Ok, thanx again Peso. I was actually, up until now, completely unaware of the possibility of prepared statements. And to be honest I don't see the real benefit compared to stored procedures but I guess it makes things slightly easier for lazy developers...

- Lumbago
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 08:21:30
They are sometimes called "ad-hoc" queries.

Yes it is easier for lazy developers. It's easier to just fire away a
oConn.Execute "SELECT * FROM Customers"
than calling a stored procedure. For that you need to use command objects and so on...



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

- Advertisement -