SQL Server performance

My server is Win2k3sp1 with SQL2Ksp3 on it. It is 4x700mhz P3 Xeon and 2GB RAM, with a fairly powerful SAN attached to it as the S: drive, where all the MDF and LDF files are stored.

I run about 75 databases on this server, each averaging about 1.2 gigs, for a total live database load of about 100GB. Each of these databases are accessed off and on all day by about 250 users in an ASP environment.

I just attached another 3 databases to this server and it has affected performance for all users. My job is to somehow "make it work" so that the server can continue to provide SQL services.

A new server is not an option at this point. Maybe in 6-8 months, but not now.

I have another SAN location, the R: drive. Many of my databases are heavily indexed, and have separate mdf and ndf files where the indexes are on a separate file group.

I feasibly only have 2 upgrade paths I can see: Add RAM, or move some filegroups to a different location to optimize IO operation.

With 100 gigs of live data, is adding another gig of RAM like throwing a bag of feathers at a charging rhino?

I am averaging about 4000 index scans per second. The Processor queue length never gets any higher than 5, which is very acceptable for a 4-way situation, and usually stays at 0. The average disk queue length stays around 8 or so, which is fine for this IO system.

Any other perfmon statistics I should consider posting?

Does anyone here have any experience with the 36-bit addressing extensions that Xeon processors use called PAE, the advanced memory management in SQL called AWE, and making SQL run with 8GB of RAM or more?

Anyone? ... Anyone? ... Beuller? ... Beuller?

Just to let you know someone is reading your posts, I'll tell you I have absolutely no clue

Well, I've decided to go ahead and order RAM for my server. I am going from 2GB to 8GB, enabling PAE in the boot.ini file, taking sql to sp4, installing the patch from MS Technote 899761, and then enabling AWE and telling SQL to use 6GB with sp_configure.

We'll see what bumps I hit, and I'll post them. Should be about a week or so before it all happens.

I hope it works well.

If you haven't run across it yet, a good sql server site is www.sqlteam.com. There are some very knowledgeable sql server admins that hang around there.

I had a disruption with my RAM supplier, but installed a total of 5GB RAM in my SQL Server. I added the /PAE switch to the boot.ini file, installed SQL sp4 and KB 899761, enabled AWE support and set max RAM to 3.5 GB. It is running much better now, and I am impressed enough that I am going to make certain I put as much RAM as possible in this box. It was completely painless, even in our clustered environment.