I was in a discussion earlier today, and it's one I've had lots of times in the past – both inside and outside Microsoft and the SQL team: why doesn't SQL Server do automatic <defrag, index creation, refactoring, de/normalization, backups, CHECKDBs, etc>?

Some of these were considered while I was still on the team, and I was *very* cautious about them. Here's why, taking automatic table de/normalization as an example (this morning's discussion). Assuming the logic to do the de/normalization based on usage patterns can be worked out, here's why I don't think it will ever happen (just off the top of my head):

  • If it's wrong even once, no-one will ever use it again.
  • When should SQL Server change the table definitions? What time of day is best?
  • Where should the new table be placed? In which filegroup?
  • What if there's no space to do it – should the database autogrow because of an automatic process? And then should it shrink afterwards?
  • How far should the change plan parallelize?
  • Who manages the transaction log size if the table is really large? Automatically take log backups? What if there's no space for the log backups? What about space on the log shipping secondaries? And the time required to roll-forward the log on the log-shipping secondaries?
  • What about the impact of extra transaction log on database mirroring? Both in terms of the huge SEND queue preventing log truncation, and the huge REDO queue preventing the mirror coming online quickly?
  • What if a cluster failover occurs during the operation and the rollback prevents the database coming online within the company's RTO?
  • What if there are an replication topologies setup? How do they get quiesced for the schema change?
  • How does the app get changed to handle the different schema, or do views get created automatically? Indexed views or not?
  • What if Change Data Capture is running? Should it automatically create a new capture instance? What if both are already in use?
  • How do SPs get updated to the new schema? Build in the datadude engine as part of SQL Server?

My point: it's a lot harder than you think to just put some automatic behavior into SQL Server. 

Now, saying that, I hope that some of the others do happen at some point, but with lots of config parameters so I can control them.