Re: SP WITH RECOMPILE Option

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 12:20:03 -0500

It sounds as though some user is logging in and specifying parameter inputs
that cause SQL Server to choose an execution plan which is suboptimal for
future executions of the stored procedure using different parameter inputs.
Obviously you've discovered that WITH RECOMPILE comes with some problems but
does help; another option you might consider is creating different stored
procedures for your vastly different parameter inputs which require
different execution plans, having your application call them depending on
which parameters will be used. This is definitely a difficult situation to
solve elegantly, unfortunately... 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ayad" <ashammout1@hotmail.com> wrote in message
news:%238XmYuPwEHA.2732@TK2MSFTNGP12.phx.gbl...
> I have a web application with a back end SQL server db. Every time the
users
> login to the web site, they execute a SP.
>
> The issue is that the user is getting a time-out error message when they
> call the SP. If I add the WITH RECOMPILE option to the SP, they don't get
> the time-out error but on the SQL server it cause a COMPILE x lock on the
SP
> and slow down the application but they get in.
>
> Why if I remove the WITH RECOMPILE option, the user will get a time-out
> error?
>
> Thanks
>
> Ayad
>
>

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: SPs executes slower using SP3
    ... SQL Server does not have a seperate cache for each CPU, ... but it may have more than one copy of the execution plan if the plan is set ... > I viewed the execution plans from my workstation vs. my co-workers ...
    (microsoft.public.sqlserver.clients)
  • Re: query performance help needed
    ... Whether the execution plan is there or not has little do with it. ... It's very clear that you need to analyse your query plans, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: update 2 fields problem
    ... as the SQL Server optimizer will usually generate the ... This changes if the subquery has to be duplicated. ... order in which matches are found depends on the execution plan, ... still be affected by the UPDATE statement and the new values for Column1 ...
    (microsoft.public.sqlserver.programming)