1

I am having some disk access issues on a web application.

The main load is many concurrent inserts/updates/selects on large tables.

It is currently a single 2ru rackmount running SQL Server 2000 and IIS, running a raid5 with 4x15k SAS for the DB; and 6GB of ram/8 physical cores. CPU and memory usages seem fine.

Things i'm considering....

  • move from our current Raid to a many disk SAN
  • Ram Drive for tempdb files
  • Cluster the server
  • Upgrade SQL-Server (Will happen eventually regardless; but handy new keywords should help much at the very least)
  • ?

Anything I should be looking at/considering?

Thanks, Chris

1
  • Thanks for the answers; I'd like to upvote/accept multiple as many gave me good things to look at. I think I'm going to wait until we move to newer sql server (Should be fairly soon) and do a number of the improvements mentioned below.
    – wom
    Oct 19, 2010 at 20:59

5 Answers 5

2

Couple of things. First off, clustering the server won't give you any performance benefit, only increase in availability.

Probably the easiest thing you can do to relieve disk I/O is to increase the memory on your server. As more of your data pages are stored in RAM, the less physical I/O that needs to happen for queries to run.

Depending on your workload, if you can afford to move to a SAN, then you will probably want to look at splitting up your TempDB, logs, and data files onto seperate physical disks (you may want to look at this MS article around storage top 10 best practices for SQL Server: http://technet.microsoft.com/en-us/library/cc966534.aspx). As Hutch stated, move off of RAID 5, especially for logs and TempDB, has these have high amounts of writes (RAID 5 has a write penalty for the parity).

As Chopper3 says, FusionIO drives are some of the fastest drives around currently. If you have the budget for them, that is definitely one area to explore (especially for your TempDBs).

HTH, Dan

5
  • Since this is SQL Server 2000, is this Enterprise or Standard edition? Also is this 32 bit?
    – SQL3D
    Oct 18, 2010 at 18:07
  • 64bit Enterprise I believe.
    – wom
    Oct 18, 2010 at 18:46
  • Currently; we sit ~20% memory usage at any given moment; is there a benefit from growing this greaty? The barometer I am using to determine if I am disk bound is the average QUEUE length; which spikes above 100 when some of my high load operations kick off...
    – wom
    Oct 18, 2010 at 18:48
  • 1
    awesome. You might want to check a couple of Perfmon counters. "Physical Disk" >> "Avg. Disk sec/Read" should be less than 8ms. Also "SQLServer:Buffer Manager" >> "Page Life Expectancy" should be over 300. If either of these are off, then adding memory to your sever will be you quickest, easiest, and probably quickest way to relieve disk pressure.
    – SQL3D
    Oct 18, 2010 at 18:53
  • if you are at 20% memory usage, then you might want to check out your SQL Server properties and make sure that they are configured properly to use as much memory as possible (without affecting IIS, the OS, etc).
    – SQL3D
    Oct 18, 2010 at 18:55
5

Move your data and logs to a FusionIO drive, very very expensive but as far as I'm aware you can't get faster persistent storage right now.

Oh and you should move to 2008R2 64-bit too of course, plus load up on memory.

3

The immediate thing that leaps to mind is to look into converting that RAID5 to a RAID10 or a pair of RAID1's.

2

This is a huge topic, but I might be able to offer some tips to help you get started. In general I think it is safe you say that you will need to gather more precise information before you can decide how to best attack your problem. In the end it will probably come down to a memory issue or a disk issue.

(I'll ignore the possibility of looking at the queries that are running slowly and attempting to find out if there are ways to improve them. This is a huge topic, but using the query analyzer can help you to determine if you are missing an index or of a query should be completely rewritten.)

I would start out by using the Performance Administrative Tool to look at a few key metrics. The SQL Server performance counters are a good place to look. Disk queue stats can also be very telling. Are applications (i.e. SQL Server) waiting on disk I/O to perform operations? If so, then you know that you will need to upgrade the disks (or fix the queries, possibly). @Hutch is correct that RAID 10 will offer better performance than RAID 5 for a database server, but really that is only going to come into play if you're waiting on writes. 4 15k RPM drives in a RAID 5 should be able to handle quite a reasonable load with a decent RAID card (of course what's reasonable and/or decent is relative).

I would also check the memory. The server is running SQL Server 2000 so I will assume it's a 32-bit server OS. Is SQL server actually able to use all that memory? On a 32-bit system you'll need to use PAE to use all 6 GB, and even then I believe the SQL server process itself will only be able to grow to 4 GB. You can use a program like the sysinternals process explorer to look at how much memory is actually being used. You can even view how much memory SQL Server "wants" -- check out the SQL Server: Memory Manager counters (if they are available on 2000, can't remember off the top of my head).

You also mention that the server is running IIS. If you're running MS SQL Server on a server that's also running other heavily loaded services, you may want to look into manually configuring how much memory should be used. By default SQL Server assumes its on a dedicated server and tries to dynamically allocate as much memory as possible; depending on the needs of your IIS processes this may not be a good thing. On servers running multiple applications I usually like to configure SQL Server to use exactly a certain amount of RAM by setting the minimum and maximum to the same value (e.g. 4GB, 10GB, whatever).

Finally, you might consider upgrading SQL Server. SQL Server 2005 introduced some big speed enhancements in certain areas that might benefit you depending on your setup. Not to mention the fact that you can obtain a 64-bit version of SQL Server 2005/2008 and run it on a 64-bit OS to give it even more memory, and memory is by far one of the most important things for a SQL server.

0

This topic needs a huge "It Depends" for the answer. While the answers posted so far are good and correct for given conditions, there is so much more.

Yes, moving off of RAID-5 to RAID-10 is a huge check-mark in the "Do this now" column, but what about the pieces we have not been told?

Thinking about this, I come up with the following items: Disk layout:
What files are on what drives? Where is the TempDb and how many files are associated with TempDb Are the DB's and T-Logs on different drives?

Query management and optimization:
Have the heaviest queries been identified and optimized? How? Are the Updates causing row relocations or are they able to "Update in-place"? Row relocations during an update cause all sorts of performance issues. Index management: Are the needed indexes in place and are they correctly defined in order to be utilized by the queries which need them? Index rebuilds: Indexes become fragmented over time and need to be reorganized or rebuilt. Not doing this will affect performance.

Environment: Drive fragmentation can lead to file fragmentation. Even when the drive is kept clean, a large number of extents for any DB or T-Log file has a negative effect. Upgrading from SQL 2000 to SQL 2005 or (better yet) 2008 or 2008 R2 is a great suggestion. Running on Windows Server 2008 x64 or Windows Server 2008 R2 x64 complements the 6GB of memory in natively. SQL Server 2008 and 2008 R2 have built-in tools for helping to identify problem queries. In a stressed environment with a stressed DBA, this is worth the upgrade. Drive Controller Cache Memory: Is it sufficient? Is the cache shared between multiple logical drives?

Resource competition (pressure):
Besides IIS, what other processes on the SQL Server system are competing for the system's resources?

Adding a SAN is not a bad idea, but you need to understand the environment. Knowing if the cause of your performance speed-bumps is due to table-scans, improper foreign-key management, row-relocation during update, some other issue, or "all of the above" is very important. Without knowing the underlying cause(s) means the addition of a SAN will only be a temporary boon. Also, knowing your environment is mandatory for the proper sizing of a SAN -- It is not only about the availabe dise-space.

I do not know your environment and could not give a reasonable answer as to what you should do; also my above list is really the tip of a topic to which many thoughtful books have been dedicated.

You must log in to answer this question.

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