I recently came across an interesting article that went over using IO statistics to identify the causes of performance issues. After examining my statistics i came to the determination that i had a "bottleneck in the IO Subsystem".

Unfortunately I don't really know what this means. From what I've found on the web, throwing some more memory at it might help (i assume because it doesn't have to write to disk as much) but I'm curious if anyone knows of a good approach for determining how to proceed?

  • 1
    You need to describe the symptoms of your problem before anyone could concur that you have an I/O subsystem bottleneck.
    – Tim
    Feb 5 '11 at 0:19
  • 1
    For the sake of argument can we assume I am right? I'm not asking how to determine if there is an IO Subsystem Bottleneck. I'm asking how to fix one. Feb 5 '11 at 0:59
  • 1
    possible duplicate of Where to start fixing an IO Bottle neck in SQL Server? Feb 5 '11 at 23:54

The IO subsystem generally deals with storage to disk. This happens when the data has to be written or read. Generally speaking you'll want to monitor the disk queue depth. I don't exactly remember the recommendations but I think it's something like 1.5 * the number of physical disks involved.

There are a few potential solutions depending on what you find.

First, get enough RAM in the server so that it can basically keep the entire DB in memory and have plenty of room to execute queries against. The less amount of time you can keep it from paging to disk the better off you are. This includes having enough RAM to keep temp tables in RAM instead of having those paged out to disk.

Second, get faster disks. 15k SCSI drives appropriately raided. You might even investigate SSD if applicable; be aware of their limitations before you do though. Put the log files on a separate disk system and, potentially, partition out the tables so that they are on different disks as well. This one can get complicated and very expensive.

Third, tune your queries to make better use of your indexes which should also help with limiting down when the server has to go to disk to pull the full records.

Finally, don't sweat it unless it's an actual problem. If your app is moving fast then find a different area to "fix" If it's an actual issue then decide if it's time to scale your server up.

  • 1
    +1 but would add: Zero-ith : add indexes as needed to support the existing queries; make sure index rebuilds jobs are in place, and that your statistics are up-to-date. Make sure log files are on different physical disks than data; make sure the RAID levels are capable of handling the workload, i.e. no RAID 5.
    – SqlACID
    Feb 6 '11 at 0:04
  • @SqlACID: Agreed.
    – NotMe
    Feb 19 '11 at 21:47

Before you start upgrading your IO system you need to look at your indexing. If your indexing isn't correct this will show as a lot more IO load on the disks. Once you have fixed your indexing problems, if you still have IO problems then you will need to add more spindles or switch to flash drives.

  • 1
    +1 - Fix the schema before you start throwing hardware at the problem. There is a lot to be gained in profiling your slowest queries and discovering WHY they're slow. Feb 5 '11 at 2:17
  • The database, queries and indexes are all controlled by Sharepoint. If I attempt to modify them, I enter a world of pain. All of my previous experience with performance tuning has been just what you are talking about but in this situation my options (as far as I can tell) are limited to hardware. Feb 8 '11 at 18:12
  • SharePoint is separate from just about any other database as you can't touch the schema at all. In the case of SharePoint work with your SharePoint team to ensure that the SharePoint content databases aren't getting to large. You don't want any content databases over 100 Gigs. If your content databases are already small your only option is to throw more hardware at the problem. Start with more memory. If that doesn't help you'll want more/faster hard drives.
    – mrdenny
    Feb 8 '11 at 23:32

IO subsystem basically means disk/storage. If that really is your bottleneck, you'd improve performance by adding more and/or faster disks. Choosing the right raid level is also important, RAID 10 generally outperforms RAID 5 for databases, especially for write-heavy loads.

But start out by making sure your server isn't wasting disk bandwidth. Appropriate indexing is a must for db performance. More ram for caching could also lower IO load. Reorganizing your data for better locality might help, depending on access patterns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.