As a database professional one of the things that we should be familiar with is the use of indexes.  In SQL Server an index helps to improve query performance – when properly implemented.  There are some penalties in play when an index is created.  These penalties are typically associated with the insert update and delete operations.  Other penalties might revolve around a poorly defined index that may cause more work to be done for a given query.  As a database professional, it is within our best interest to strike a harmony between benefit and penalty through analysis and tuning of these indexes.  We want the benefits to offset the penalties for the index to be of worth to the query and system.

In SQL Server we have been given an array of tools over time to help us with this effort.  Microsoft gave us the Index Wizard in SQL 2000.  Then we got a decent upgrade with the Database Tuning Advisor in SQL 2005.  Microsoft has been attempting to continue to make a better more agile product for the database professional so s/he can spend time doing other equally important duties.  It appears that we now have a new tool to help us do just that.

First, let’s recap the predecessors.

Index Tuning Wizard

This tool was born in SQL 2000.  The intent was to give the Database Professional a view into the big picture.  Not just look at one index for one query isolated from other tables, views, and queries.  When used properly, this tool could give the professional a decent idea of the indexes that were needed throughout the system.  A common faux-pas was a blanket acceptance of the proposed indexes – good or bad.  Yes, this tool did recommend many bad indexes.  You can learn more about the tool if you would like, from here.

Database Engine Tuning Advisor

This tool was introduced by Microsoft with SQL 2005.  DTA made significant advancements over the predecessor.  The main purpose is much the same as the Index Tuning Wizard.  DTA did produce more reliable results (IMHO) and seemed to be more efficient at doing it.  Unfortunately, there was still the possibility of getting some bad recommendations.  And yes, the bigger issue is that there was blanket acceptance of all indexes.  You can learn more about this tool here.  This tool is available in SQL 2005, 2008 and 2008 R2.

GAIHA (pronounced like GAIA / GAYA)

GAIHA is Genetic Algorithm Index Heuristic Automation.  This name was chosen for multiple reasons, one of which is the pronunciation similarity with Gaya.  For some Gaya infers “enlighten.”  The hope for this product is to really help the database reach that next level and appear to be enlightened because it runs that much more seamlessly and effortlessly.

Rumor has it that GAIHA is on the docket to be released with a future version of SQL Server.  This tool has the same prime directive as the predecessors.  As the name implies though – it’s like DTA on steroids.  Furthermore, it is entirely automated.  Just like you can enable auto-update of statistics you will be able to enable GAIHA.  Much the same as new statistics get created based on data usage and data volume – indexes will auto-create based on query usage.

The automation of GAIHA will use advanced algorithms and heuristics.  Think of the heuristics as the recording of usage patterns and the system learning how the system is used over and over again.  Then it triggers an algorithm to run hundreds of generations of possible indexes and then picking one that would be the best fit.  Your query that ran in ten seconds today will run in ten milliseconds tomorrow.  What’s more, all you did was use the system.

Is there the potential for bad indexes – yes.  Would you still be able to create your own indexes – yes.  If enabled, would this be entirely maintenance free – no.  I don’t imagine it to be entirely maintenance free.  Take the auto update on your statistics for example.  Though you may have that enabled, you still have to do manual statistics updates on occasion.  I envision the same kind of behavior in the future.

Conclusion

There you have it.  The tools keep getting better and better.  This is true especially when the next generation tool is entirely fictional.  There is no such tool and it was completely derived from imagination.  Would it be nice to have – sure.  I envision the DTA being many magnitudes better if it were recreated to be similar to a product by Quest called TOAD.  I liked the feature in TOAD from many moons ago that allowed me to input a query and see it refactored 100 different ways with varying performance gains.  This would be extremely useful for many Database Professionals.

If you would like to see a genetic algorithm at work in SQL Server, check out this blog by Gail Shaw.

Happy April Fools.