One of our main applications in our company has been getting slower and slower over time. People are even experiencing long delays in editing and saving records in the system.

So I ran some performance monitor counters and found one thing stood out. I think there might be a bottle neck with the HDDs. The CPU and memory resources are not under strain but the HDD with the DB looks to be struggling.

Below are some counters taken over a few hours during the day for the HDD hosting the DB and nothing else:

Counter AVG - Max
avg disk queue length 7.77 - 31.438
avg disk read queue length 7.698 - 31.386
avg disk write queue length 0.073 - 1.53
Disk read bytes/sec 41,783,373 - 63,903,516
Disk write bytes/sec 185,987.97 - 6,072,001
Disk Reads/sec 306.13 - 412.372
Disk Writes/sec 8.338 - 696.269

Buffer Cache hit ratio average is about 98%, rarely can drop to a lowest of 95% but not that often.

Those disk queues look high to me. Our system is running windows server 2008 with SQL server 2005 express. One HDD for the OS and application, one drive for the database log and another just for the database DB. The DB itself is only 1.3GB so it's tiny.

There is no raid setup on this sytem. My hunch is the Db needs to be put onto some kind of RAId setup to imrove the disk read times. Maybe raid 10?

Does anyone have any thoughts on this? I am not experienced with dealing with database performance issues so this is new to me. Any advice would be appreciated.

The queuing is all on the read side of things. This indicates to me that there is something in the code of the application or database that is generating a lot of table scans. It is possible that these can be relieved by adding an appropriate index, but that is part science, part black magic. Do you have access to any SQL Server tools? They would not be installed with an Express edition, I think.

Thanks for the advice. Yeah SQL express doesnt have any inbuilt tools that I know of like the SQL profiler or sql tuner wizard.
I have found a free SQL profiler tool that works with SQL express so I will gather data on what tables might need indexing and go from there.

Wow, those queue lengths are pretty high. What kind of drives are you using? SATA?

This is going to sound a bit old school, but besides adding some indexes to reduce the full table scans you're probably seeing, you may also want to check for fragmentation too. If you see a lot of fragmentation with your data or log files, shut down MSSQL, defrag your drives and then start it back up again.

It probably won't buy you as much as adding a few strategic indexes, but I've seen it work miracles on a few of my customers databases, giving them another year or more out of their hardware.

If fragmentation is really at fault, then defragmenting will actually increase Disk Reads and Disk Read Bytes/sec counter values. The problem is not in how much is read (or written). The problem is in "why". One solution would be to stop the application ().
Another, - optimize data access by indexing and possibly reviewing the SELECTs.