1

Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?

If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?

note: The need for the FULLSCAN comes by proved suboptimal plan generation when using non-FULLSCAN statistics.

CAFxX
  • 28,060
  • 6
  • 41
  • 66
  • Tell us more about the sub-optimal plan. Perhaps share the actual query and schema with us too. – John Sansom Oct 20 '11 at 12:45
  • 2
    I don't really see what that has to do with the actual question... anyway, consider that the suboptimal plan used two clustered-index scans whereas in the optimal one they are not present (replaced by seeks on the same indexes). Just FYI, the two indexes were PKs of tables having 280M+ and 55M+ rows, respectively. – CAFxX Oct 20 '11 at 12:57
  • 1
    It *is* relevant An index scan is poor indexing or poor predicates Not statistics most likely – gbn Oct 21 '11 at 17:31
  • 1
    @gbn If your theory can explain why, immediately after manually updating the statistics and invalidating all execution plans, the average query duration dropped to 1/100th of the average duration we saw up to that moment then I could actually take into consideration John's request (but I won't be nevertheless able to share the queries and schemes, for they are proprietary) – CAFxX Oct 23 '11 at 06:37
  • Also, take into consideration that our findings have been verified by a Microsoft consultant (that was called in for unrelated DB problems). – CAFxX Oct 23 '11 at 06:40
  • @CAFxX: Ask an MS consultant then to answer your question – gbn Oct 23 '11 at 13:50
  • 1
    @gbn That's what I did on Friday, since no-one seemed to have a proper answer here on SO or on DBA. Unfortunately, his answer was that there's no way known to him to force `FULLSCAN` in the automatic statistics. We will probably have to fire off planned `UPDATE STATISTICS` multiple times per day, at least on the tables that change the most. – CAFxX Oct 23 '11 at 14:32

1 Answers1

0

In the end, it looks like it's not possible (see my last comment on the question for details).

CAFxX
  • 28,060
  • 6
  • 41
  • 66