I/O bottleneck and RAM

[andshr]

Hi:
A Performance Monitor of our SQL Server 2000 PhysicalDisk % Disk Read Time is too high (>100) showing I/O problem (on reads, it seems). Would it help to double RAM from 2 Gb to 4Gb to lighten up reads from a disk? All memory counters, on the other hand, seem to show that buffer has enough space...

 

[druer]

In my experience I/O bound servers are likely caused by poor indexing, or poor layout. Table/Index scans cause queries to read massive amounts of data that don't need to be read. If you've analyzed each query thoroughly and indexing is always leading to Index Seek then the problem may be that your Data/Indexes/TempDb/Log are all on the same disk (array.) You can alleviate the I/O on one drive by shifting things to multiple disks/arrays. (You can use a product called Speed Coefficient to do a performance trace and analyzitation for you to find Index/Table Scans and the queries that lead to the highest amount of reads/writes to find your trouble spots. You can find the product at

http://www.quest.com/coefficient/

and in the past you've been able to download a free trial version of the tool. I don't spend money on many products, but this tool is well worth the money as it automates the biggest part of my job "analyzing the system to find the areas (queries/stored procedures) that need attention.

I'd also try and use the Bytes Read/Written counters as opposed to the percentage of time as it could be that the disk is always moving but only a small amount of data is being read/written most of the time.

 

[druer]

I've never seen a situation like yours seems to be in. Your other post indicates that you are CPU bound, and in this one you seem to be I/O bound. If the system is sitting around waiting for I/O the CPU shouldn't be 100% busy. Likewise, if the machine is bound by the CPU the I/O shouldn't be so busy. Odd scenario, at least from my background and perspective. Again I would check the actual amount of I/O bytes being done in the performance monitor as opposed to the % to see if the volume is really that high.