Re: sp_recompile

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 14:36:14 +0100

You don't really need sp_recompile unless you add indexes. Stored procedures
that access tables are automatically marked for recompilation when the
schema changes or the statistics on columns or indexes that are used in the
execution plan of the stored procedure.

The downside of running sp_recompile on a regular basis is that it only
marks the objects for recompilation, and only when the stored procedures are
actually run will a new execution plan be compiled. If you have a stored
procedure with an execution plan that costs a lot of time to compile, the
first user who calls that stored procedure after recompilation will notice a
delay. 

-- 
Jacco Schalkwijk
SQL Server MVP
"JOE" <removejaylou@aol.com> wrote in message 
news:380401c4a623$c2a42520$a501280a@phx.gbl...
> Hi all,
> Is there any downside to running sp_recompile on all
> objects in my data base once a week?  Should I run this
> against system objects as well?
>
> TIA,
> Joe

Relevant Pages

  • RE: Schema Locks and heavy volumes
    ... the recompilation of stored procedures. ... When the stored procedures are in the process of recompilation, the Schema stability locks is ...
    (microsoft.public.sqlserver.server)
  • Re: Schema Locks and heavy volumes
    ... SCH_S locks are held during recompiles to prevent schema modifications that, ... > the recompilation of stored procedures. ... > When the stored procedures are in the process of recompilation, ...
    (microsoft.public.sqlserver.server)
  • Re: Newbie: Stored Procedures and Or Datasets ?
    ... >the proc is executed for the first time, the execution plan is then cached ... whole lot of advantages over a standard ad-hoc T-SQL ... An Evaluation of Stored Procedures for the .NET Developer ... SQL Server, execution plans are cached for all T-SQL batches, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... stored procedures are going to be faster than ... performance from dynamic sql (sql that you generate on the client and ... as well as if the queries are the same and a number ... another execution plan would be faster because statistics changed. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Performance Tool Question
    ... tool has a performance-optimizer ... Open a new window in query analyzer and type execute myprocedurename (type ... The execution plan will show all scans to be performed and will give hints ... > I am looking for a tool that will help optimize TSQL and Stored Procedures ...
    (microsoft.public.sqlserver.server)