Here's an interesting one that I can't fathom. I have updated a large DB with UPDATE STATISTICS WITH FULLSCAN. However, the queries run much faster if I run a simple UPDATE STATISTICS. The query plans bear this out - one of the queries takes 5 mins after the FULLSCAN but 30 seconds after updating the stats without any options. I've looked at the fragmentation of the indexes and all seems well. So what situations produce this behaviour of a FULLSCAN producing duff query plans?
Welcome to the forum... What index the query is using after FULLSCAN vs default? Based on the available stats optimizer might be using different index resulting the query to run slow...
http://sqlserver-qa.net/blogs/perftune/archive/2007/05/29/how-to-detect-a-table-fragmentation.aspx & http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx BTW what is the SAMPLE value you have mentioned with UPDATE STATISTICS? Also post the query that is used and chekc its execution plan too.
