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?
Best Answer
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.