March 27, 2008 at 10:24 am
Hi All,
I have a stored Proc that runs fine from management Studio. That is it takes around 10,000 page reads and less than a second to complete. But when I run the same stored proc from the a web page (in ASP), it just hangs there. Once it completed and it had 10 times more reads than when run from the management Studio.
I was looking at the activity monitor and I can see lots of Page Latches when running from ASP Page.
The only thing I can conclude is that it is taking a different Query plan. The question is how can make it use the same query plan? I really would prefer not to use any hints. That woudl be my last resort.
Thanks
Roy
-Roy
March 27, 2008 at 10:27 am
Fixed it for now by doing an sp_recompile for the stored Proc. But it might still creeep up again. So any advice is appreciated.
-Roy
March 27, 2008 at 10:39 am
You may want to turn on profiler and catch what is going on when the stored procedure runs. this way you can see what the memory,io, and locking really look like when this sp runs.
March 27, 2008 at 11:46 am
Also it could be the statistics are changing in your data. Make sure that they're up to date. It's really important, as noted above, to look at the execution plan, especially when it's running very well or very poorly, to understand what is happening. You could be seeing a bad plan for one set of parameters but a good plan for another. It could be a lot of things, but I'd start with the statistics and the plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply