Post reply

Performance and Tuning

  • November 16, 2006 at 10:43 am

    #76611

    Can somebody recommend the best 3rd party tool or software for tuning SQL Server database? (Stored procedure and the rest)

  • November 16, 2006 at 1:24 pm

    #672441

    the built in tools work best in my opinion.

    Start>>Run>>Profiler

    connect to the database, and run a trace for a few hours.

    stop teh trace, and save to a trace file.

    open Enterprise manager, get the index tuning wizard and open the trace file you just saved.

    let it do it's thing to completion(takes time), and save it's recommendations to a file.

    examine the changes suggested yourself, so you see what they are going to do, and then run the changes.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 17, 2006 at 4:48 am

    #672547

    While you should generally run the tuning wizard as a matter of course as the first step with performance problems, you have to remember that it can only tell you the optimal configuration for the queries its given. What it can't do is tell you if the query itself is written optimally. For that, you need an experienced DBA

  • November 17, 2006 at 6:30 am

    #672568

    And I think we got a few of those around here .

  • November 17, 2006 at 12:51 pm

    #672739

    Lowell and Ninja have hit it right on the nose. The only caveat I would add is to NOT take the Tuning Advisor 'literally' and as an absolute expert. If you do so, you may develop 'indexitis', the symtoms of which a few are:

    • new indexes
    • new performance issues on modify operations (insert/update/delete)
    • shrinking nighttime maintenance window do to more indexes
    • larger and longer database backups

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • November 18, 2006 at 4:16 am

    #672795

    Also you might want to limit his ideas to a certain number of columns like 4 or 5.  Or you could get suggestions for 3-4 indexes with 10+ columns (PER TABLE) and now those inserts will start to suffer!

Viewing 6 posts - 1 through 5 (of 5 total)

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