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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-07-16 : 13:24:31
|
I've been doing some auditin with Profiler looking for performance enhancements. However, I've noticed something weird.I have a procedure; let's call it p_procedure. It's called on every load of a particular web page. In a typical hour, that page gets about 2000 hits.The call is done with explicit command, parameter, and recordset objects.In a 10 minute Profiler trace, I saw the stored procedure called the expected 150 or so times. The weird thing is that two of them were different. Rather than a single event with the textdata being "exec dbo.p_procedure", in thse two cases, there were two events.The first event looks like this:declare @P1 intset @P1=1exec sp_prepare @P1 output, N'@P1 int,@P2 int', N' EXEC dbo.p_procedure @P1, @P2', 1select @P1 The second event was simply "sp_unprepare 1"I've searched high and low through the app and all stored procedures; there is no place at all calling this procedure except that one ASP page (and rightfully so, as the procedure's purpose is to provide all of the info that that page needs).Any idea why the anomalous sp_prepare calls?Cheers-b |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-16 : 13:37:11
|
| I'd bet that proc's plan has been pushed out of the cache buffer because either the memory manager needs to free up some buffer for other objects, the object isn't being referenced by a connection (doubtfull given 150 in 10 minutes) or the statistics on your dependency tables have changed causing the engine to reevaluate the plan.<O> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-16 : 13:48:31
|
| ODBC uses a lot of statement prepare calls (sql_prepare I think its called), so if you're using ODBC or the ODBC provider for OLE DB to connect to the SQL Server, that could be the reason. I don't think ODBC has its own procedure cache, and in generating sql_prepare calls they might differ enough so that SQL Server's procedure cache doesn't recognize them either.If you can switch to a native OLE DB provider for SQL Server (if you're not already using it) it should clear up the problem. |
|
|
|
|
|
|
|
|
sp_prepare -- why?