April 13, 2015 at 7:58 am
I have a daily scheduled job that uses the update statistics with full scan command on a few big tables. This job runs at early morning hour. It has been adequate and everything was fine. Lately I encountered a few queries that use these big tables starts running super slow. Based on the execution plan, instead of using a specific index for the search (index seek), it chose the clustered index scan instead. Upon I run the scheduled job again, then the execution plan shows it chose the specific index correctly again. For one of these big tables which has 13 million records, daily inserts are about 30,000 to 50,000 record. Daily update would be similar or less.
My question is that I understand optimizer has its own logic to determine when to update statistics, but I consider our % change to that big table is small (I could be wrong), is there something else wrong that I should look into instead of running the update statistics with full scan every time that query runs slow? By the way, does update statistics with full scan cause any locking problem besides creating high IO activity?
We are using SQL 2008 STD 64 bit on Windows 2008 enterprise 64 bit
April 13, 2015 at 9:24 am
If you're getting lots of inserts there's a good chance that during the day a healthy chunk of the information added to the table are falling outside of the statistics. When the data can't be found in the stats, the optimizer will make radically different choices. So, possibly, you can update stats more often during the day (maybe sampled instead of FULL SCAN to avoid contention, and yeah, there is some). Also, I'd suggest looking into traceflag 2371 to change when the automatic update of statistics occurs. By default, you have to modify 20% of the table to see a change. If you have a large table, this may take a while. The traceflag changes that behavior. Another option, not my favorite, is to look at KEEPFIXED PLAN hints. I find these to be a little dangerous, but it is an option. I'd make it the last possible option though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply