Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and check if the plan of each changes when I add/drop an index. However, this may be too tedious if I have, say 100 queries. I was wondering what available alternatives (tools, scripts, etc.) exist and what are their pros/cons.

share|improve this question
1  
Any script would be specific to the vendor (eg SQL Server DMVs). There is no single tool, really – gbn Nov 19 '11 at 11:49
Yes, I'd like to know what are the options out there, regardless of the underlaying DBMS. As you mention, DMVs are one alternative for SQLServer. Other people may be using the hypothetical index feature of SQL Server. I'm trying to define the work-flows that people follow in this kind of situation. Maybe my question is too generic, in which case I could create one for each DMBS – ivotron Nov 19 '11 at 21:35
Related: dba.stackexchange.com/q/56/2660 – Nick Chammas Nov 21 '11 at 17:00
@NickChammas I see. Thanks for the link. Should I specify a concrete DBMS implementation then? Or do you think I can leave the question open? – ivotron Nov 21 '11 at 23:45
If that discussion gives you all the info you need then you can delete this one. Otherwise, if there's something more specific you still want to ask (either platform-specific, or regarding a detail in that discussion) then edit this question appropriately and leave it open. – Nick Chammas Nov 22 '11 at 0:09
show 1 more comment

1 Answer

up vote 2 down vote

The best way to do this for a DB2 database running on Linux, UNIX or Windows is use the DB2 Design Advisor.

This tool (included with DB2) allows you to specify a workload (which could include hundreds of queries), and it will analyze the workload and the existing database to recommend indexes and/or other physical design changes to optimize the workload.

share|improve this answer
Thanks a lot. In the same spirit of question dba.stackexchange.com/q/56/2660 I'd like to know, once I have a set of recommended indexes, which ones are necessary. Quoting from that question: "I am reluctant to take the plunge and blindly add the recommended indexes... I am looking for some advice on how to determine whether the recommendations are necessary or not". I'm also interested in this. In other words, how can I make sense about the recommendations besides looking at the output of the index advisor? – ivotron Nov 23 '11 at 9:53
The design advisor does not recommend indexes that the workload you supply will not use – it tests each index recommendation to be sure that the DB2 optimizer will use the index and estimates the cost savings. – Ian Bjorhovde Nov 23 '11 at 22:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.