SQL Server Forums
Profile | Register | 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
 New Topic  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
30282 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
30282 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
30282 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
30282 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000