SQL Server Performance

SQL2000 Max memory used now what

Discussion in 'Performance Tuning for DBAs' started by bytehd, Apr 28, 2008.

  1. bytehd New Member

    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)
  2. satya Moderator

    What is the edition of SQL Server used here?q
  3. bytehd New Member

    2000 STD
  4. Ben2525 New Member

    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.
  5. bytehd New Member

    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...
  6. satya Moderator

  7. bytehd New Member

    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?
  8. satya Moderator

    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.

Share This Page