When selecting from table variables, the estimated row-count is always 1, unless a predicate that evaluates to false, and doesn’t have any relationship to the table variable, is applied (such as WHERE 1=0), in which case the estimated row-count evaluates to 0.Solution: Don’t count on using table variables for temp tables if they are likely to contain more than a few rows.You see the internal table DM_TRAN_CURRENT_TRANSACTION that is called by invoking OPENROWSET and an estimated row-count of 1000, which is far from reality.Picture 2: Row-count estimation for TVF Solution: If possible, give the optimizer some support by specifying the row-count through the TOP(n) clause.This can occur, if you use OPENROWSET or OPENQUERY for remote data access.But you may also face this issue out of the blue, when working with DMVs.This will only be effective if n is less than the Estimated Number of Rows, 1000 in our example.
It’s perfectly understandable that SQL Server has no idea of any row-counts from that remote table, since it is residing in an Oracle database.
If there’s no statistics, the optimizer will have to guess row-counts rather than estimate them, and believe me: this is not what you want!
There are several ways of finding out from both the estimated and actual execution plans whether the optimizer comes across missing statistics. The there will be an exclamation mark in the graphical execution plan and a warning in the extended operator information, just like the one in Picture 1.
Solution: If you experience performance problems with queries that have to search through XML data, or filter spatial columns e.g., XML or spatial indexes may help.
But that’s another story and beyond the scope of this article.
Problem: For table variables, statistics will never be maintained.