SQL Server Performance

Server crashes with 'Insufficent Memory'

Discussion in 'Performance Tuning for DBAs' started by stanle, Dec 9, 2002.

  1. stanle New Member

    I am posting a part of the log that describes our error. The configuration is 8 processors, 8GB memory, AWE enabled. The server crashes (hangs) after this error is reported in the log. So far we have tried increasing the pagefile size. We have also checked for tables pinned into memory (no such). The technet articles mention a similar problem but it is only for a database with more than 100,000 tables, and we have 275.

    Has anyone experienced this problem, and does anyone have any ideas?

    Thank you in advance.

    Mobiltel EAD

    2002-12-08 14:46:24.40 server Error: 17803, Severity: 20, State: 14
    2002-12-08 14:46:24.40 server Insufficient memory available..
    2002-12-08 14:46:26.62 spid4 LazyWriter: warning, no free buffers found.
    2002-12-08 14:46:26.67 spid4 Buffer Distribution: Stolen=19263 Free=0 Procedures=124
    Inram=368556 Dirty=62 Kept=0
    I/O=0, Latched=219, Other=0
    2002-12-08 14:46:26.67 spid4 Buffer Counts: Commited=388224 Target=388224 Hashed=368837
    InternalReservation=541 ExternalReservation=7520 Min Free=512
    2002-12-08 14:46:26.67 spid4 Procedure Cache: TotalProcs=13 TotalPages=124 InUsePages=124
    2002-12-08 14:46:26.67 spid4 Dynamic Memory Manager: Stolen=11549 OS Reserved=1768
    OS Committed=1729
    OS In Use=1651
    Query Plan=217 Optimizer=7
    Utilities=1302 Connection=502
    2002-12-08 14:46:26.67 spid4 Global Memory Objects: Resource=2095 Locks=6224
    SQLCache=73 Replication=11
    LockBytes=2 ServerGlobal=46
    2002-12-08 14:46:26.67 spid4 Query Memory Manager: Grants=2 Waiting=0 Maximum=143560 Available=128216
    2002-12-08 14:46:46.62 spid4 LazyWriter: warning, no free buffers found.
    2002-12-08 14:46:46.67 spid4 Buffer Distribution: Stolen=19252 Free=0 Procedures=128
    Inram=368556 Dirty=61 Kept=0
    I/O=0, Latched=227, Other=0
    2002-12-08 14:46:46.67 spid4 Buffer Counts: Commited=388224 Target=388224 Hashed=368844
    InternalReservation=561 ExternalReservation=6424 Min Free=512
    2002-12-08 14:46:46.67 spid4 Procedure Cache: TotalProcs=14 TotalPages=128 InUsePages=128
    2002-12-08 14:46:46.67 spid4 Dynamic Memory Manager: Stolen=10431 OS Reserved=1768
    OS Committed=1729
    OS In Use=1651
    Query Plan=229 Optimizer=0
    Utilities=1302 Connection=509
  2. Chappy New Member

    Is this a dedicated SQL server ? What other processes if any, are running, and do they appear to consume much mem in task mananger? Is sql configured to have free reign over all accessible RAM? Also what version of the server are you running, and what SP level?

    Try using the DBCC MEMORYSTATUS command to see how memory is allocated within the various subsystems of sql server, perhaps this will give some ideas.

    Is this reproducible, and if so try tracing and a memory perfmon, to see if any sp's or jobs are eating away at the memory. Also it might be worth looking at the -g startup parameter mentioned in SQL7 SP4 readme ..http://support.microsoft.com/defaul...om/support/servicepacks/SQL/7.0/sp4ReadMe.asp

  3. stanle New Member

    10x for the help Chappy. Yes, it is a dedicated SQL server. It is also a distributor and other processes that take memory are the distrib jobs (26X6.5MB = appx. 150MB), so nothing special. The server is under a constant heavy load. The proc cashe is appx. 170MB (22,000 pages) and the data pages in buffer are 3.6GB. This is not unusual, as I have enabled the AWE and it means that the server holds in memory as many data as it could. The max server memory setting is 4GB at this stage. We used to run this server with only 4GB of phys RAM (no AWE and max server mem 2GB) for quite a long time and then this error occured. Now I have replaced the memory with 8GB and the error again occured after a few days (but the max memory is still 4GB - there is an argument here whether to increase it or not). No cursors are in memory, no pinned tables but just a very heavy load from stored procedures and applications. I'm sending the DBCC MEMORYSTATUS results. Another weird thing is that in DBCC SQLPERF(lrustats) the number of free buffers varies widely from 50000 to 5 in a short period of time.

    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 47707
    Free 1055
    Procedures 73366
    Inram 0
    Dirty 14217
    Kept 0
    I/O 0
    Latched 190
    Other 375465

    (9 row(s) affected)

    Buffer Counts Buffers
    ------------------------------ -----------
    Commited 512000
    Target 512000
    Hashed 389872
    InternalReservation 699
    ExternalReservation 28544
    Min Free 511

    (6 row(s) affected)

    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 20643
    TotalPages 73366
    InUsePages 35106

    (3 row(s) affected)

    Dynamic Memory Manager Buffers
    ------------------------------ -----------
    Stolen 100081
    OS Reserved 2560
    OS Committed -201
    OS In Use 2435
    General 17468
    QueryPlan 78617
    Optimizer 0
    Utilities 5306
    Connection 1125

    (9 row(s) affected)

    Global Memory Objects Buffers
    ------------------------------ -----------
    Resource 4627
    Locks 248
    XDES 40
    SQLCache 730
    Replication 12
    LockBytes 2
    ServerGlobal 49

    (7 row(s) affected)

    Query Memory Objects Value
    ------------------------------ -----------
    Grants 1
    Waiting 0
    Available (Buffers) 154040
    Maximum (Buffers) 203576

    (4 row(s) affected)

    Optimization Queue Value
    ------------------------------ -----------
    Optimizing 0
    Waiting 0
    Available 32
    Maximum 32

    (4 row(s) affected)
  4. bradmcgehee New Member

    What are your memory settings in the boot.ini files, and can you send the output of sp_configure for this server?

    Brad M. McGehee
  5. stanle New Member

    /PAE /3GB are in the boot.ini

    sp_configure returns:
    affinity mask-2147483648214748364700
    allow updates0100
    awe enabled0111
    c2 audit mode0100
    cost threshold for parallelism03276700
    cursor threshold-12147483647-1-1
    default full-text language0214748364710331033
    default language0999900
    fill factor (%)010000
    index create memory (KB)704214748364700
    lightweight pooling0100
    max degree of parallelism03200
    max server memory (MB)4214748364740004000
    max text repl size (B)021474836476553665536
    max worker threads32327670255
    media retention036500
    min memory per query (KB)5122147483647512512
    min server memory (MB)02147483647512512
    nested triggers0100
    network packet size (B)5126553640964096
    open objects0214748364700
    priority boost0100
    query governor cost limit0214748364700
    query wait (s)-12147483647-1-1
    recovery interval (min)03276700
    remote access0111
    remote login timeout (s)021474836472020
    remote proc trans0100
    remote query timeout (s)0214748364700
    scan for startup procs0111
    set working set size0100
    show advanced options0111
    two digit year cutoff1753999920492049
    user connections03276700
    user options03276711921192
  6. bradmcgehee New Member

    I am curious why you have the max server memory set to 4000, when you have 8GB of RAM in your server. You have said that there is an argument about this setting? If I was you, I would allocate much more RAM to SQL Server and see if that helps. Can you perhaps outline the pros and cons of this arguement?

    Also, the "cost threshold for parallelism 0 32767 0 0" is currently set to a value of 0, the default value is 5. Why have you chosen a setting of 0? Below is a tip I have for this setting on my website that you might find useful:

    "Using parallelism to execute a SQL Server query has its costs. This is because it takes a little additional overhead to run a query in parallel than to run it serially. But if the benefits of running a query using parallelism is higher than the costs, then using parallelism is a good thing.

    As a rule of thumb, if a query can run serially very fast, there is no point in even considering parallelism for the query, as the extra time required to evaluate it for possible parallelism might be longer than the time it takes to run the query serially.

    By default, if the Query Optimizer determines that a query will take less than 5 seconds to execute, parallelism is not considered by SQL Server. This 5 second figure can be modified using the "cost threshold for parallelism" SQL Server option. You can change this value anywhere from 0 to 32767 seconds. So if you set this value to 10, this means that the Query Optimizer won't consider parallelism for any query that it thinks will take less than 10 seconds to run.

    In most cases, you should not change this setting. But if you find that your SQL Server runs many queries with parallelism, and if the CPU rate is very high, raising this setting from 5 to a higher figure (you will have to experiment to find the ideal figure for your situation), will reduce the number of queries using parallelism, also reducing the overall usage of your server's CPUs, which may help the overall performance of your server.

    Another option to consider is to reduce the value from 5 seconds to a smaller number, although this could hurt, rather than help performance in many cases. One area where a smaller value might be useful is in cases where SQL Server is acting as a data warehouse and many very complex queries are being run. A lower value will allow the Query Optimizer to use parallelism more often, which can help in some situations.

    You will want to test changes to the default value thoroughly before implementing it on your production servers.

    If SQL Server only has access to a single CPU (either because there is only one CPU in the server, or because of an "affinity mask" setting, parallelism is not considered for a query.

    If you find in your audit that the cost threshold for parallelism is being used, find out why. If you can't get an answer, move it back to the default value."

    It is possible that a setting of 0 is producing unnecessary overhead on your server, contributing to some performance issues on your server.

    Also, I noticed that your min memory per query (KB) is set to 512K, instead of the default 1024K. I am curious as to why this setting has been made. Generally speaking, this setting is increased, not decreased, to boost performance.

    Also, if you have not done so, read this article at:http://www.sql-server-performance.com/awe_memory.asp

    Brad M. McGehee
  7. stanle New Member

    Hi Brad, apparently you're the only one taking this problem seriously. I really appreciate your help. The hints you've given me so far are good and helpful but they idicate that I still haven't managed to describe the real problem.
    Yes, I just noticed the parallelism and max memory per query settings and tuned them to more reasonable values (I have no access to the DBA who has set this server up, so I have no idea what he's meant by these settings).
    However, the problem is that at a certain point I have no free buffers available. The AWE is enabled, I have ~150MB of proc cache in memory and ~3.5GB of datapages. If I execute DBCC SQLPERF(lrustats) repetitively, I notice that the number of free buffers varies from 500,000 to 1. At a given moment it becomes 0 and the server hangs reporting an error (see my first posted message). The argument about the memory size is that for a very long period of time this server had been working with 4GB RAM, AWE not enabled (basically 2GB then) and at a given time it just crashed with this error (at that point I got involved into this problem). Another argument is that each processor needs at least 512MB for proper operation and 8*512 = 4GB (I still have problems believing this!?!?).
    Anyway, my problem is: why SQL Server doesn't free buffers when such are needed (I have no tables pinned in memory) and what might have caused this increased hunger for buffers. We are still investigating if it is a HW failure. So far we have eliminated the RAM but it might be also the controllers.
    Do you have any other ideas?

  8. bradmcgehee New Member

    I am not familiar with the "argument is that each processor needs at least 512MB for proper operation". This is new too me, and also hard to believe. If this were true, how could it have run when you only at 4GB on the server. The logic doesn't seem right.

    If this were my server, I would bump up the maximum memory to at least 6GB for SQL Server and see what happens. Of course, depending on the cause of your problem, this may or may not help. If it is solely a memory problem (not enough of it), then it will fix it.

    But if it is bug-related, it won't. I have been doing some research on how SQL Server frees up buffers, and it can occur two different ways. It can be done by the Lazywriter process or by a user thread. In either case, SQL Server is supposed to dynamically allocate free space in the buffers. According to Microsoft, the minimum amount of free space in the buffer cache is supposed to range from a minimum of 128KB to a maximum of 4GB. Based, on this, your system should never have less than 128K of free space. Once the buffer cache below the currently established minimum, SQL Server automatically goes out and frees pages to make more room. There are no settings for this option, it happens automatically. That is why I am suggesting that you might have run into a bug, as what you have described appears to be different that how SQL Server is supposed to behave.

    I don't know if it would be worth your while, but at this URL:


    is a list of counters you can use to more closely watch the AWE memory behavior. It might give you a clue as to what is happening.

    Also, it is remotely possible, as you have said, that it might be hardware or driver related, and you should consider this possibility.

    But if after you try bumping up the maximum memory, and check out these counters, and you can't still find the answer, this is the time I (personally) would call Microsoft Support. I always try everything I can before I call, but I end up calling Microsoft Support about 4-6 times a year, and it almost every case, it is due to a bug-related issue.

    Brad M. McGehee
  9. alzdba Member

    Did you set the MaxMemory for each SQLServer instance ? afaik sqlserver attempts to take all when awe is enabled.
  10. stanle New Member

    To the previous post:I have just one SQL instance installed.

    By this moment I haven't reproduced the error with the new settings applied and I hope I have solved the problem. Thanks to everyone for the help!

Share This Page