2

I've been monitoring our SQL server for a while, and have noticed that I/O hits 100% every so often using Task Manager and Perfmon.

I have normally been able to correlate this spike with SUSPENDED processes in SQL Server Management when I execute "exec sp_who2".

The RAID controller is controlled by LSI MegaRAID Storage Manager. We have the following setup:

  1. System Drive (Windows) on RAID 1 with two 280GB drives
  2. SQL is on a RAID 10 (2 mirroed drives of 280GB in two different spans)

The server is a 64bit machine with over 50GB of RAM. SQL 2005 64bit is running on Windows 2003 64bit. Unfortunately, the application runs on top of jBoss, which is currently a 32bit version (but we're pushing the software provider to get us onto a 64bit version of jBoss).

This is a database that is hammered during the day, but is pretty inactive at night. The DB size is currently about 13GB, and is used by approximately 200 (and growing) users a day.

I have a couple of ideas I'm toying around with:

  1. Checking for Indexes & reindexing some tables
  2. Adding an additional RAID 1 (with 2 new, smaller, HDs) and moving the SQL's Log Data File (LDF) onto the new RAID.

For #2, my question is this: Would we really be increasing disk performance (IO) by moving data off of the RAID 10 onto a RAID 1? RAID 10 obviously has better performance than RAID 1. Furthermore, SQL must write to the transaction logs before writing to the database.

But on the flip side, we'll be reducing both the size of the disks as well as the amount of data written to the RAID 10, which is where all of the "meat" is - thereby increasing that RAID's performance for read requests.

Is there any way to find out what our current limiting factor is? (The drives vs. the RAID Controller)? If the limiting factor is the drives, then maybe adding the additional RAID 1 makes sense. But if the limiting factor is the Controller itself, then I think we're approaching this thing wrong.

Finally, are we just wasting our time? Should we instead be focusing our efforts towards #1 (reindexing tables, reducing network latency where possible, etc...)?

3 Answers 3

1

I think that there's always a benefit to separating the database files and the log files onto separate RAID arrays. Database file I/O is always random while log file I/O is always sequential. Mixing those I/O types on the same RAID array will always induce a performance penalty (although it may not be apparent if there's very little I/O load on the array). I think your point # 2 is well advised, although as mrdenny stated, you probably have database problems (indexes, etc.) if you're seeing disk I/O as high as you are with a database that size and 200 users.

I'm running a single SQL Server (2005 Standard) with on average 2000 connections to 125 databases without the performance problem you're seeing. We have a single RAID1 array for the databases and another RAID1 array for the log files.

In addition, don't overlook partition (volume) alignment as a possible cause of your performance problem.

Also, Have a look at these articles:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21949

http://technet.microsoft.com/en-us/library/cc966540.aspx

http://technet.microsoft.com/en-us/library/cc966534.aspx

http://technet.microsoft.com/en-us/library/cc966412.aspx#EEAA

2
  • Thursday night (2 nights ago), I finished moving the log files off of the RAID 10 they were sharing with the master data files, onto a new RAID 1. But the real performance kicker was upgrading jBoss running behind the application from 32bit to 64bit 5 days ago. Seems our software vendor missed that step when we initially sought their help in migrating servers 1.5 months ago. Yesterday, monitoring the server, I saw a difference in IO, while over the last 4 days of monitoring, we've seen substantial performance gains. Thanks for the links & the suggestions & the help!
    – David W
    Dec 17, 2011 at 13:57
  • Glad to help...
    – joeqwerty
    Dec 17, 2011 at 14:31
3

Odds are that you need to fix some indexing problems with your SQL Server. A 13 Gig database with 200 users shouldn't be pushing the disk very hard, unless the users are running some very complex queries and there isn't any RAM for the system.

I wouldn't bother with adding any hardware (except maybe more RAM) depending on if you are x32 or x64 and what version and edition of SQL and Windows you are using.

3
  • Thanks for the response. We have over 50GB of RAM, and are on a 64bit server running on Windows 2003 with SQL 2005. The commerical software we're using definitely has some complex queries, and is an older version of the software that the company currently provides.
    – David W
    Nov 29, 2011 at 18:04
  • MRdenny can't be more right - if the disk is being pushed that hard for a 13 gb database, something is wrong. Also note that a suspended processes doesn't necessarily mean that disk is the culprit (although it usually is) I've also seen them wait on poor queries for memry allocations to occur.
    – Jim B
    Nov 29, 2011 at 18:36
  • With 50 Gigs of RAM the entire database should be in memory unless something really bad is going on. There should be almost no disk access except for writing dirty pages to disk and the transaction log being written. Seems like a lot of diagnosis needs to be done to see what's going on with the server before any storage purchasing decisions are made. What does PLE look like? How much space is buffer pool using? How much memory is SQL Using? What other apps are on this server?
    – mrdenny
    Nov 30, 2011 at 2:31
0

For the log files, they should be on separate spindles, which would mean separate disk drives, raid arrays, and volumes. The reason for this is log writing is sequential, and should be performed as quickly as possible, in large allocation units, without having to contend with other database access (either writing to the database or querying).

Your volumes should be a 64k allocation unit size, and as joqwerty mentioned, you are more than likely also suffering from misaligned partitions, because Windows partitions are misaligned by default in Windows 2003. In some cases, the performance hit can be significant, as much as 30 to 40%. The following article describes how to re-create partitions that are properly aligned:

Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx

I'm not sure what you mean by "reindexing". If it is rebuilding or reorganizing, that is something that should be part of a daily maintenance plan anyway. And if you don't know how fragmented your indexes are, then you have some basic information collection to do before any action is taken. I would not be too eager to create new indexes, unless you have empirical data to support that. In particular, databases that are updated often (OLTP) should have as few indexes as possible, because every index slows update performance. I've seen people do more harm than good by "carpet bombing" a database with indexes.

Finally, you may want to verify that your RAID controller does have write caching enabled. I've seen too many people miss this. Sometimes write caching may be disabled due to the battery needs replacing, or because they weren't aware that they needed a battery to enable caching.

1
  • Great point on the battery. That was something that I installed onto the (existing) RAID controller just 1 week ago! I'll take a look at the Disk Partitioning, thanks for that suggestion (and thanks to joeqwerty too). Just for clarification, though, it seems to me that there would be a tradeoff for the RAID 10 -> RAID 1 performance. The more I read, the more I agree that this would probably be the better way to go, but if it were the same exact data written to the RAID 10 vs. the RAID 1, the RAID 10 would win (assuming all else equal). Thanks again.
    – David W
    Nov 29, 2011 at 19:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .