Today we have a guest editorial from Andy Warren. This editorial was originally published on June 8, 2011. It is being re-run as Steve is out of town at SQL in the City.
Going all the way back to SQL Server 7 we've had the automatic creation and automatic update of statistics. Both are interesting options that are on by default, and seldom looked at or thought about. Occasionally I'll run into DBA that turns them off, but in practice if you schedule updates at decent intervals and create the indexes you need they seldom do anything besides acting as a safety net. They guarantee that you have a minimal level of statistics and they have been updated within some reasonable amount of change.
I like safety nets. I like the idea that the system will save me (or the new user) from serious problems, but do so in a way that I can disable if I want (switches for the power users!) and that remain quietly out of the way. Do we have enough of them in SQL Server? Why doesn't the installer set up at least a recurring full backup and index rebuild unless the user unchecks the box? We can find out about missing indexes, why not more recommendations on things like missing primary keys? Why not built in functionality that warns about jobs that exceed the average run duration?
Would you be in favor of more safety nets if you could disable them or tweak them? Or do you think that SQL should remain in the hands of only those who know what needs to be done and how to do it?