Optimization

In db maintenance when you schedule optimizations, in addition to rebuilding
all the indexes does sql recompile all tables as well, which in turn should
recompile all stored procs accessing those tables to use the latest and most
efficient query plan? Thanks.Hi
SQL does not recompile the tables, there is no such functionality.
During a Index re-build, the statistics are updated if the setting is on for
the DB.
SP's are not automaticlly re-compiled. Unless you set your SP to use "with
recompile", (which is not optimal as this would happen everytime the SP gets
executed), the SP will only get re-compiled when SQL Server's criteria are
met (aged out of procedure cache or the self tuning detects that the
statistics have changed enough).
Regards
Mike
"mrdj" wrote:
> In db maintenance when you schedule optimizations, in addition to rebuilding
> all the indexes does sql recompile all tables as well, which in turn should
> recompile all stored procs accessing those tables to use the latest and most
> efficient query plan? Thanks.|||I believe Mike meant
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DE531A23-A31B-4EB2-9C7D-492C6656762A@.microsoft.com...
> Hi
> SQL does not recompile the tables, there is no such functionality.
> During a Index re-build, the statistics are updated if the setting is on
for
> the DB.
> SP's are not automaticlly re-compiled. Unless you set your SP to use "with
> recompile", (which is not optimal as this would happen everytime the SP
gets
> executed), the SP will only get re-compiled when SQL Server's criteria are
> met (aged out of procedure cache or the self tuning detects that the
> statistics have changed enough).
> Regards
> Mike
>
> "mrdj" wrote:
> > In db maintenance when you schedule optimizations, in addition to
rebuilding
> > all the indexes does sql recompile all tables as well, which in turn
should
> > recompile all stored procs accessing those tables to use the latest and
most
> > efficient query plan? Thanks.