Post reply

Need advice to improve SQL Server 2000 performance

  • December 5, 2010 at 9:36 am

    #104012

    Dear all

    My server has 4 CPU quad core (4x4=16 cores), 8GB Ram, Windows 2003 R2 32bits

    I use SQL Server 2000 Standard. There is one instance with one big database in SQL Server.

    Recently it runs slowly. Sqlserv.exe always has memory near 1.8GB RAM (it seems a limit of memory), but CPU only some percent. Even I configure to SQL Server use all cpu and up to 8GB RAM.

    I want to use maximum CPUs and RAM, please advice me:

    - If I upgrade to SQL Server 2000 Enterprise, can Sqlserv.exe use more than 1.8GB Ram ?

    - Is it better if I use Windows 2003 64bit, SQL Server 2000 32bit ? If it requires SQL Server 2000 64bit also, is there any complex of upgrade from SQL 32bit to 64 bit ?

    - Is it better if I upgrade to SQL 2008? (But for some reasons now I don't want to upgrade to SQL 2005 or 2008 so much)

    Thanks

  • December 6, 2010 at 11:29 am

    #1259141

    Any advice please ? Thanks.

  • December 6, 2010 at 3:50 pm

    #1259272

    thang_ngo_2002 (12/5/2010)

    Recently it runs slowly. Sqlserv.exe always has memory near 1.8GB RAM (it seems a limit of memory), but CPU only some percent. Even I configure to SQL Server use all cpu and up to 8GB RAM.

    This is correct. SQL 2000 Standard can only use 2GB of memory. SQL 2000 Enterprise can use more than 2GB of memory using AWE.

    I would recommend upgrading to SQL 2005 or 2008 Standard running on 64 bit which can utilize much more memory.

  • December 6, 2010 at 6:48 pm

    #1259324

    This is correct. SQL 2000 Standard can only use 2GB of memory. SQL 2000 Enterprise can use more than 2GB of memory using AWE.

    Do you mean to use parameter /PAE and /3GB ?

    I'm worry about stabilization of Windows because at that case Windows has only 1GB for kernels.

  • December 6, 2010 at 8:09 pm

    #1259332

    That would only help if you SQL 2000 Enterprise. They won't help with standard edition.

  • December 6, 2010 at 8:43 pm

    #1259339

    thang_ngo_2002 (12/5/2010)

    Recently it runs slowly.

    If you can figure out what changed (it may just be the size of the data), then you'll know what to do. Credits to Navy-Beans says that one or more pieces of code have reached the "tipping" point that every machine has. Most of that type of code can be repaired to run much more expiditiously without the expense of the Enterprise Edition, migration to new hardware, or an SQL Server upgrade to a new edition.

    First, though, you need to find out what the slow moving code is using SQL Server Profiler. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 6, 2010 at 8:45 pm

    #1259340

    Yes, of course I will upgrade to Enterprise version

    But as my second question, I'm worry about stabilization of Windows because at that case Windows has only 1GB for kernels.

  • December 9, 2010 at 1:10 am

    #1260446

    Should I have another question.

    Is there version of SQL Server 2000 Enterprise 64-bit for server Hewlett-Packard DL360 G6. I try to search in Internet but only get version 64-bit for Itanium process.

  • December 9, 2010 at 9:42 am

    #1260640

    The 64 bit version of SQL 2000 is only available for the Itanium processors IA64 and not for x64 processors.

  • December 9, 2010 at 12:32 pm

    #1260735

    thang_ngo_2002 (12/6/2010)

    Yes, of course I will upgrade to Enterprise version

    But as my second question, I'm worry about stabilization of Windows because at that case Windows has only 1GB for kernels.

    Please read my previous post... I'm suggesting that you don't need to do such an expensive upgrade. If you insist on that, you should also upgrade to 2008.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 9, 2010 at 6:38 pm

    #1260858

    As I said, for some reasons now I don't want to upgrade to SQL 2005 or 2008 so much.

    So I think the temporary best way now is install SQL Server Enterprise 2000 x32 on Windows x64. Is it right?

  • December 9, 2010 at 7:45 pm

    #1260863

    thang_ngo_2002 (12/9/2010)

    As I said, for some reasons now I don't want to upgrade to SQL 2005 or 2008 so much.

    So I think the temporary best way now is install SQL Server Enterprise 2000 x32 on Windows x64. Is it right?

    If you are going to go through the hassle of performing an in-place upgrade, why not move forward and upgrade to SQL Server 2008? I haven't checked 2008 R2 - but I am sure you can set the compatibility level for your databases in that version also.

    You should realize that as of SQL Server 2005, the standard edition can access as much memory as is available to the OS.

    Instead of paying the very high cost of Enterprise Edition - you could pay a lot less for 2008 Standard Edition.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 11, 2010 at 7:21 am

    #1261278

    Is it possible to upgrade from SQL 2000 x32 to SQL 2008 x64 by detach data files from SQL 2000 x32 and attache them to SQL 2008 x64 ?

  • December 11, 2010 at 7:30 am

    #1261280

    Yes.

    I just did a detach from SQL 2000 attach on SQL 2008 R2 this morning, and the processor architecture makes no difference to the file structure. x86, x64 and IA 64 have the same file structure.

    Another vote for 2008 or 2008 R2. I doubt you can even buy SQL 2000 any longer, and it has been unsupported for a couple of years now.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2010 at 9:06 pm

    #1261503

    OK, thank you very much.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply