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?