Stored procedure timeout

I have a stored procedure that is called from an ASP page and it takes
about 3 minutes to execute.
When I run it from QA it takes about 4 seconds.

Simple SQL Select works fine the problem is only with stored procedures.
Server is runnig W2003 and SQL2000
I've also installed SQL locally on Windows XP Pro with local IIS and
restorde database from backup and it also took about 4 minutes to execute.
When I open page with sp processor utilisation is 100%

Any ideas???

Thanks,
Grzesiek

Re: Stored procedure timeout

Grzegorz Rózanski wrote:
> I have a stored procedure that is called from an ASP page and it takes
> about 3 minutes to execute.
> When I run it from QA it takes about 4 seconds.

With the same parameters?

>
> Simple SQL Select works fine the problem is only with stored
> procedures. Server is runnig W2003 and SQL2000
> I've also installed SQL locally on Windows XP Pro with local IIS and
> restorde database from backup and it also took about 4 minutes to
> execute. When I open page with sp processor utilisation is 100%
>
You may be running into the "parameter sniffing" behavior described here:
http://tinyurl.com/h7aa

If you do something like this:

create procedure myproc (@parm int) as
select ... where somecolumn = @parm

You may be able to mitigate the behavior's effects by changing it to:

create procedure myproc (@parm int) as
declare @localparm int
set @localparm=@parm
select ... where somecolumn = @localparm

If this does not work, add WITH RECOMPILE to the CREATE PROCEDURE statement
so it is recompiled every time it is executed (yes, I know this defeats one
of the benefits of using procedures ... )

If recompiling does not , then you will need to use SQL Profiler to
determine where the bottleneck is occurring and address it. See:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp .mspx#EFAA


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure timeout

Bob Barrows [MVP] wrote:
> If recompiling does not , then you will need to use SQL Profiler to

This should have read:
If recompiling does not eliminate the timeout, then you will need to use SQL
Profiler to



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure timeout

I've eliminated parameter sniffing by declaring local variables in stored
procedure.
Asp page loaded in 2 seconds and after few hours it takes about 2 minutes
to load.
The problem is only with stored procedures called from asp page.
Any more suggestions?

Thanks in advance,
Grzesiek

Re: Stored procedure timeout

Grzegorz Rózanski wrote:
> I've eliminated parameter sniffing by declaring local variables in
> stored procedure.
> Asp page loaded in 2 seconds and after few hours it takes about 2
> minutes to load.
> The problem is only with stored procedures called from asp page.
> Any more suggestions?
>

Using local variables does not always resolve parameter-sniffing problems.
The only way to be really sure is to use the WITH RECOMPILE option in your
CREATE PARAMETER statement. If that resolves your symptoms, then you know
parameter-sniffing is the culprit.

If not, you have to look for other culprits. Use SQL Profiler to see what is
happening on the server when the timeouts start occuring. See
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp .mspx#EFAA Add
the SP:StmtStarting and SP:StmtCompleted events to the trace.

It will quickly tell you whether your stored procedure is the bottleneck
and, if so, which statement in your procedure is causing the bottleneck.
Conversely, it will also tell you if you have been falsely accusing your
stored procedure. The problem just may be in the code used to process the
results from your procedure. You can use some well-placed Response.Write
Now() statements (combined with response.flush) to figure out where the
bottleneck is occuring in your vbscript code.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure timeout

Bob Barrows [MVP] wrote:
> Grzegorz Rózanski wrote:
>> I've eliminated parameter sniffing by declaring local variables in
>> stored procedure.
>> Asp page loaded in 2 seconds and after few hours it takes about 2
>> minutes to load.
>> The problem is only with stored procedures called from asp page.
>> Any more suggestions?
>>
>
> Using local variables does not always resolve parameter-sniffing
> problems. The only way to be really sure is to use the WITH RECOMPILE
> option in your CREATE PARAMETER statement.

Sorry, I meant to say "CREATE PROCEDURE", not "CREATE PARAMETER"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Stored procedure timeout

> If not, you have to look for other culprits. Use SQL Profiler to see what is
> happening on the server when the timeouts start occuring. See
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp .mspx#EFAA Add
> the SP:StmtStarting and SP:StmtCompleted events to the trace.
>
I've compared execution of sp in ASP and in QA using Profiler and I've
noticed that some statements (for example select from user defined
function) takes about 110391 duration in ASP and 1625 in QA.
Here http://drogowcy.pl/pub/porownanie.pdf is comparison of execution.
Why functions called from SP by ASP are executed so slow and so fast when
executed from QA?

Thanks a lot for your help,
Grzesiek R.

Re: Stored procedure timeout

Grzegorz Rózanski wrote:
>> If not, you have to look for other culprits. Use SQL Profiler to see
>> what is happening on the server when the timeouts start occuring. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp .mspx#EFAA
>> Add the SP:StmtStarting and SP:StmtCompleted events to the trace.
>>
> I've compared execution of sp in ASP and in QA using Profiler and I've
> noticed that some statements (for example select from user defined
> function) takes about 110391 duration in ASP and 1625 in QA.

Even with "WITH RECOMPILE" included in the procedures creation statement?

> Here http://drogowcy.pl/pub/porownanie.pdf is comparison of execution.
> Why functions called from SP by ASP are executed so slow and so fast
> when executed from QA?
>
To find out, you need to add the Execution Plan and Show Plan Text events
(in the Performance node) to the Profiler trace and post the results to
microsoft.public.sqlserver.programming (you need some more eyes to look at
it).
I suspect you may need to add an index hint to the problem statement in the
procedure.
They will probably need to see some DDL (see www.aspfaq.com/5006) in order
to be able to help you. I hope you will be able to follow up here with any
solution you find.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"