MS SQL Server - Question about auto update statistics and stored proc recompilation

Author

Question about auto update statistics and stored proc recompilation

pshroads@gmail.com

2006-10-24, 6:31 pm

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!

Dan Guzman

2006-10-24, 6:31 pm

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!
>

Sponsored Links