Execution Plan in SQL Server?

Question

In which situation estimated number of executions and actual number of execution differs? Could anybody list out the occasions?

Thanks In Advance

Answer 1

In general, distribution statistics are responsible for cardinality estimates. In addition to said by TomTom, until very recently SQL Server has always assumed that table variables contain exactly 1 row. Needless to say, sometimes this resulted in an execution plan being horribly wrong - ganz falsch, practically.

If you use table variables that have a lot of rows, switch to at least 2012 and set a special trace flag that enables correct estimate: http://support.microsoft.com/kb/2952444

Answer 2

• Shitty statistics which simply do not match reality because they are outdated.

• Complex queries where the math behind assumptions goes wrong. Statistics entries are always simplified (A histogram) and some values may not fit into it.

Both can have part in it.