I have an older Dell boxen with 2000 SVR SP4 with SQL 2000 SP4

Our app is getting slower as it ages and the mainboard cannot take more than 1GIG of ram.

Without getting new hardware, Id like to use this time to tune the DB before we spend $$ on an upgrade.

When I let memory go dynamic, it eats into all our swap and crawls

If I strap it down to 512MB, it speeds up for a while then gets slower, but not as slow as above (IE no swap pressure)

I have used Tuning Wizard, Update Statistics, Profiler and Perfmon and Process Explorer traces for baselines.

Some Profiler traces show some SPs taking 45-90 seconds to return result sets (bad app)

Target Memory hovers at 500MB-550MB and Total starts at 100MB goes up to 500MB by the end of the day

But since the app is commercial, its hard to change the front end,

Although I have SA on the box to eval the backend SPs, Views and other.

Ive used this to create my own SPs for Crystal reports.

Box is a Dual PIII-800 with a Gig of ECC and 3 18GB SCSI-180s off an older Dell Megaraid RAID in a PCI slot.

Seems like a good learning platform for sub-molecular evaluation...(tuning class)

What is the edition of SQL Server used here?q

2000 STD

Sounds like your stuck between a rock and a hard place. Like trying to defrag a HD at 100% full. The good news is if you get a decently new mobo/cpu, you can get 2x2GB pc667 ecc  kingston or crucial memory for ~$120 from newegg. Pretty darn cheap.

 GL

yeah i know.

Dell mobo actually maxes at 4x512 PC133 ECC for 2 gigs total for $600 from Crucial

I think I need to watch profiler traces and indexes

wondering if adding more disks to raid5 willl help

these 80-pin 18GBs are only $50, I could put 12 in for $600

Joe Chang always has 10+ disks...

You don't have much option to increase the SQL memory as it is limited to 2Gb and in this case refer to http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx blog post to take down the troubled spots on performance hit.

those links are developer stuff

i dont have access to the source client, so i cant control SELECT * etc

 But I can take baselines.

What happens  when you host a multi-gigabyte DB on a 4GB STD box, switch to ENT?

Yes and No!

I wouldn't suggest to go for Enterprise unless there is no other option or you have performed all kinds of optimization measures against the database(s) that are suffering such an issues.

You need to baseline the system usage in order to see which/what query/process is taking most of the memory, it is a general practice that SQL will take up the 80% of available memory and in this case say around 1780mb due to the standard edition. If the SQL is still struggling with slow performance then you need to perform such an exercise as mentioned http://sqlserver-qa.net/blogs/perftune/archive/2007/12/19/2981.aspx  here.