• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

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!

2 Solutions
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.
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_caseymAuthor Commented:
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.  
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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?

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.
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.
npc_caseymAuthor Commented:
Thanks.  This helped me out more than anything.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now