Why isn’t there automatic in SQL Server?

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.

4 thoughts on “Why isn’t there automatic in SQL Server?

  1. Agreed. I think the best alternative is having two approaches: an "expert" mode with a set of commands or an auto-generated property page (code would sniff over object methods and properties) and a "Wizard" mode where the user is led through a series of screens with explanations and ramifications explained.

    In both cases I think there should be a "script this in T-SQL" and "Scrip this in PowerShell" button so that the process can be "templated" for later.

    Of course, I’m not on the product team any more. :)

  2. funny…Microsoft is not afraid to allow for us to schedule the ability to complete fragment (sorry, i meant to say shrink) our database inside of a maintenance plan…so why not offer us the ability to schedule for something, say, index optimization?

    how about you let me pick the time and tables on which i want the system to go in and remove indexes that are not being used (high number of writes versus reads), and/or suggest new indexes based upon the quer activity? even if you didn’t want to headache of doing the work, you could at the very least schedule something to produce an output of suggestions that could be easily implemented.

    yeah, yeah, yeah…i know, it’s not that easy. still, it would seem there is always room for some improvements as we get closer to a system that can actually heal itself.

  3. @SQLBatman Well, you can actually do that today. Have a job that looks at a time-series analysis of the output from sys.dm_db_index_usage_stats and then drops them after a complete business cycle has elapsed. And you can schedule fragmentation removal. What I’m saying is that its hard to have SQL Server do it for you automatically, even with the scheduling in place.

  4. I always figured that the way to implement an Auto* feature in SQL, given the generally risk-adverse and suspicious nature of most DBAs would be this:
    Implement the feature with a 3 position "switch":
    OFF: I’m never going to use your feature, so don’t take up cycles thinking about it.

    Advisory: Tell me what you think should happen and why, either in the form of a script or a "make it so" button. That way I can second guess SQL’s logic, and hopefully gain confidence in the heuristics

    Full Auto: OK, OK I trust that SQL knows what it’s doing (or I have no time to look at this database and whatever you do will probably be no worse than me ignoring it), just do it.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.