Thread: Feature Suggestion/Index tuning wizard that automatically decide on indexes
Feature Suggestion/Index tuning wizard that automatically decide on indexes
From
"Brian Maguire"
Date:
I don't think that this has been up for discussion yet but I think it would be good concept/feature that someone might want to take up or shoot down. However some may have already. I am not sure where it falls PGAdmin, psql, optimizer, gborg... all. Recommended Feature: Index Tuning Wizard Description: The Index Tuning Wizard simplifies the task of identifying which indexes to create in a table and also optionally generates scripts to create them by analyzing a user-supplied workload. Various modes are offered for tuning both indexes and indexed views. Rational: Why this would be a killer feature... because it can save time and improve the performance of the database based real data. Sure you could say that the DBA should be able to do this but it really is a good job for a machine because it analyzes real production data workload queries and statistics quickly. Not to mention not everyone has a DBA on staff and the databases are managed by developers. Comments: MSSQL Server has an excellent model for this. It has the ability to take the transaction logs and statistics and analyze them and recommend indexes and create them. Various URLS Description of MS SQL's Feature http://www.sql-server-performance.com/index_tuning_wizard_tips.asp MS Feature White Paper http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/prodt echnol/sql/maintain/optimize/tunesql.asp A discussion blurb regarding it http://weblogs.flamefew.net/bayard/archives/000406.html
On Mon, 2003-06-09 at 12:47, Brian Maguire wrote: > I don't think that this has been up for discussion yet but I think it > would be good concept/feature that someone might want to take up or > shoot down. However some may have already. I am not sure where it falls > PGAdmin, psql, optimizer, gborg... all. > > > Recommended Feature: Index Tuning Wizard > > Description: The Index Tuning Wizard simplifies the task of identifying > which indexes to create in a table and also optionally generates scripts > to create them by analyzing a user-supplied workload. Various modes are > offered for tuning both indexes and indexed views. > > Rational: Why this would be a killer feature... because it can save > time and improve the performance of the database based real data. Instead of an Index Tuning Wizard, instrumentation that would allow the collection of SQL statements, frequencies, IO counts and CPU usage. The data collected from such collections could then be analysed and used as the DBA balances the number of indexes versus number of inserts/updates/deletes, and the time of day when the query occurs. Yes, such instrumentation would be needed for the Tuning Wizard itself, but a human can usually spot nuances better than a program can. -- +-----------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | Regarding war zones: "There's nothing sacrosanct about a | | hotel with a bunch of journalists in it." | | Marine Lt. Gen. Bernard E. Trainor (Retired) | +-----------------------------------------------------------+