SQL Server Performance

SQL2005: There is insufficient system memory to run this query.

Discussion in 'Performance Tuning for DBAs' started by nmavra, Jun 25, 2008.

  1. nmavra New Member

    <p>Hello all,</p><p>I have a 64bit version of SQL 2005 (9.00.3257.00) with SP2 and the hot fix for this error message which I got from http://support.microsoft.com/KB/952624</p><p>The problem is that although this error message was fixed for 5 days, it has appeared again today for more than 50 times (most of them are in the same second). The full message I get is:</p><p>There is insufficient system memory to run this query.<br><br>For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. </p><p>&nbsp;</p><p>Any idea about what might be causing this?<br>The server is a Dell Poweredge 2950 with 4GB ram, and CPU usage is usually around 25%.</p><p>This is a screenshot from Perfmon ..</p><p>[IMG]<br><img src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll.jpg" mce_src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll.jpg"><img src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll2.jpg" mce_src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll2.jpg"> <img src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll3.jpg" mce_src="http://sql-server-performance.com/Community/forums/www.amaze.gr/sqlll3.jpg">[IMG]<br>[IMG] <br></p>
  2. techbabu303 New Member

    Please check with MS support on this , I guess this needs to reported to them if the fix failed on your server.
    -Sat
  3. nmavra New Member

    Do you see any weird behavior from the Perfmon screenshots? These are random intervals by the way, they are not related to the insufficient memory points in time..
  4. moh_hassan20 New Member

    you say " ... The problem is that although this error message was fixed for 5 days"
    how it is fixed.?
  5. nmavra New Member

    It seemed that it was fixed, because I couldn't see any errors.. But the errors came back about 5 days after the hotfix, which is also the last time the server was restarted.
    When we restart the service the error goes away, but i don't like restarting the server every 2-3 days because the down time is not acceptable for our clients..
  6. moh_hassan20 New Member

    I find strange values for memory:
    Target memory = 0.0001 KB ( that total you need !)
    Total server memory = 0.0001 KB (that server have !)
    The hit ratio must be 80% or more
    Also , i find Processor Queue Length =10 , and Processor Queue Length shouldrange from 0..1 for server load 25%.
    that may indicate cpu problem. (do server have only 1 cpu?).

    i suggest reviewing the sql server configuration parameters , especially for that dedicated for 64 bit , and measuring these counters again
    try adding all memory counters.
    check http://sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx

  7. nmavra New Member

    the configuration is as follows:
    2x Intel(R) Xeon(R) CPU 5120 @ 1.86GHz
    4GB Ram
    3 disks in RAID-5
    does this help a bit?
    Also, these are the correct measurements, does it look worrying now?
    [IMG]
    [IMG]
    [IMG]
  8. nandan.pandey New Member

    Hi, Do you have default memory setting for your SQL Server Instance (Min or Max) or you have configured it? Is there any other instance running on machine? You get a better idea from SQL Server 2005 Performance Dashboard Reports. It will give you what is the CPU and Memory utilization for your particular database. Regards,Nandan
  9. nmavra New Member

    I have set the minimum to be 1gb ram, and the maximum I left it as it was by default (some really big number).
    There is only one instance running, which holds 3 databases, the main one which has all the transactions is around 3gb, a smaller one of around 450mb (few transactions), and a small of 5mb with extremely few transactions..
    How can I access Performance Dashboard Reports? Can you please be a bit more specific? Thank you!
  10. satya Moderator

  11. moh_hassan20 New Member

    Let us concentrate on the error you get, as you point:
    The error says: FIX: Error message when you run a query that contains many OR clauses in SQL Server 2005: "There is insufficient system memory to run this query"
    - Fix the query that fire the error . can you post it to help more.
    meanwhile , setup page file size for 64-bit Windows Server 2003 as in: http://support.microsoft.com/kb/889654/

  12. moh_hassan20 New Member

    Try to apply the resolution that is described in the url you post, which require to install the Cumulative update package 8 (CU8) for SQL Server 2005 Service Pack 2
    CU8 is released on 17 june 2008
    , but it is not available for download, so contact Microsoft Customer Service and Support to obtain the cumulative update package 8
    for more info:
    http://support.microsoft.com/kb/951217/en-us
    ms support: http://support.microsoft.com/contactus/?ws=support


Share This Page