June 30, 2004 at 10:38 am
OK,
I have been working on this problem for some time now and have not come up with a solution. I currently have a .Net web application that connects to my SQL 2000 database using Microsoft application blocks. The web application is built using using multiple Virtual directories that control various functional areas of the application. When I go into one area I consistently receive a timeout error, and I have narrowed it down to connecting and running one specific stored procedure. The stored procedure is a fairly substantial procedure that retrieves data joined from several tables (18) in two separate databases. In the past I have not had a problem with this stored procedure, only recently have I started to receive the timeout error. The stored procedure has one input parameter that comes in as a char data type that is later converted to an integer data type in the where clause.
I can run the stored procedure from the Query Analyzer and after an initial run where an execution plan is developed (takes about 2 minutes) the procedure returns a record set in under 1 second. When I run the profiler I can see how an execution plan is being used when I run the procedure in the Query Analyzer. When I use the profiler to trace activity while running the web application I can see that no execution plan is used when it hits this stored procedure - (cachemiss event).
I don't want to up the connection timeout through the web application; because I don't want end user to have to wait 2 minutes for a page to load, and I can see that the procedure can be executed in a timely manner. Not to mention that the procedure worked in the past. I have tried a number of different solutions to fix this problem:
1) The statement that executes the procedure was not using the fully qualified name. I changed the code to use the fully qualified name - no change
2) I changed the input parameter to pass the parameter as an integer and removed the convert statement in the where clause - no change
3) I changed the stored procedure to take the input parameter and assign it to an internal parameter and run the select using the internal parameter - no change
4) I have tried the following hints in the stored procedure: OPTION (KEEP PLAN) OPTION (MAXDOP 1)
5) I place indexes on the tables involved in the procedure.
Any help at this point would be welcome.
Thanks,
Ross E. Clement
June 30, 2004 at 12:06 pm
You didn't happen to name the SP 'sp_' as the prefix did you? If so it will always try to find in master before local and the cachemiss will occurr.
July 1, 2004 at 6:56 am
Does the web user have the same priveleges as the user that QA is running under?
If so, and If the sp works OK in QA but not through a web app, I would say that the web app is doing something more than QA is.
Sounds like it is recompiling the sp every time it runs in the web app. There hasn't been a WITH RECOMPILE added anywhere, has there?
All I can think of I am afraid.
July 1, 2004 at 1:50 pm
Thanks for the responses, I do appreciate the input.
More information:
My stored procedures are preface with "usp_".
I used the same login to run the procedure in Query Analyzer that the web application uses.
I am not suing the WITH RECOMPILE option.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply