newbee needs help sp:recompile

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny Ni - 15 Jul 2008 05:22 GMT

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

Reply to this Message

Uri Dimant - 15 Jul 2008 07:31 GMT

Danny
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

> Hi,
>
[quoted text clipped - 5 lines]
>
> TIA

Reply to this Message

Dan Guzman - 15 Jul 2008 13:45 GMT

> 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."

Signature

Hope this helps.

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

> Hi,
>
[quoted text clipped - 5 lines]
>
> TIA

Reply to this Message