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.
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,67161exec sp_execute 2,10000,'Jan 14 2009 1:01:32:090PM'exec sp_execute 6The 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).aspxand section "API System Stored Procedures" E 12°55'05.63"N 56°04'39.26" |
|
|
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" |
|
|
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 |
|
|
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" |
|
|
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 |
|
|
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 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" |
|
|
|
|
|
|
|