SQL Server Performance

insufficient memory

Discussion in 'General DBA Questions' started by thiruvengadam_seenu, Aug 4, 2003.

  1. thiruvengadam_seenu New Member

    This is an intresting story.
    I have windows2000 and sql7 standard edition.
    To my knowledge,windows2000 and sql7 standard edition support only 2 gb of ram.
    Recently i took a dba assignment from a big insurance company,where in we have sql7 standard edition is running at windows2000 at 4gb of ram,as per the recomendation of a famous IT company,who has supplied the application.Now once in 2 days i am getting insufficient memory problem as a result my database goes into suspect mode,in the night after the scheduled jobs like reindexing,check db,backup and fragementation check in the night(scheduled job)
    My question is

    1 what are the chancess that the server goes in to insufficietn memory
    2 can i prevent this by setting somthing in sql server
    3 is the server is using 4gb of ram or it usses only 2gb

    can anybody tell me how to proceed??????????


    hanks and regards,
    thiruvengadam.

    s.thiruvengadam
  2. rushmada New Member

    It will use only 2gb because of limitation and

    In the server settings are u using dynamically configure sql memory or
    use a fixed size memory if it is fixed change to dynamically configure
    option.

    Having 4gb ram u can go for enterprise edition so that it solves ur problem.



    Rushendra
  3. gaurav_bindlish New Member

    Are you running more than one job simultaneously on the system?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. thiruvengadam_seenu New Member

    Thanks a lot Rushendra,still i want to know why this error,is it because of all the scheduled jobs like checkdb,fragemtnation check,reindexing and finally backing up database.

    Thanks and regards
    thiruvengadam_seenu

    s.thiruvengadam
  5. thiruvengadam_seenu New Member

    To the question of Gaurav,no not imultaneously,but one after the other.I think there is a memory leak,but how to find out?????????/

    s.thiruvengadam
  6. rushmada New Member

    What about the service pack level are u running with sp4 if not apply and
    see.

    Rushendra
  7. gaurav_bindlish New Member

    Did you monitor the memory utilization on the box using performance monitor. Genreally, there are very few known cases of memory leak in SQL Server Applications. Are you using and custom based application (using SQL-DMO etc.) for these activities?

    The suggestion of using the latest service pack by rushendra is recommended.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. satya Moderator

    No. of factors thay may cause insufficent resources :

    Insufficient memory allocated or available to SQL Server will degrade performance. Data must be read from the disk continually rather than residing in the data cache. NT and Windows 2000 perform excessive paging by swapping data to and from the disk as the pages are needed.

    A slow disk I/O (disk reads and writes) will cause transaction throughput to degrade.

    An improperly configured number of users can cause the system to run slowly or restrict the amount of memory otherwise available to SQL Server.

    A process may be forcing another process to wait, thereby slowing down or stopping the blocking process.

    To monitor for a low-memory condition, start with the defined object counters using SYSMON Memory: Available Bytes & Pages/sec.

    To monitor the amount of memory being used by SQL Server, examine the following counters:
    Process: Working Set
    SQL Server: Buffer Manager: Buffer Cache Hit Ratio
    SQL Server: Buffer Manager: Total Pages
    SQL Server: Memory Manager: Total Server Memory (KB)

    Refer thru other tips listed on this website which will give you required information on using PERFMON/SYSMON. And also as stated take advantage of Enterprise Edition if you can afford.

    HTH



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page