Question about auto update statistics and stored proc recompilation

I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pr...005/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>