September 14, 2006 at 8:22 am
Every time my stored procedure is executed from .NET app it takes up to 1.5 minutes to excute. But when I execute it with the same params (it takes only some int user id) from QA it is finished in seconds. (Of course I executed stored proc in QA, not the query text)
I profiled both executions and discoverd that diffrent execution plans were generated. Also in case of .net app SP: ExecContextHit event is fired but in case of QA SP:CacheHit occurs. Could anyone explain why they're diffrent?
Also I failed to found sufficient information about SP: ExecContextHit and how it's diffrent from SP:CacheHit? Could you please explain me or give links on info about it?
Thanks in advance!
September 15, 2006 at 2:59 am
Check the SET settings that you connections are using, they have to be the same for SQL Server to reuse the execution plan. This is probably why your SP takes longer to run from ASP.NET and why you are seing different events in the profiler (even though I cannot explain what the ExecContextHit means).
Hope this can help you fix the problem and make your SP run faster from ASP.NET!
September 18, 2006 at 8:07 am
I've been here and the solution may take some time. You can check the procedure cache to see your plan being reused ( or not ) the different SET options will create seperate plans so it's not a big deal only duplication of space in the cache. I supect you have some other problems. I suggest you profile the plans for the proc and see what the differences are - you should be able to find the SET oprions too.
Make sure your proc call is fully qualified and that the proc doesn't start sp_
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply