ricardo_chicas (6/4/2013)
explain stale stats in a brand new table, with a brand new clustered index with a run of update stats a few minutes ago, again not a stats issue
take a look at this article to help you understand the issue:
http://www.sqlservercentral.com/blogs/briankmcdonald/2010/11/05/how-stale-are-my-statistics_3F00_/
in your specific example,
when we did an insert on a brand new table as shown in my example above: 200K rows;
on creation, the stats are accurate.
you can see it with this:
DBCC SHOW_STATISTICS (temptable2,'field3' )
if you look, the distribution is very unique because of my randomization code)
now lets update 10% of the table(which i don't remember you mentioning you did) to the exact same value, and also run the same statistics query
SET ROWCOUNT 10000
UPDATE temptable2 SET field3 = 'bananas'
SET ROWCOUNT 0
DBCC SHOW_STATISTICS (temptable2,'field3' )
now, the distribution didn't change! but we KNOW there's one value in there that is certainly no even close to being unique any more...THAT's the stale statistics that will could potentially throw off a query; during testing, that didn't happen, bu the underlying issue is this cause.
Lowell