12

I've recently become involved in maintaining a set of databases with some interesting index problems. One of the ones that aggravates me the most is the differences in the indexes between development, test, model and production machines. Since the differences make tuning queries rather difficult syncing them up is one of my first projects.

As I've compared the test and model environments I've noticed that most of the indexes in the model environment have STATISTICS_NORECOMPUTE set to ON while those in test do not. In all of the environments there is a nightly job that updates the statistics of all of the databases.

I've never dealt with STATISTICS_NORECOMPUTE before so here are my questions. Are there any best practices when dealing with this setting? If I'm doing statistics updates at the end of the day is it best to turn STATISTICS_NORECOMPUTE in all of the environments on all of the indexes? Or is there a good reason not to?

EDIT: I've found one of Kimberly Tripp's blogs on the subject here that seems to suggest that STATISTICS_NORECOMPUTE should be used sparingly at best. But I'm still concerned about turning it off globally. Has anyone tried this and what did they experience?

1
  • 1
    One good case would be using STATISTICS_NORECOMPUTE=ON and FILLFACTOR=100 for read-only lookup tables that are only changed by DBA's using a script that does an INDEX REBUILD with FULLSCAN after the changes; then the table's in optimal shape with optimal statistics, and with no other changes, there's no reason to even consider recomputing statistics, or leaving space to reduce page splits on future changes. Apr 7, 2014 at 3:59

1 Answer 1

7

It's really a situational thing that you want to look at per-table or per-index, and you really need to find out what's in production before taking any action. When in doubt, use what's in production in the other environments, too, even if it means using a bunch of crazy settings. You just can't get a good feel for how production will behave if things are different in test or dev.

Anyway, the general recommendation to leave auto-update stats turned on (STATISTICS_NORECOMPUTE = OFF, which is the default) is for safety reasons, because if this is turned off and nothing is manually updating the stats, the result could be really horrendous execution plans that never change after they're first created (and don't get invalidated for other reasons later on).

You said auto-update stats is turned off for most indexes (I think I originally misread that as all, not most). For the indexes with auto-update stats still enabled, does this setting make sense given the activity on those tables? I would expect that these are higher-activity tables. It's possible a lot of work went into figuring that out, and it may be worth keeping (or strongly considering) those settings. At the very least, make a note of which stats these are, because that information could come in handy down the road.

Thinking about it more, I will say that the current strategy does make sense. Is it better than leaving auto-update stats on for everything? It seems someone thought so, to the point that it was worth the ease-of-management tradeoff of having an associated SQL Agent job.

If the idea was to have fresh stats available without blocking queries (like this), you could consider turning auto-update back on for everything, and then also turn on AUTO_UPDATE_STATISTICS_ASYNC as well. Then probably change the job schedule to run once/week instead of daily, as you still do want stats updated WITH FULLSCAN periodically.

I might just leave it, though, as you probably have bigger fish to fry if the indexes themselves are different between environments, and the stats rebuilds aren't too painful. What's there now does make sense; you just need to make things consistent across environments. It's probably marginally better than the simpler settings I suggested, at the expense of more work being done. But find out what's in production, tend towards using that, and move on to more important things; revisit this when you're at the point of needing to more finely tune performance -- the best stats in the world won't save a query that's missing a critical index.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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