asked on

Increase performance on SQL Server

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!

Thanks,
CaseyM

bertgeysels

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.

mastoo

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.

npc_caseym

ASKER

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.

St3veMax

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?

HTH

ASKER CERTIFIED SOLUTION

bertgeysels

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

bertgeysels

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

npc_caseym

ASKER

Thanks. This helped me out more than anything.