Hi,

When I ran SQL Profile on one of SQL 2005 server, it captured a lot of
sp:Recompile ( 2 Statistics changed) anf they all belong to the same
statement in the same SP. The statement is just a SELECT statement join 3
table and a temp table, could the statistics of one joined table out of
sync? How do I make sure that's the case and how do I reset?

TIA

Danny
http://www.microsoft.com/technet/pro...05/recomp.mspx



"Danny Ni" <dndn@yahoo.com> wrote in message
news:u6k1hJj5IHA.4364@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> When I ran SQL Profile on one of SQL 2005 server, it captured a lot of
> sp:Recompile ( 2 Statistics changed) anf they all belong to the same
> statement in the same SP. The statement is just a SELECT statement join 3
> table and a temp table, could the statistics of one joined table out of
> sync? How do I make sure that's the case and how do I reset?
>
> TIA
>
>
>

> When I ran SQL Profile on one of SQL 2005 server, it captured a lot of
> sp:Recompile ( 2 Statistics changed) anf they all belong to the same
> statement in the same SP. The statement is just a SELECT statement join 3
> table and a temp table, could the statistics of one joined table out of
> sync? How do I make sure that's the case and how do I reset?

Below is a relevant excerpt from the article Uri posted. I suggest you
consider a table variable instead of temp table if the recompilations are
causing you pain.

"Best Practice: Because a change in cardinality of a table variable does not
cause recompilations, consider using a table variable instead of a temporary
table. However, because the query optimizer does not keep track of a table
variable's cardinality and because statistics are not created or maintained
on table variables, non-optimal query plans might result. One has to
experiment whether this is the case, and make an appropriate trade-off."

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Danny Ni" <dndn@yahoo.com> wrote in message
news:u6k1hJj5IHA.4364@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> When I ran SQL Profile on one of SQL 2005 server, it captured a lot of
> sp:Recompile ( 2 Statistics changed) anf they all belong to the same
> statement in the same SP. The statement is just a SELECT statement join 3
> table and a temp table, could the statistics of one joined table out of
> sync? How do I make sure that's the case and how do I reset?
>
> TIA
>
>
>