Mark Gritter (markgritter) wrote,
Mark Gritter
markgritter

Why aren't SQL databases self-healing?

Maybe they are and it's just not a feature set I'm familiar with? The inquiry comes because I spent much of today tracking down a performance problem which turned out to be a frequent SQL query that was not efficiently indexed. (In this case it was 7-day trending--- queries to find the most recent data were fine but those to find the earliest data point for the VMs that didn't yet have 7 days worth of data were not OK.)

So, it's the 21st century--- why doesn't the database build its own indexes to match the observed load? I realize there's a space/time tradeoff that humans *sometimes* want to make. But why not set a limit and ask the optimizer to do its best within that limit? Or at least have a recommendation engine that's straightforward to use.

A little searching did turn up a set of scripts released by Microsoft in 2006, and a StackOverflow question on exactly this topic: http://stackoverflow.com/questions/231528/do-any-databases-support-automatic-index-creation The answer seems to be "it is a hard problem, and it's hard to gather sufficient information about the relative value of various queries or updates." That is, frequency != value. But, precisely because it is a hard problem is why there should be more tool support... Some of the comments on StackOverflow strike me as having somewhat the flavor of "well, you *could* have the runtime manage memory reclamation for you, but it'll always be an inferior solution."

On a related note, I am constantly amazed at what people (including developers!) are willing to put up with in terms of response time. In my mind, if some reasonably-sized operation takes more than 30 seconds, it's an indication that there's a problem that needs fixing, not a reason to bump the timeout. And "reasonably-sized" these days should be thousands of entities.
Tags: programming, rant, software, sql
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 5 comments