4

We have a few tables in our database. Since most of our queries are date based, we have constructed clustered index on 'date + primary key (surrogate)' combination and we enforce joins on date as well in our queries. We have enabled 'page compression' on these tables/indexes.

Data is bulk inserted into these tables in the order of ~ 200k rows per day.

We have a query which joins a few of these tables and has date filter for two consecutive days.

Problem : This query times out(no data returned even after minutes) for the latest dates for which data was bulk inserted, but works perfectly fine for the older dates. Further debugging revealed that the execution plan chosen by the optimizer changes (and is not optimal) when we pass latest two dates in the filter.

Currently, we are able to solve this issue by just running 'Update statistics' on these tables, post which optimizer chooses good old plan for the latest dates as well.

I noticed from the stats that, out of ~5 million rows, only ~300k rows are sampled for stats purpose.

Has anyone faced a similar problem of having to update statistics so often. Is there a way, I can fix this problem or this can be avoided?

Improve this question
4
  • 3
    This is a known issue with ascending date columns. For a 5 million row table you would need to insert 1 million rows (20%) before the statistics would be auto updated. See the answers here. The Microsoft response on this Connect Item looks intriguing though.
    – Martin Smith
    Commented Apr 11, 2013 at 9:18
  • Thanks @MartinSmith for the quick response. I'll check them.
    – Manish Mulani
    Commented Apr 11, 2013 at 9:40
  • Have you considered using parameterizied queries and plan guides?
    – Remus Rusanu
    Commented Apr 11, 2013 at 14:42
  • @RemusRusanu I found a way to pass date as parameter to my query without impacting the plan and then use Optimize for option. Thanks.
    – Manish Mulani
    Commented Apr 11, 2013 at 19:10

1 Answer 1

Reset to default
3

Data is bulk inserted into these tables in the order of ~ 200k rows per day... out of ~5 million rows

That's 200k new rows that do not have any stats for, so querying for those values will make the optimizer come up with scans because of the outdated stats do not inform it that the new values are in the range sampled. Force a stats update after the bulk insert.

Not sure autostats can help given that 200k inserts fall bellow the 5 million 20% cadinality trigger, see Statistical maintenance functionality (autostats) in SQL Server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.