I was reading Ian Stirk's Uncover Hidden Data to Optimize Application Performance article on MSDN Magazine, and now I am wondering if table indexes creation could be automated, as Google AppEngine does for its BigTable.
There is any tool or Sql Server feature that automates table index creation?
No, as far as I know, there's no feature in SQL Server that enables automatic table index creation.
I wouldn't think it to be a good idea, either, because getting the right indexes in place will depend on a multitude of factors, hardly any of which can be really truly automated.
Sure - you can place a primary key on any column called "ID" - until you run into a case where you need a primary key on something else....
Sure, it makes sense to index foreign key columns in the child table - but sometimes, the added overhead for INSERTs can more than offset the gains of having the index.
Getting the right indices in place is just way too dependant on your actual usage and a lot of dynamic, usage parameters (and design decisions on your part, too) so I'd be surprised if any solution would really work all that well...
Marc
I am not aware of any tools, and the best way to create indexes is to actually check the queries and their execution plans manually. I don't think that an automated tool will ever be as good as a few good DBA's analyzing the data together with the Profiler.
But, if you feel like giving a shot yourself, I recommend that you start looking at the performance views in the SQL Server.
Start with the function sys.dm_db_missing_index_columns, that should give you a hint of which columns that could benefit from being indexed.
sys.dm_db_index_usage_stats could show you which indexes are useless, or could be optimized as well.
sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_query_stats and sys.dm_exec_sql_text should show you queries performed and how they perform, and together with information from the other tables, you could probably find out which ones that need more work.
Actually, I vaguely recall some wizard that can help you to analyze performance in the Profiler, probably not automatically, but it might be possible to put that in a Maintenance plan.