Query optimize tool

  • Hi,

    Are there any tools that would check for query optimzation and suggest ways to improve it. By looking at execution plan, we can try to optimize the query, but for someone with little experience are there any tools.

    -R

  • The really really short answer is

    Nope

    I have not seen anything.

    IMHO optimizing is as much an art as it is a science.

    The easiest way is to run the index tuning wizard, and load test your application, at the end of that the ITW can give you suggestions for indexes, but I do not know of anything to help with actual structure of a query

  • Well, I remember with KPN - a Dutch telco in the Netherlands - they were using something like "SQL expert" in order to optimize their databases. This was something they bought of the shelf. So applications like you requested DO exist. Maybe the best place to start is http://www.orafaq.com/tools/leccotech/sqlexpert.htm.

    @win

  • This sqlexpert product is now a part of the Quest Central for SQL Server tool marketed by Quest now.  see http://www.quest.com/Quest_Central_for_SQL_Server/sql_tuning/sql--tuning.asp

    I looked at the product demo.  Very interesting, in terms of coming up with and testing alternative implementations.  But it seems you have to buy the whole package not just the tuning part. Anyone have experiece with this product?  What does it cost?

    Francis

  • Never tried it, i'm someone from quest will be on to sing it's virtues though

    The best tool in the world for this sort of thing lives in your head, it may be all grey and squidgy but learning this sort of thing will help you no end in the world of SQL.

    Sorry if that's not what you want to hear,

    Mike

  • I for one tried once when consulting for a client that owned it before Quest bought it and with fairly small to medium queries it performed "acceptably". The issue is that you still need to know what are you doing! and on the other end if you know what you are doing you will be definitely the best and most flexible approach

    Don't get me wrong if you need to go through a TON of queries is a tool may want to keep at hand.

    I can't remember how much those guys paid for it but it was not cheap

    Cheers!

     


    * Noel

  • Quest makes tools for optimizng SQL and Indexes. I've had excellent use of the SQL tuning tool in Oracle. If it works as well in MS SQL it would be well worth the price.

    The SQL must return correct results before optimization, it won't fix junk. Poor index structure will also prevent the tool from returning any results.

    J.R.

  • Thank you all. I will take a look at Quest's software.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply