Increase performance on SQL Server

Posted on 2008-11-07
Last Modified: 2012-05-05
I'm having performance issues on my SQL server.  I'm running SQL 2005 Standard on an HP ProLiant DL380 G5, dual quad-cores, 4 GB RAM, 8 physical disks (set up as 3 partitions - C: mirrored, D: RAID5, and E: RAID5), Windows Server 2003 Standard edition.

We are running Orion Network Performance Monitor and the database resides on my SQL server (Orion server is a separate physical server).  Orion is having major performance issues and all is pointing to the SQL server at this point.  The disk queue lengths on the SQL box are averaging 22 right now and maxing at like 160.  All of the SQL data is located on the D: partition.  

What can I do to increase the performance of this box?  According to SolarWinds, we should not be seeing these issues with the hardware we are running.  Our Orion install is only polling 800 nodes...with the hardware we have, SolarWinds says that we should be able to poll up to 8,000 nodes.

Any help at all would be greatly appreciated!

Question by:npc_caseym
    LVL 3

    Expert Comment

    First the disk configuration. Normally for SQL, you install the database files on a RAID5 or RAID10 and the log files on a RAID1. You say that all your SQL data is on the D drive. You should make a RAID1 drive and move all your log files of your databases to that RAID1 drive. Logs are sequential, data read/writes are random. If both are on the same disk, that will lower the performance. I don't know whats on the E drive, but if possible, remove the E drive RAID5, take 2 disks in a RAID1, the 3rd disk, add it to the RAID5 of the D drive (with ACU you can do this life without loss of data (of course, murphy...) and move the log files to the E drive.
    Second, are you running a rebuild of indexes and update of statistics on a regular basis? Because if you don't, this can be a HUGE performance issue. I have databases that need a daily (during night) rebuild/update, because if I don't, some queries that normally take 20min, can end up taking several hours.
    LVL 21

    Expert Comment

    You mean you've got 3 raid sets each with one partition I assume?

    The usual culprits to check would be: 1) Make sure something else isn't causing the activity; 2) Make sure your statistics are up to date; 3) Make sure the raid write cache is enabled (assuming on a ups); and 4) Check that the raid sets all show optimal.

    Author Comment

    The E: drive is currently set up for just the Log files.  Sorry, the data files are on the D: drive, log files on the E: drive.  
    LVL 13

    Expert Comment

    How much memory is in your RAID Controller? You may find 192MB is better. Also; if performance is an ongoing issue; Look at a seperate RAID controller for your Log Drive.

    Also - How is your memory configured? Do you have the /3GB Switch in the boot ini?

    LVL 3

    Accepted Solution

    The RAID controller isn't a problem. The server is a DL380G5 so it's more than enough to do the job. A seprate RAID controller is not necessary.
    The /3GB in the boot.ini won't make a big difference, because the problem is a bottleneck on the disks (unless there is a lot of memory swapping, but in that case the supplier Solarwinds would have suggested to add more memory).
    Even with the logfiles already on the E drive, you still can change the E drive to RAID1, because this gives better performance (no parity to be calculated anymore and logfiles are sequential). If you want to change this, stop the sql server service, copy/move all files from the E drive to a tempfolder on the D drive. Destroy the RAID5 with the E drive, create a new RAID1 (with 2 disks), create a disk E, move all data from tempfolder to E, start sql server service. You can then add the remaining disk to the RAID5 of the D drive.
    LVL 3

    Assisted Solution

    And can you have a look at the performance monitor to the Avg disk queue length READ and WRITE and this for the different drives C, D, E. This way, you can discover which drive has the bottleneck and for what operation.

    Author Closing Comment

    Thanks.  This helped me out more than anything.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    759 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

    7 Experts available now in Live!

    Get 1:1 Help Now