Re: Execution plan reuse

From: Prasad Koukuntla (prasad.koukuntla_at_realpage.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 13:37:35 -0500

Thank you Kalen.
Is there way to execute SPs without actually sustaining the affects of it?
This may not sound like a reasonable request but I want to speed up the
response time of my SPs by keeping the execution plans in cache before the
users call these SPs.

Thanks,
Prasad Koukuntla

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:e1LeiyIrEHA.2596@TK2MSFTNGP12.phx.gbl...
> Hi Prasad
>
> An execution plan for a stored procedure can be reused by any connection
> calling that procedure, as long as the SET options in the environment are
> the same. There are other factors that can force a recompile so the plan
> isn't reused, but that is a big topic.
>
> There is a lot of information in the Books Online about plan caching and
> reuse. Make sure you have read of it.
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Prasad Koukuntla" <prasad.koukuntla@realpage.com> wrote in message
> news:%231XyWvIrEHA.1204@TK2MSFTNGP12.phx.gbl...
> >I want to confirm my understanding of execution plan reusage. The
execution
> > plan for a stored procedure is created when the SP is first called and
> > kept
> > in the buffer. This execution plan is resused if the same stored
procedure
> > is called from the SAME CONNECTION. It would NOT resue the execution
plan
> > if
> > the same SP is called from a different connection. Please confirm this.
If
> > this is not true, can one execution plan be reused by several
connections
> > if
> > the procedure call remains the same. From my experience, my understaning
> > of
> > reuse appears to be correct.
> >
> > What I want to do:
> > If my understanding is wrong and the execution plan can be resued by
> > several
> > connections, I want to explore the possibility of executing my
frequently
> > used SPs once in the morning so their execution plans are placed in the
> > cache and available for all users so the response time is faster. If
this
> > is
> > possible, is there a way to generate the execution plan for these SPs
and
> > keep it in the cache without actually making the data modifications the
> > stored procedure code might be making if it were actually to be
executed.
> >
> > Please let me know.
> >
> > Thanks
> > Prasad Koukuntla
> >
> >
> >
> >
>
>

Relevant Pages

  • Re: MSDE Slow in executing Stored Procedures
    ... I've ran SQL Query Analyzer and executed both stored procedure and sql ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: MSDE Slow in executing Stored Procedures
    ... Perhaps you can summarize you'd findings (MSDE vs. SQL Server and INSERT vs. stored procedure)? ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: index bloat?
    ... execution plan is, how can I get the query optimizer to generate it - *without the hints*? ... Because some users of the DB are using report designing software that will just generate the join query with no hints, and I don't want them sitting there for hours when they don't have to. ... Does the optimizer not choose the merge join because it requires a bookmark lookup? ...
    (comp.databases.ms-sqlserver)
  • Re: Select Statement: Join vs Inner Select
    ... By using a stored procedure, you are actually "hiding" the implementation ... >> will have to prepare an execution plan for each call. ... >> this as a stored procedure and call the stored procedure from your app. ...
    (microsoft.public.sqlserver.programming)
  • Re: Frustrating Execution Plan Analysis
    ... >I've always been under the impression that Query cost is directly related to ... But if the execution plan isn't making sense, ... SQL Server will make a plan based on assumptions about what will ...
    (microsoft.public.sqlserver.programming)