How to make IIS / MSSQL handle a larger load

Not sure where to go from here...

[dataguy]

I have a large, popular, custom-coded web site running on Windows Enterprise Server 2003 x64, MS-SQL server 2005, & Classic ASP. The hardware is 2 dual-core xeon's, 20 gigs of RAM, 15k SAS drives in a raid.

The problem is that it continually hits the maximum amount of traffic this server can handle. When traffic peaks, the site gets very slow.

I've been working on optimizing the database queries, but every time I do, the traffic level increases until the site starts to buckle under the load again.

I've been thinking about designing some sort of static-file caching system to store the main content of the pages as flat files and displaying them to most of the users, then updating them after they are a day or so old.

The problem is, of course, not everything is static and I'll still have to call the database for a few bits of info every time a page is viewed, regardless.

Then there's also the fact that the cached pages will consume disk writers and memory, so I'm not sure how much further ahead I'll be with this system.

I'm not ready to change the underlying programming language (I doubt that .Net could handle the traffic.) Hardware is cheap, but what I've got is farely beefy already.

Can anyone give me some direction?

[LifeinAsia]

It sounds like you need to do some analysis as to where the bottlenecks are.

First, determine if the main slowdown is on the ASP side or the database side. That will help you determine where to initially focus. If you're getting that much traffic, you may want to look into a new server and separate the web code onto its own box. (Or even boxes- one of my clients has over a dozen mirrored web servers with 1 database server.)

Are your tables properly indexed? (That's the first thing I'd look at.) How much of the RAM is actually being used at the peak times? Which queries are taking the longest to run? Are you grabbing more data than you actually need? (For example, doing a "SELECT * FROM TableName" that has 100K rows, but only displaying 20.)

[marcel]

I also agree with LifeinAsia, some good points there.

You can also look at the following:

- Data Caching best practises [webgecko.com]

- Do you use HTTP Compression? if so, de-activating this should decrease processor load (although your bandwidth use will increase)

[dataguy]

Thanks LIA. I've been monitoring long-running queries and optimizing them as best I can. At peak times, Task Manager shows 11.2 gb out of 20 are being used, so there seems to be plenty of RAM. I recently eliminated the one place where I was calling too much data (for pagination reasons) which allowed a few thousands more pageviews per hour, before it conked out again.

I don't know how to tell if the "ASP side of things" is a bottleneck. The site worker process usually peaks out at about 300,000 kb in size. I wish I could us multiple WP's but I need to maintain session variables for logged-in users.

[LifeinAsia]

Monitor how the server is acting (disk reads/writes, etc.).

How many disk drives, are they partitioned, and what level of RAID?

Does your site do a lot of DB writes (INSERT & UPDATE statements), or is it mostly just reads (SELECT statements)?

[dataguy]

I've finished my caching system, and it works better than I had even hoped it would. Page load time on a cached page averages about 1/50th the previous average, and the database is under such a lighter load that even pages which aren't cached load in about 1/3rd the time as before.

Thanks for your help. Hopefully I won't need any more hardware for awhile.

[LifeinAsia]

I won't need any more hardware for awhile.

Sorry to hear you won't get to buy any new toys for a while. :)

[marcel]

Sorry to hear you won't get to buy any new toys for a while

LOL, yeah half the fun of having a dedicated server is getting a new one ;)

Anyway, glad it works so much better now, hopefully your visitors also notice the difference.