[Webinar] Streamline your web hosting managementRegister Today

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

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.
2 Solutions
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.
Raja Jegan RSQL Server DBA & ArchitectCommented:
>>  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
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
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....

wyscomAuthor Commented:
the RAM is the biggest limitor

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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