SQL Server Performance

SQL Server 2000 runs out of memory in time

Discussion in 'Performance Tuning for DBAs' started by ekadakal, Jun 12, 2006.

  1. ekadakal New Member

    We have SQL Server Standard Edition with Service Pack 3.
    We are facing with problem in which SQL Server runs out of memory in time.
    The OS is Windows 2003 Advance Server and has 2GB RAM.

    sqlserver process start with approximately 60-70 MB of memory, but in about 10 days it starts consuming more than 1.5GB of memory. We are currently starting the server every othe week.

    Any suggestions would be appreciated.

    Regards
    John
  2. satya Moderator

    Have you checked what kind of scheduled jobs are running or any other processess that are causing this memory hog. Also what kind of database maintenance tasks are scheduled in order to gain the performance to avoid the index fragmentation.

    Search thru other related topics in the forum where you will find the solution to your problem.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  3. ekadakal New Member

    Thank you very much for the response.
    The sqlserver process is the only one with high memory usage.

    Yes, we have scheduled nightly backups that dumps the SQL database into a flat file. It is about 500MB. I've found similar problems posted in the form, but I haven't come accross any solution yet.

  4. satya Moderator

    Articles & KBA for your reference:
    http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
    http://sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk - check thru to resolve the issues.
    http://support.microsoft.com/?id=271624
    http://support.microsoft.com/default.aspx?scid=kb;en-us;321363

    Also check whether there is any disk contention as and when data is read from is from disk, it saves the data in the cache, so check what it is running such as resource intensive queries and that uses as much as possible in cache.



    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  5. ekadakal New Member

    I've applied SQL Server 2000 Service Pack 4, however it appears that the memory consumption of SQL Server has gotten worse; it appears to increase exponentially. For example, right after the update, memory usage was 94MB and on the next day during the peak time it increased to 700MB.

    I am currently reviewing the articles suggested by Satya.




  6. joechang New Member

    do you that SQL Server, like most other RDBMS are essentially data caches,
    a data cache functions by keeping data in memory
    so if someone accessed data, and there are free buffers, its kept in memory

    if you didn't want SQL Server to use memory, you can always lower the amount of memory SQL Server can use

    so now, why did you buy the memory in the first place?
    is it your intent to hold that memory aside unused?
    doesn't it make sense to actually use the memory that you bought?

    its like hiring someone, then telling to sit and do nothing
  7. mmarovic Active Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Exactly, it sounds like normal sql server (and database server in general) behaviour, unless you are facing the paging problem because there is not enough memory for OS.<br /><br />Is it your production server? Is the server deducated db server? If so and there is no paging this is how database server is supposed to handle memory. If not then you can set upper memory limit for sql server.
  8. ekadakal New Member

    Thanks for the useful responses.

    I understand that data is cached in the momory in order to have high cache hit ratio.
    However at what point does SQL Server write the dirty buffers (modified buffers) to the disk so that it can release and re-use that portion of the memory when it needs to copy the data block from a datafile on disk into a buffer cache?

    In our case, we have 2GB of memory, but SQL Server eventually takes up all the memory, causing the OS run out of memory.

    Currently our SQL Server is configured for Dynamic memory usage. Are there any implications to changing SQL Server to have fix memory? This is a production database, so I need to be very careful.






  9. csuttman New Member

    You should probably set the amount of memory sql server can use... leaving enough for the OS to do it's thing. otherwise the OS will have to compete for memory when it needs it. SQL Server will use every bit that it can... indefinitely.

    You may want to look into setting the 3GB switch in the registry so you can add and use more memory. Until I discoved this setting, my sql server would only us 1.7GB of memory, even though I had 4GB in the server. With the switch set, it's using 2.7GB... which made a huge difference in performance.





    Louder+Harder+Faster >= Better
  10. ekadakal New Member

    We can change the SQL Server memory allocation from dynamic to fix size (like 1.GB).
    However I still would like to know more about SQL server memory allocation, especailly at what points SQL Servers writes the buffers modified to the disk. Does SQL Server memory management have anything similar to Oracle's checkpoint (a database event which synchronizes the modified data blocks in memory with the datafiles on disk)?

  11. joechang New Member

    sql server frequently writes dirty buffers to disk
    but this does not release memory

    only when other applications start using memory, and the system free memory get low, will sql release memory

    all this is in the product documentation
  12. mmarovic Active Member

    quote:Originally posted by ekadakal

    We can change the SQL Server memory allocation from dynamic to fix size (like 1.GB).
    However I still would like to know more about SQL server memory allocation, especailly at what points SQL Servers writes the buffers modified to the disk. Does SQL Server memory management have anything similar to Oracle's checkpoint (a database event which synchronizes the modified data blocks in memory with the datafiles on disk)?
    Yes, at checkpoint dirty buffers are written to the disk. Checkpoint interval is derived from desired recovery time. You can also force checkpoint executing chekpoint command.
  13. ekadakal New Member

    All:
    Thanks for all the responses.
    Please keep in mind that in our situation, “OS runs out of memory” that we cannot even ping the server anymore. I am sure this cannot be by design.

    For your information, this excessive memory allocation happens during when the application is in use during a peak time. The code is written in C# and uses DBUtility class.
  14. joechang New Member

    i think you are having a different problem than what you described

    sql server using physical memory is one thing, which i do not believe is causing any issues for you

    One big thing that almost every one does not realize is the the application out of memory messages has nothing to do with physical memory.

    since SQL 2000 does not support C#/clr, i assume you are talking about a standalone C# app that runs on your db server? and that it has a problem, not SQL Server itself?
  15. satya Moderator

    Probably a candidate for the virus attack or issue with network or hardware too in this case, may check and cut down those issues too.

    quote:Originally posted by ekadakal

    All:
    Thanks for all the responses.
    Please keep in mind that in our situation, “OS runs out of memory” that we cannot even ping the server anymore. I am sure this cannot be by design.

    For your information, this excessive memory allocation happens during when the application is in use during a peak time. The code is written in C# and uses DBUtility class.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  16. Jack Vamvas Member

    It sounds like a regular SQL server process . As it takes 10 days to build up, why don't you run a schedules command to Restart SQL Server. Do it at a quiet time
  17. Adriaan New Member

    Also, how much free diskspace for the database, log and index files, and what current sizes are the files, and what is the growth setting for each of them?

    Especially when the log file can grow to gigabytes, filling up the disk completely, you will see the complete server grind to a near-halt. If you're having problems starting Enterprise Manager, that's usually a good indication.
  18. ekadakal New Member

    Thanks a lot, but restarting SQL Server nighly is not an option. I am looking for a solution, not a workaround. Thanks again for the suggestions.
  19. Adriaan New Member

    Did you check the disk space issue?
  20. ekadakal New Member

    Yes I did check the disk space. I have 6.5 GB free space.


    quote:Originally posted by Adriaan

    Did you check the disk space issue?
  21. mmarovic Active Member

    Once, few years ago I had similar problem. We discovered that a simple stored procedure that was executing 1000s times a day was eating memory. We have never discovered why that happened, but we applied the workaround: we scheduled recompilation of that proc each hour. I almost forgot about that but recently someone reported in this forum the similar issue. It looks like the same workaround helped him. I don't know if it is your issue, take it for what it is wort.
  22. ryanr12076 New Member

    quote:Originally posted by csuttman

    You should probably set the amount of memory sql server can use... leaving enough for the OS to do it's thing. otherwise the OS will have to compete for memory when it needs it. SQL Server will use every bit that it can... indefinitely.

    You may want to look into setting the 3GB switch in the registry so you can add and use more memory. Until I discoved this setting, my sql server would only us 1.7GB of memory, even though I had 4GB in the server. With the switch set, it's using 2.7GB... which made a huge difference in performance.





    Louder+Harder+Faster >= Better

    How is it you changed the registry setting, where is it set? We also get stuck @ 1.7GB

    Specs:
    Windows 2000 SP4
    SQL 2000 SP4
    2GB memory
  23. ekadakal New Member

    Thanks for the reply. We are currently restarting the server every 10 days, but we prefer not to. This is why we are investigating this problem. You said "It sounds like a regular SQL server process" I am having a hard time believing that by design, sql server will run the OS out of memory.


    quote:Originally posted by Jack Vamvas

    It sounds like a regular SQL server process . As it takes 10 days to build up, why don't you run a schedules command to Restart SQL Server. Do it at a quiet time
  24. pyao88 New Member

    What was your SQL Server max. set to? With only 2GB you can probably do 1.5GB and reserve 500meg for OS. If the OS portion runs out of physical/virtual memory you are going to get that OS out of memory issue.

    I recommend that you another 2GB to make the system 4GB, then use /3GB parameter in boot.ini to allow SQL Server to use 3 GB of memory.
  25. ekadakal New Member

    Hi,
    Thanks for the response. I will take a look at the stored procedure you mentioned. Do you remember what "stored procedure" was executing 1000 times?

    Thanks


    quote:Originally posted by mmarovic

    Once, few years ago I had similar problem. We discovered that a simple stored procedure that was executing 1000s times a day was eating memory. We have never discovered why that happened, but we applied the workaround: we scheduled recompilation of that proc each hour. I almost forgot about that but recently someone reported in this forum the similar issue. It looks like the same workaround helped him. I don't know if it is your issue, take it for what it is wort.
  26. mmarovic Active Member

    That was a simple stored procedure with a few straight selects that joined couple of tables and executed in a few miliseconds. It was not system stored procedure if it was what you mean by your question.
  27. ekadakal New Member

    When the server became unreponsive to our remote queries, we managed to look at the eventlogs and saw the following error message:

    "receive an Event ID 2019 error message in the System log:
    Event ID 2019
    Event Type: Error
    Event Source: Srv
    Event Category: None
    Event ID: 2019
    Date: Date
    Time: Time
    User: N/A
    Computer: Computer
    Description:
    The server was unable to allocate from the system nonpaged pool because the pool was empty."

    It looks like that the system had run out of nonpaged pool memory. Investigated on microsoft and found out that McAfee VirusScan 80i (patch 6 and earlier) has a nonpaged memory leak. Poolmon showed that the 3rd highest pool is the NAI0 pool, which is a pool used by the Virusscan 8.0i. See.http://support.microsoft.com/kb/888928/en-us.

    We have applied patch 10 and monitoring the system.

    On the otherhand, I've been also running SQL profiler on the server and observed the following: Sometimes, the SELECT or UPDATE SQL have high values in the "reads" (the number of logical disk reads in 8KB pages) columns but no values are in the "writes" (the number of logical disk writes in 8KB pages). In some cases, the reads column has as many as 26000 reads, but there is persistently 0 in the "writes" column.

    Is it correct to assume that a zero value in the writes column indicate that there are no writes to the disk? Can someone explain or give more information about write/reads columns in SQL Profiler.

    Thanks






  28. joechang New Member

    1) these are logical ops, not physical
    2) an insert, update, delete can show 0 write.

    my suspicion is because in the case of writes, there were no writes to the data disks, the lazy writer comes along later and does the writes

    i suggest you ignore profiler reads and write, there is no meaningful assessment to be made from these columns
    just use cpu and duration averaged over many calls.

Share This Page