wyscom
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_reque
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....
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....
ASKER
the RAM is the biggest limitor
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.