Stored Procedure Timeout

3 Mar 2006 1:59 PM

Joachim Prömmel

Hello,

I've got a problem with a stored procedure. Our Webserver (Win 2K, IIS5)
calls a Stored Procedure on SQL-Server (Win2003, SQL2000 Std Ed) using
ADO.NET. The Stored Procedure takes about 2 or 3 Seconds to run.

But now, we've got a problem with this stored procedure. Sometimes (not
always!) the stored procedures needs up to 2 minutes (or more) and the
Web-Server throws an Timeout Exception.
We can reproduce the Timeout by calling the Stored Procedure from other
Applications (e.g Excel - ODBC, QueryCommander - ADO.NET).
BUT (and this is very curious) when i call the stored procedure in the
QueryAnalyzer, the stored procedures needs only 2-3 seconds.
I'm using the same SQL-Server Account.

After recreating the stored procedure (using a script) everything works
fine.

I used the Profiler to see what´s going on, and the only difference was,
that running the SP from the QueryAnalyzer there was an SP:ExecContextHit
and calling the SP from the Web-Server there was a SP:CacheHit.

Does anyone know a solution for this problem?

Thanks

J. Proemmel

3 Mar 2006 2:09 PM

JT

When a 2 second procedure executes for 2 minutes (or time outs) on occasion
or under certain cirsumstances, then this could perhaps be a blocking issue.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453

Show quote

"Joachim Prömmel" <joachim.proem***@schweer-it.de> wrote in message
news:%23arMCrsPGHA.4956@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I've got a problem with a stored procedure. Our Webserver (Win 2K, IIS5)
> calls a Stored Procedure on SQL-Server (Win2003, SQL2000 Std Ed) using
> ADO.NET. The Stored Procedure takes about 2 or 3 Seconds to run.
>
> But now, we've got a problem with this stored procedure. Sometimes (not
> always!) the stored procedures needs up to 2 minutes (or more) and the
> Web-Server throws an Timeout Exception.
> We can reproduce the Timeout by calling the Stored Procedure from other
> Applications (e.g Excel - ODBC, QueryCommander - ADO.NET).
> BUT (and this is very curious) when i call the stored procedure in the
> QueryAnalyzer, the stored procedures needs only 2-3 seconds.
> I'm using the same SQL-Server Account.
>
> After recreating the stored procedure (using a script) everything works
> fine.
>
> I used the Profiler to see what´s going on, and the only difference was,
> that running the SP from the QueryAnalyzer there was an SP:ExecContextHit
> and calling the SP from the Web-Server there was a SP:CacheHit.
>
> Does anyone know a solution for this problem?
>
> Thanks
>
> J. Proemmel
>

3 Mar 2006 2:14 PM

Andrew J. Kelly

You most likely got a bad plan when the sp was recompiled the last time.
Since it depends heavily on the values passed in for the parameters the
first time it gets called (or during a recompile) the plan can be different
depending on the values. If the values were such that a table scan was best
all subsequent calls will do a scan as well.

--
Andrew J. Kelly SQL MVP

Show quote

"Joachim Prömmel" <joachim.proem***@schweer-it.de> wrote in message
news:%23arMCrsPGHA.4956@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I've got a problem with a stored procedure. Our Webserver (Win 2K, IIS5)
> calls a Stored Procedure on SQL-Server (Win2003, SQL2000 Std Ed) using
> ADO.NET. The Stored Procedure takes about 2 or 3 Seconds to run.
>
> But now, we've got a problem with this stored procedure. Sometimes (not
> always!) the stored procedures needs up to 2 minutes (or more) and the
> Web-Server throws an Timeout Exception.
> We can reproduce the Timeout by calling the Stored Procedure from other
> Applications (e.g Excel - ODBC, QueryCommander - ADO.NET).
> BUT (and this is very curious) when i call the stored procedure in the
> QueryAnalyzer, the stored procedures needs only 2-3 seconds.
> I'm using the same SQL-Server Account.
>
> After recreating the stored procedure (using a script) everything works
> fine.
>
> I used the Profiler to see what´s going on, and the only difference was,
> that running the SP from the QueryAnalyzer there was an SP:ExecContextHit
> and calling the SP from the Web-Server there was a SP:CacheHit.
>
> Does anyone know a solution for this problem?
>
> Thanks
>
> J. Proemmel
>

3 Mar 2006 2:42 PM

JT

Also, does the SP use dynamic SQL?

Show quote

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:ec3SIzsPGHA.5296@TK2MSFTNGP09.phx.gbl...
> You most likely got a bad plan when the sp was recompiled the last time.
> Since it depends heavily on the values passed in for the parameters the
> first time it gets called (or during a recompile) the plan can be
> different depending on the values. If the values were such that a table
> scan was best all subsequent calls will do a scan as well.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Joachim Prömmel" <joachim.proem***@schweer-it.de> wrote in message
> news:%23arMCrsPGHA.4956@TK2MSFTNGP09.phx.gbl...
>> Hello,
>>
>> I've got a problem with a stored procedure. Our Webserver (Win 2K, IIS5)
>> calls a Stored Procedure on SQL-Server (Win2003, SQL2000 Std Ed) using
>> ADO.NET. The Stored Procedure takes about 2 or 3 Seconds to run.
>>
>> But now, we've got a problem with this stored procedure. Sometimes (not
>> always!) the stored procedures needs up to 2 minutes (or more) and the
>> Web-Server throws an Timeout Exception.
>> We can reproduce the Timeout by calling the Stored Procedure from other
>> Applications (e.g Excel - ODBC, QueryCommander - ADO.NET).
>> BUT (and this is very curious) when i call the stored procedure in the
>> QueryAnalyzer, the stored procedures needs only 2-3 seconds.
>> I'm using the same SQL-Server Account.
>>
>> After recreating the stored procedure (using a script) everything works
>> fine.
>>
>> I used the Profiler to see what´s going on, and the only difference was,
>> that running the SP from the QueryAnalyzer there was an SP:ExecContextHit
>> and calling the SP from the Web-Server there was a SP:CacheHit.
>>
>> Does anyone know a solution for this problem?
>>
>> Thanks
>>
>> J. Proemmel
>>
>
>