Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    44

    Unanswered: SQL server 2000 performance problem

    All,

    I have a 4 way quad core SQL 2000 server running on W2003 server. 32 gigs of memory. AWE enabled. It is set to dynamically configure memory from a Min of 1024 to a Max of 32765.

    "Reserve Memory For SQL Server" box is UNCHECKED.

    100 to 150 user sessions on average.

    I regularly have performance problems yet the CPU total utilization number rarely goes about 25%.

    Any thoughts on what's causing the slowness despite 25% peak CPU utilization? What can I do about this?

    Isaac

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    poorly written sql, poorly designed databases, inefficent application code, blocking, excessive locking, the possibilities are many. index framentation. no indexing. who knows?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2003
    Posts
    44
    Good points - so let me clarify. I can attest to the fact that the DB is well designed, we have good indexes in place, and our users are unusually well educated on their coding(believe it or not).

    This is an analytical mart (not transactional).

    So I've been wondering about this from a configuration standpoint. EG: should I have AWE enabled, should I have different MIN/MAX memory settings, anything in sp_configure that I should look at?

    Any Windows 2003 server settings that I should have someone checK etc?

    Isaac

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I usually attack performance issues from the ground up. Code and indexes are usually cheaper than hardware.

    how do you know the cpu or the memory is the bottleneck. It does not sound liken the CPU is the issue. Have you looked at the performance monitor in the Control Panel? Disk Que? Pages/Sec etc... laddy da...
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    No blocking locks ? try nolock hint with (nolock).

  6. #6
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    check database settings like sp_dboption , autogrow , maximum file size , put sp_configure analyse the settings, RAID levels, Servuce pack and also statistics updated properly , DBCC job executed regularly like Integrity and Optimization job . Check the query with Index tuning advisor , Run SQL profiler and analyse the query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •