Memory Problem

  • Hi every one,

     

    We have checked our aplication for queries and there is nothing wrong with the code.But still our response time is too long and server is responding very slow.

    When i see there is still 4 GB of Ram free not been used in the process.

    We have  8 GB of RAM + 4CPU +400 users

    Pls help me .

    Thanx

    from

    Killer

  • Do you have AWE switched on? What's sql's memory settings? Dynamic or fixed maximum?

    What's the CPU utilisation like? Disk IOs? Network card queue length?

    Locking? Index usage vs table scans?

    There's a number of reasons why the resonse could be poor without there been memory a bottleneck

    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
  •  

    If you are using sql server 2000 standard edition then

    memory max is 2 GB. only 2 GB of your 8 GB will be of use for sql server.

    Dynamic memory setting is recommended by MS unless you have known

    reason for setting otherwise.

    If you are using Enterprise edition then Configuration setting should help. 

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • Is your slow response something new?  Is it only slow on this one specific server?  From a memory perspecitve, it does not look like you have AWE enabled.  With that said, you have 8 GB of RAM and SQL Server only has access to 2 GB.  You need to enable AWE and add the /3GB and /PAE switchs to your boot.ini file.  To enable AWE:

    SP_CONFIGURE 'show advanced options', 1 

    RECONFIGURE                             

    GO

    SP_CONFIGURE 'awe enabled', 1

    RECONFIGURE

    GO

    I also recommend setting your 'max server memory' value to 7 GB.  By default, AWE will use up all available memory except for 128 MB, which it reserves for the OS.  Setting the 'max server memory' value allows you to allocate more RAM for the OS. 

    SP_CONFIGURE 'max server memory', 7168

    RECONFIGURE

    GO

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Hi,

     

    AWE option is enabled . Yes there are few locks on the tables but that is normal for our application.

    We have enterprise edition and we have setup dynamic memory allocation.The CPU utilisation is normal.

    from

    Killer

  • One thing to look into would be your disk IOs. As your tables grow for the same queries, you may have more paging activity, where at one time your queries were running in memory, now the temp results or sorts are too big and are going out to disk.

  • Yes ,We have number of tables in buffer (memory) but that is our need because these tables are used number of times most freq.

    But they have only few records as they are master tables.

    "The temp result is not running out of disk.

     

    from

    Killer

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

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