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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 sp_execute in profiler
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/14/2009 :  07:11:38  Show Profile  Reply with Quote
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
30421 Posts

Posted - 01/14/2009 :  07:20:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30421 Posts

Posted - 01/14/2009 :  07:21:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/14/2009 :  08:04:08  Show Profile  Reply with Quote
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

Sweden
30421 Posts

Posted - 01/14/2009 :  08:07:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Norway
3271 Posts

Posted - 01/14/2009 :  08:16:50  Show Profile  Reply with Quote
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

Sweden
30421 Posts

Posted - 01/14/2009 :  08:21:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000