Feasibility of Automated and Continuous Performance Tuning in the future


First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, create/modify indexes, update statistics, etc which is a tedious task.

My question is very open, but I'd like to know if there is already a DBMS that automatically and continuously tunes itself (create/modify indexes, update statistics, etc) based on the data that lives in the database and its usage? If there isn't anything like that would we expect to see automated systems like that in the future? Given the increasing rate at which we're accumulating data these days seems like it will be a must in the near future.

What are the current impediments or negative side of doing that?

I understand that modifying the queries and stored procedures to be optimal may be hard to automate an more of a human task. But I believe good level of tuning automation could be done on the database side.

Best Answer

Perfect tuning is an art and the underlying system (meaning the data and its usage patterns) is almost always evolving in ways automation can't predict or react to. Tuning is also based on past activity; the system can't predict tomorrow's growth or usage patterns, but a DBA can to some degree. We're a long way away from decent artificial intelligence and I highly doubt this will be one of its first applications when it does arise.

For a while, SQL Server was marketed as a self-tuning system, "No DBA required." I'm not sure that's a good thing even if it were possible, but it's certainly not marketed that way today and it doesn't even seem to be a secondary or tertiary goal of Microsoft. Even the new Query Store feature in 2016 is designed to allow you to make decisions about query plans and regressions, not to make them for you. As a monitoring company we, too, strive to provide you with the information for you to make decisions, and try to make the pain points extremely obvious, but we stop short of telling you to change index x and tune query y.

Think about self-driving cars for a moment - while they may reduce accident rates slightly overall, they're going to put millions and millions of people out of work, from cab drivers to delivery people to truckers... never mind that software can't think like a human. I like cruise control, but it can't predict hills or know that I'd like to pass this truck at a slightly faster pace, so I don't always rely on it. That's pretty mature technology but it's far from perfect, even in its most advanced implementations (like maintaining a certain distance from the car in front of you, adjusting for its speed - I don't know if that's a good thing, either).