locked
which tools can help fine-tune Stored Procedures in SQL server 2005 RRS feed

  • Question

  • HI ALL,

    I WANT TO KNOW THE which tools can help fine-tune SQL Queries and Stored Procedures in SQL server 2005?
    PLEASE HELP ME IF ANY FREE TOOL IS THERE?

    THANKS,
    PRASAD.
    • Moved by Tom Phillips Monday, December 21, 2009 4:12 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Monday, December 21, 2009 2:38 PM

Answers

All replies

  • Prasad,

    Here are a few links to resources. All you really need is management studio and checking the query plan.

    MSDN Query Tuning Overview
    Query Tuning Hands On Lab

    Hope this helps,
    Sean
    Monday, December 21, 2009 3:15 PM
    • Marked as answer by SAAD's Saturday, December 26, 2009 5:54 AM
    Monday, December 21, 2009 3:53 PM
  • There is not a tool that will automatically examine code and give recomendations on how to rewrite it.  This mostly comes back to knowing what to look for and experience.  You can use the DTA (database tuning advisor) to give you advice about indexing, partitioning, and indexed views, but it wont be able to tell you that a correlated subquery is causing performance problems, or the number of temp tables is causing recompiles, or scalar function are evaluated for every row returned by the query etc....   This is where you have to bite the bullet and get into the code yourself to fix this stuff. 

    The links provided should get you started in identifiying bad code, which in my experience is biggest culprit of poorly performing code.  Also dont hestitate to use DTA.  DTA is a great tool and finding indexes and partitioning strategies.  You should lean on this whenever you feel stuck or not sure how to go about an indexing/partitioning strategy.  Another great perk of DTA is it gives us a What-if scenario, where DTA will tell you the estimated performance increase, in using a particular performance strategy.
    http://jahaines.blogspot.com/
    Monday, December 21, 2009 4:37 PM
  • Your last and best tool is to use this forum. The answers you will get here might never be found in any tuning tool.
    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, December 21, 2009 4:49 PM
  • Your last and best tool is to use this forum. The answers you will get here might never be found in any tuning tool.
    Abdallah El-Chal, PMP, ITIL, MCTS

    Actually for me, it's the best place to come first.  Not real certain if it's a good or bad thing, perhaps I'd retain a bit more of "the why" when resolving issues if I had to do it all alone.  But I still learn a great deal by doing, and being able to overcome problems/issues in the real-world from suggestions made here have been wildly benefitial to me. 
    jfc
    Monday, December 21, 2009 4:58 PM
  • Your last and best tool is to use this forum. The answers you will get here might never be found in any tuning tool.
    Abdallah El-Chal, PMP, ITIL, MCTS

    + 1 :D
    http://jahaines.blogspot.com/
    Monday, December 21, 2009 5:04 PM