Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Apr 11 '13 at 9:18
    
Thanks @MartinSmith for the quick response. I'll check them. –  Manish Mulani Apr 11 '13 at 9:40
    
Have you considered using parameterizied queries and plan guides? –  Remus Rusanu Apr 11 '13 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 Apr 11 '13 at 19:10
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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