| Author |
 Topic  |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
 Posted - 01/14/2009 : 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
Sweden
29138 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/14/2009 : 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" |
Edited by - SwePeso on 01/14/2009 07:22:34 |
 |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/14/2009 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/14/2009 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/14/2009 : 08:21:30
|
They are sometimes called "ad-hoc" queries.
Yes it is easier for lazy developers. It's easier to just fire away aoConn.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" |
|
|
| |
Topic |
|
sp_execute in profiler
