October 26, 2011 at 2:10 pm
I'm having an issue in my environment that I have never seen before. We start seeing performance issues and when we check the stats the sample rate is pretty low. Auto Update stats seems to have kicked in and the sample rate is anywhere from 10% - 50%. We update the stats with fullscan and this seems to resolve the issue.
We run an UPDATE STATIISTICS with FULLSCAN on all tables every weekend. We execute a rebuild of indexes nightly, but only for indexes that hit a specified defragmented threshold. We also update stats with fullscan on some tables nightly, they seem to go out of date frequently.
We do have auto update stats turned on (Honestly, I have never worked in an environment that had it off). I'm wondering if I should turn this off, but I'm very nervous about it. Maybe I should update stats with NORECOMPUTE?
I also question the code. Why would it require fullscan every time it executes? This doesn't make sense. I'm I missing something? Shouldn't the optimizer work the same with 10% or 20% sampling, whatever it decides to resample with? If not, why resample?
October 26, 2011 at 2:26 pm
What you're seeing is not that uncommon, especially if you have tables with data skew, where some values are present more than others.
If you need fullscan, you need fullscan. If you identify specific indexes or stats with that problem, set norecompute on those (just those) and manually update them. Weekly is surprisingly seldom, most of the time by the point people get this kind of problem they're updating daily.
I've seen a case where, to prevent sudden performance changes, a scheduled stats update with fullscan was needed on a specific table every 15 minutes. It was a high-volume, high-insert environment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply