sql 2008 high disk i/o

we are currently running SQL 2008 SP1 on a Windows Server 2003 Web Edition Server (2GB O/S RAM limit)

the issue is that SQL seems to be using a high disk I/O causing DB connections to be very slow and continual timeouts.

in perfmon the Avg. Disk Queue Length is always at 100 which is not good.

the server has 2GB ram due to O/S limitation which we understand is not the best. the RAID is a RAID1 with data & transaction logs on the same drive.

we are replacing this server but need to in the mean time wondering if there is anything we can do? I have changed SQL to us max 1.2GB RAM but the disk activity still peaks out.
wyscomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidPresidentCommented:
Queue length of 100?  More RAM will buffer some read I/Os, and help.  

Unless whomever set up your storage purposely went out of their way to create the most bone-headed configuration possible, then you need to add more disk drives and create additional logical devices (assuming RAID1) and migrate data to them.  100 I/Os in the queue is about as obvious as you can hope for that you need to create more IOPs, meaning more disk drives.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>>  I have changed SQL to us max 1.2GB RAM but the disk activity still peaks out.

You have only 1.2GB of RAM for SQL Server which is very low for SQL Server to perform better since SQL server loves utilizing more Memory..

High Disk IO means that there is heavy fragmentation in your indexes and tables and just Rebuild indexes in your table in order to reduce Disk IO..

use ur_db_name
go
sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)’
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwkorCommented:
It could be a few different reasons - like it was mentioned, it could be hardware configuration (not enough RAM) and fragmentation. Although one of the other most common reasons for this problem is unoptimized queries. Try to profile Scans and see if you have extensive number of them. If this is the case, take a look at the queries which produces them and create necessary indexes
0
ProjectChampionCommented:
Find out what is causing the high load on disks. There are a few i/o related DMVs which you can use to find out what is going on on the disk, for instance sys.dm_io_pending_io_requests give you info about each pending i/o request in SQL Server or sys.dm_io_virtual_file_stats gives you the i/o stats for individual data and log files.
Use the "Activity Monitor" and look ath the most recent expensive queries. Find such queries and right click to see the excution plan, a missing index, a heavily table/fragmented index, or a bad join in a complex query can cause lots of unnecessary physical/logical i/orequests... When you find out whats causing the problem then you can go about resolving it: create the missing index if any, rebuild/reorganise the fragmented index(es),  ask the developers to review underperforming queries, reschedule heavy jobs to reduce the load on the server at the peaj hour....

0
wyscomAuthor Commented:
the RAM is the biggest limitor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.