Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    14

    Unhappy Unanswered: SQLSERVR.exe keeps capturing memory

    Hi there,

    I have got SQL Server 7.0 SP4 installed on Win2k Server with 512 MB RAM. The problem I am having is that SQLSERVR.exe file keeps on capturing more and more memory. At it's peak it takes up as much as 250 MB memory and suddenly the server is having not more than 1 or 2 MB memory.
    Then all of a sudden the available memory bounces to 250 MB and again SQLSERVR.exe starts capturing memory. If I have to get RAM free I have to stop SQL server service and start again and then it starts from consuming merely 35 MB RAM and the cycle continues.

    Is there any solution to this problem? I have some more details about SQL configuration. Previously it was using dynamically allocated unlimited memory and to troubleshoot I configured SQL to use dynamic memory of min 0 MB and max 152 MB. Still it captures more than 200 MB.

    Please give me solution.
    Thank You.
    tapman

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Arrow Re: SQLSERVR.exe keeps capturing memory

    Hi there, I have got SQL Server 7.0 SP4 installed on Win2k Server with 512 MB RAM. The problem I am having is that SQLSERVR.exe file keeps on capturing more and more memory. At it's peak it takes up as much as 250 MB memory and suddenly the server is having not more than 1 or 2 MB memory. Then all of a sudden the available memory bounces to 250 MB and again SQLSERVR.exe starts capturing memory. If I have to get RAM free I have to stop SQL server service and start again and then it starts from consuming merely 35 MB RAM and the cycle continues.

    Q1 Is there any solution to this problem? I have some more details about SQL configuration. Previously it was using dynamically allocated unlimited memory and to troubleshoot I configured SQL to use dynamic memory of min 0 MB and max 152 MB. Still it captures more than 200 MB.

    Q2 Please give me solution. Thank You. tapman
    Question I
    The situation described is not 100% clear. Has this issue just appeared following sp 4 installation??

    A1 Maybe. For example, if sp 4 truly has introduced the cause of the described issue, one may simply revert to an sp 3 install until MS issues an appropriate hotfix, or another sp that does not introduce the issue.

    A2 If* the issue may be clearly and reproducibly demonstrated to be linked to sp 4, revert to sp 3 (and contact MS PSS so that MS may address the exact sp 4 issue further).

    Notes:

    It may well be necessary to more clearly identify the exact cause / source before a workaround or fix may be implemented. Consider running tests on a development server installed to sp 3, (or whatever configuration was running in production prior to applying sp 4).

    Consider initially monitoring i.e.(performance monitor) all running processes while running normal production type workloads (paged, non-paged pool, and virtual bytes objects would be some initial candidates to monitor); longer intervals will cause less overhead (performance drain). This should help to confirm (or eliminate) SQL Server (sqlservr.exe) as the likely candidate process responsible for the apparent "memory leak" issue. This may help to identify what may be going on from an OS process point of view when "Then all of a sudden the available memory bounces to 250 MB and again SQLSERVR.exe starts capturing memory", (also consider monitoring to understand what may be going on in terms of sql processes via profiler, when this occurs).

    *If the issue cannot be reproducibly demonstrated to be linked to sp 4, consider running some diagnostics to help identify the exact cause / source, then take appropriate action based on the results obtained. {Note in an unpatched state, MS Sql Server 7.0 did have some memory leaks (in Bulk Insert for example, if memory serves correctly these were addressed in sp 1 or sp2); it is possible that another leak of some kind is the issue in this case.}
    Last edited by DBA; 02-01-03 at 14:44.

  3. #3
    Join Date
    Dec 2002
    Posts
    14

    Unhappy Reply of Memory Capturing

    Thank you for your helpful reply.

    1. Answer to Question:
    The situation described is not 100% clear. Has this issue just appeared following sp 4 installation??

    No, the same problem was still there when SP 2 was installed. It has not been through installation of SP 3. After SP 2 directly SP 4 was installed.
    I am not sure if I revert to SP 2 will solve the problem.

    2. Regarding the performance monitoring, I have monitored the server many times and the only application causing trouble seems only SQLSERVR.EXE. All other software are normally running.

    3. About "Then all of a sudden the available memory bounces to 250 MB and again SQLSERVR.exe starts capturing memory" monitoring, that turnaround of memory happens randomly like 2 hrs, 3 hrs, 6 hrs, 12 or 20 hrs or sometimes even after 2-3 days. So it is not possible for me to know what exactly was running through Profiler to check it.

    4. There were some problems which came in installation of SP 4 and had terminated 3-4 times with errors about running .sql files(all different files everytime). At last it installed when I stopped the web sites-- because when it was running in single user mode to run .sql files and web client was utilizing that connection.
    But this is unlikely the reason as same problem was there with SP 2.

    Please help me overcome this problem.
    Thanks,
    tapman.

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Re: Reply of Memory Capturing

    RE:
    1. ...the same problem was still there when SP 2 was installed. It has not been through installation of SP 3. After SP 2 directly SP 4 was installed.
    I am not sure if I revert to SP 2 will solve the problem.

    2. ...I have monitored the server many times and the only application causing trouble seems only SQLSERVR.EXE.

    3. About "Then all of a sudden the available memory bounces to 250 MB and again SQLSERVR.exe starts capturing memory" monitoring, that turnaround of memory happens randomly like 2 hrs, 3 hrs, 6 hrs, 12 or 20 hrs or sometimes even after 2-3 days. So it is not possible for me to know what exactly was running through Profiler to check it.
    1., 2., 3. Then, for this system this is not atypical / unusual, or suddenly dramatically different behavior, as such, (and it is possibly normal behavior for typical system operations).

    Bear in mind, the following is a rather ordinary behavior pattern for many production Sql Servers (with no particular issues / problems):

    From service startup MS Sql Server normally will consume increasing quantities of RAM, (with each new connection and with many kinds of process activity), in the course of typical production activity. Generally, Sql Server will reuse, (and not release) RAM back to the OS (until and / or unless an excessive amount of Sql Server memory has not been used by any Sql Server process for some time.

    Question II
    Other than the Sql Server memory consumption / release pattern described, how would the problem be characterized? Is this a dedicated server?

    If there is doubt that the behavior is in fact other than ordinary; a logical strategy would be to:
    a Monitor Sql Server processes (Profiler) to identify process(es) involved and the source(es) of the processes involved
    b Once the process(es) involved are identified, examine / analyze their activity in some detail e.g.(the nature / length of their connections, transaction time lengths, isolation levels, statements run, etc.) to help spot any anomolous and / or unexpected resource intensive behavior.

  5. #5
    Join Date
    Dec 2002
    Posts
    14

    Unhappy Not a dedicated server

    Question II
    Other than the Sql Server memory consumption / release pattern described, how would the problem be characterized? Is this a dedicated server?

    Answer II
    The server is not a dedicated server. There are six web sites hosted on the server and among other software Site Server 3.0 is also installed for search services on web sites.
    This is the only concern I am having that since it is not a dedicated server, it should not allot all it's resources especially memory to SQL service since it can slow down other services and may result in disastrous consequences.
    Would it have been a dedicated server I wouldn't mind it. I want to find a proper balance in distribution of memory among all services according to their requirements and I think SQL is acquiring alot more memory than it requires.
    On an average at a time only 30-40 users are connected to SQL server and 100-120 to web sites. Still web sites do not consume even half of the memory that SQL takes up.

    Through the profiler it is not possible to findout which process is taking up more memory since there is no memory consumption data column available.

    Thank You,
    tapman

  6. #6
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: SQLSERVR.exe keeps capturing memory

    It could also be a memory leak in MSSQLServer service. I don't know how your "business rules" are encapsulated, but this behaviour could be generated for instance by a stored procedure wherein you declared cursors, you closed them but you didn't released them.

    If you connect to the server via ADO, you must know that an ADODB.Command object, even if it's destroyed within the aplication, won't free the memory locked by a cursor not deallocated in a stored proc.

    S.
    Steve

  7. #7
    Join Date
    Dec 2002
    Posts
    14

    No Cursors or Procedures used.

    1. The server has no user defined cursors and procedures. The built-in are kept untouched.

    2. I make use of ADODB to connect to server and kill the instance after completion of application.

    3. Since I have put max memory utilization by SQL Server to be 152 MB, it still exceeds that limit, goes to 180-200 MB. Why does this happen while dynamic memory of range 0-152 is being allocated?

    Thaks,
    tapman.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •