Performance tuning MS SQL 2005

Posted on 2008-11-17
Last Modified: 2012-05-05
I work for a small to mid-size business, and am a jack-of-all-trades: some application development, database design, technical support, etc.  I have been working a lot lately with SQL server 2005.  We have some databases that contain a large (for us) amount of info... anywhere between 5-20 million rows in the largest tables.  I'm interested in purchasing any type of software that will assist with performance tuning on these databases.  I doubt I manage updates/inserts/deletes the most effectively, but am not sure how to identify and analyze bottlenecks.  Outside of getting a consultant (which we're open to, but that's a different question), do you have any recommendations on software to help us with this?
Question by:christopher_perry
    LVL 25

    Accepted Solution

    The first tools are for free
    - sql*profiler: tells you which statement is heavy or many times called , heavy in the way of cpu,duration or read/writes
      + from a (representative) profiler trace you can call a optimasation analysis -> will give you indexes or statistics you can benifit with
    - query window -> execution plans can learn you where full table scans occur (previous step is this on the whole load)
    - server monitoring : cpu, memory, disk I/O
    LVL 7

    Assisted Solution

    I haven't used this product but Quest's "Workload Analysis for Oracle and SQL Server" looks good.  I ahve used other products from Quest and have been very pleased.

    I am also a big fan of SQL Profiler.  In my experience most DB problems are the result of poorly tuned queries, and/or bad indexing methodology.
    LVL 25

    Expert Comment

    addition to my "The first tools are for free".
    They are already on your server
    - SQL*profiler is a part of your SQL*Server so you don't even have to install it.
    - performance monitor for your server monitoring

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now