March 2, 2007 at 11:15 am
I am having a strange performance problem.
I have a classic ASP web page that connects to our SQL Server and executes a stored procedure with some passed in parameters. Occassionally, we get a timeout error (i.e. > 90 seconds) on the page at the line number for the stored procedure. If I extract the SQL code from the ASP page and execute it in Query Analyzer (logged in with the web login/password), it takes only 2 seconds.
To make sure that the problem wasn't being caused by a firewall, I ran the ASP page on my local computer and connected to our SQL Server on the internal network. Saw the same timeout problem.
I then ran Ethereal and captured the TCP packages being sent to and from the SQL Server. Sure enough, I saw the request go over to the SQL Server and then a long pause before the resultset was returned.
So, what is going on? The ASP page times out, but Query Analzer doesn't for the same SQL code that executes the same stored procedure with the same parameters, even when both are ran at the same time.
Anybody have any idea what more I can do to figure out what's causing the time out with the classic ASP page?
March 2, 2007 at 12:09 pm
It could be a different execution plan between the stored procedure and the extracted sql code due parameter sniffing. The query plan could help here.
To capture the sql-statements you can use sql-profiler.
Do the parameters match the underlying datatypes?
In the extracted sql code, did you replace the parameter with the actual values?
Have you checked that the connection options are the same?
Have you monitored any locks (sp_who, sp_lock)
March 2, 2007 at 12:19 pm
Sorry. I guess I wasn't very clear. The same "exec usp_...." SQL code that the ASP page executes is what I am executing in Query Analyzer. (I showed the SQL code on the page before it is executed and copied that into QA.) They both are executing the same stored procedure, so shouldn't they both be using the same (cached) execution plan? The only execution plans that are created on the fly would be the execution plan for the "exec usp_...". Could they very significantly?
I checked for locks while the ASP page was running, but did not see anything that looked out of place (just a bunch of share locks). The stored procedure is only returning data - no inserts, updates or deletes (except for temp tables) - so it shouldn't be doing anything other than shared locks.
As far as connections go, the ASP page uses ADO with the connection parameters listed in a string. Query Analyzer uses ... what?
March 5, 2007 at 7:26 am
HI Roger,
Have you tried running SQL Profiler when you run the query both times. both when you run the SP in Query Analyzer and in ASP (as Jo suggested).
You should then be able to see if there are any differences in the statements being executed.
Also are you sure the ASP page is not calling the SP multiple times?
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
March 5, 2007 at 9:49 am
I know for a fact that the ASP page is only executing the SP once and is timing out on this one call.
The SQL Server is our production server and the web site is our production web site. We have not seen the problem in our development or test environments. I have been able to reproduce the problem running the same ASP page on my local computer. Is it possible for SQL Profiler to trace only submissions from my local computer? (Both the ASP page's SP call and QA's SP call.) I'm still learning SQL Profiler so I am not sure what has to be configured to do this.
March 5, 2007 at 11:31 am
There is a field Hostname you can place a filter on, but this will probably be the webserver.
Have you checked default connection-settings between test and production server?
Is there a difference in connection pooling?
Servicepack level of sql server is still the same?
March 5, 2007 at 2:45 pm
I think I found the problem - the execution plan for the stored procedure. (SQL Profiler helped point the way.)
The stored procedure is called with a date range. I suspect that the cached execution plan was built with, say, an index seek. For the timeouts, the stored procedure is being called with a large date range which the index seek in the cached execution plan takes a long time to execute. (A table scan would have been the better method.)
To solve this, I am going to convert the select statement in the stored procedure to dynamic SQL and embed the date range values into the dynamic SQL. This way, the SQL will always be recompiled, but only that part that absolutely has to.
Thanks for everyone's help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply