sp_prepexec
-
Does any one know what sp_prepexec extended stord procedure does? We find it running on server and CPU is at 100% and it appears to be the only thing running>
-
This is an internal extended system stored procedure that gets called to prepare and execute an ODBC batch in one round trip to the server. There is a detailed thread about this in Google Groups - search for "ODBC and SQL 2000" (including the leading and trailing double-quotes) in Google Groups, and look for the posts from October 2003, most of which are from Billy Yao and Lance Johnson.
Hope this helps!
Chris
PS. I would like to take this moment to commemorate my 150th post! When do I get paid?
Just kidding, all you 5000-post animals...
-
generally it is not sp_prepexec that is the root cause of 100% cpu usage, it is usually a very poorly formed query/design. sp_prepexec is used by ODBC and OLE to try an optimize sql that may or may not be used over and over again. So when the connection first issues free form sql, it will use prepare it as more or less a temp. stored procedure and then call it over and over as need, on that connection. It can be turned off.
-
Thanks for all the good information (and congratulations on 150!)
How does one turn it off?
Steve
-
> How does one turn it off?
According to the Data Source Wizard Help file, you can only turn it off for older versions of SQL Server. It is controlled by the set of options labeled "Create temporary stored procedured for prepared SQL statements and dorpt the stored procedures" and is on the third panel of the SQL Server DSN Configuration wizard (the wizard you get when you configure a SQL Server DSN in the ODBC Data Source Administrator). This is assuming current versions for the SQL Server client software, MDAC, and so forth, running (in my case) on WinXP Pro.
Hope this helps,
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply