SQL Server Disk Queue Length 1

[pathaide]

We are trying to deal with complaints of slowness in our SQL server environment. We have the following

IBM x440
8 Way P4 1.6
6 GB RAM
12 - 18 GB HD configured to RAID 10
Windows 2000 Advanced Server
SQL 2000

CPU usage is low on the server and memory usage is also low (2GB used out of 6GB) However, our disk queue lengths commonly go above 150. Our database is only 9GB in size but we have about 4 tables that are commonly accessed with over 1.5 million records in each. There are about 150 users connected to the database at any given time.

Any help would be appreciated

Thanks

 

[JayKusch]

Here are a few tips off hand ...

Consider moving database files to faster disks. If you are not using hardware RAID, consider purchasing a RAID subsystem. If you are using RAID-5 for write-intensive files (such as Database Logs or heavily updated database files), consider moving to a faster RAID implementation (RAID-0 or RAID-10).

In some cases, you can speed up all disk I/O by reviewing the RAID options on your RAID controllers. One example is to enable disk-write caching, as long as your disk subsystem is protected by battery backups or UPS.



Thanks

J. Kusch

 

[pathaide]

the disks that we are using are 15K drives. We do have it running on a hardware RAID controller for RAID 10(ten). Our current RAID controller does not support disk write caching and we were considereing purchasing a new controller to replace that one but I'm wondering how much of a performance boost that would give. Are we better off adding more drives to the drive array and at the same time increasing the size of the drives to allow the drive heads to have to move less? Any more thoughts would be appreciated

Thanks

 

[itsp1965]

pathaide, your hardware seems more than adequate to support the environment which you describe. Have you taken a look at the database side of things. There could be issues with regards to the way the database is designed. Look at

http://www.sql-performance.com

which is a great site that describes methods of getting the best out of your SQL environment.

 

[pathaide]

I'll definitely check out that site. We actually already had someone come in to do just that. They re-indexed all the tables last year and we saw a huge performance increase. Since then however, our database has grown in size and the number of users has increased as well.

 

[pathaide]

the link that you just sent me doesn't work. Is there a type in the link?

Thanks

 

[itsp1965]

Sorry about that pathaide. Here is the correct URL

http://www.sql-server-performance.com/

 

[Corran007]

what sort of maintaince do you have running on the server and databases?

 

[pathaide]

I just found some information on defrag and re-index. We're going to run those this weekend hopefully and see if it makes any difference. Do you have any other suggestions?

 

[Corran007]

no just those. When you said he rebuild the indexs and it sped it up, i wondered if you are doing maintanince on it.