Post reply

VERY SLOW PERFORMANCE OF SQL 2000 USING A VIRTUAL MACHINE

  • January 11, 2015 at 8:12 pm

    #100575

    Hello,

    I am a developer and not familiar with SQL Servers.

    I need help to analyze the performance of our SQL Server 2000.

    MDF size is 1.3TB already. It is a virtualized server running in windows server 2003 enterprise.

    Memory already increased to 65MB. We already installed latest SQL SP4 hot fix.

    Hope someone can help me.

    Thank you

    Analisa

  • January 11, 2015 at 9:10 pm

    #1769381

    I need help to analyze the performance of our SQL Server 2000.

    MDF size is 1.3TB already. It is a virtualized server running in windows server 2003 enterprise.

    Memory already increased to 65MB. We already installed latest SQL SP4 hot fix.

    Sad to say that information alone is not going to offer much help in providing an answer to fix your issues. SQL Server 2000 also is not a version that offers easy access to performance metrics as current versions will. I would likely advise that your best plan is to work on upgrading to a more current version of SQL Server.

    With that, understanding that business is business I would first look at a few basics:

    - Make sure you have backups of all user databases, and they are good backups (tested restores)

    - Make sure you are doing general maintenance on your databases (index fragmentation analysis, statistics staying current, etc.).

    - Pull the wait statistics to see what SQL Server is waiting on the most.

    - Verify configuratin of both the instance and databases, review best practices documents for some of the more common things to check (MAXDOP, etc.)

    - RedGate offers many free e-books[/url] on performance and other aspects of SQL Server that are worth reading. Although do note that these books are going to be on more current versions of SQL Server.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • January 11, 2015 at 9:20 pm

    #1769383

    You need to get someone on site to help you. Index and stats maintenance needs to be checked and maybe implemented. If you don't have anyone that's SQL "savvy", you probably don't have backups running correctly. Cruddy code needs to be flushed out and repaired. Indexing opportunities need to be flushed out and instantiated. Mismatched datatypes and non-SARGable code needs to be flushed out and repaired. Log file VLFs need to be checked.

    Also, increasing memory on SQL Server 2000 doesn't do much unless you have one of the more advanced operating systems. In most cases, the most you can squeak out is somewhere between 2 and 4GB.

    http://technet.microsoft.com/en-us/library/aa933149(v=sql.80).aspx

    If you have a 1.3TB database running on SQL Server 2000, I'm thinking it's time to bite the bullet, upgrade the server, and upgrade SQL Server.

    --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)

  • January 12, 2015 at 1:32 am

    #1769420

    Thanks for response.

    Currently details of queries through process, there are lots of RESOURCE_SEMAPHORE. Through my searching this can cause slow down of server, is this correct?

    Unfortunately as I checked what are the queries under these using dbcc inputbuffer

    it only show

    RPC EVENT 0 sp_executesql;1

    Is there other way I can dig further?

    Thank you.

  • January 12, 2015 at 3:43 am

    #1769430

    Jeff Moden (1/11/2015)

    If you have a 1.3TB database running on SQL Server 2000, I'm thinking it's time to bite the bullet, upgrade the server, and upgrade SQL Server.

    I'd second this. SQL 2000 is hard to diagnose, out of support and several of the DB consultants I know won't work on it (I won't)

    Resource Semaphore suggests memory pressure. Specifically it is (at least in SQL 2005 and up) a wait for workspace memory. I don't recall if it was logged for other things too in SQL 2000.

    SQL 2000 does not have a 64-bit edition, which means memory access above 2GB is solely for the data cache portion of the buffer pool, accessed via AWE. Everything else, including all the workspace memory grants, plan cache, lock memory, etc, are restricted to 2GB. That may very well cause the resource_semaphore and no amount of adding memory will help.

    p.s. I hope you meant 65 GB of memory, not 65MB of memory

    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

  • January 12, 2015 at 5:41 pm

    #1769681

    Yes, it was 65GB not 65MB.

    Thanks for this info.

Viewing 6 posts - 1 through 5 (of 5 total)

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