SQL Server Performance

SQL server free memory Issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by catchvaas, Dec 4, 2010.

  1. catchvaas New Member

    Hi,

    We have performance issue on one of our prod servers.

    it is Windows 2003 which has 8GB of RAM on it and running SQL server 2005.

    for SQL server 2005, the memory settings are : Minimum Server Memory: 128MB
    Maximum Server Memory: 4096 MB

    Minimum memory per query is set to 1024 KB.

    Checked the SQL server log and found the following errors:

    spid60 Error: 701, Severity: 17, State: 123
    spid60 There is insufficient system memory to run this query.

    The actual problem here is that the OS is stating that it has only 200MB of free memory left.
    I have been trying to fix this problem from long time...please help ASAP.
    Please help!
    Thanks in advance.
    catchvaas, Dec 4, 2010
    #2

  2. Luis Martin Moderator

    If you run Performance Monitor with SQL Server Memory and Target SQL server memory, what values do you have?
    Luis Martin, Dec 5, 2010
    #3

  3. catchvaas New Member

    I am sorry..am new to SQL administration...could you please tell me how do I check these values?
    catchvaas, Dec 6, 2010
    #4

  4. Luis Martin Moderator

    Control Panel--> Administrative Tools--> Performance-->
    Add this counters:
    SQL Server Memory Manager:
    1) Target Server Memory
    2) Total Server Memory
    Luis Martin, Dec 6, 2010
    #5

  5. catchvaas New Member

    Hi,
    Thanks, here are the values.
    SQL Server Memory Manager:
    1) Target Server Memory - max -1048576, Min-1022904, Average - 1048319
    2) Total Server Memory- Max - 1023728, Min - 1022928, Average - 1023528
    Please help1
    catchvaas, Dec 7, 2010
    #6

  6. Luis Martin Moderator

    Do you have 2003 and 2005 32 or 64 bits?
    Luis Martin, Dec 7, 2010
    #7

  7. Luis Martin Moderator

    From BOL:
    BOL:

    Error 701
    Severity Level 19
    Message Text
    There is insufficient system memory to run this query.

    Explanation
    The memory requirements for the set of tasks Microsoft® SQL Server™ is attempting to perform exceeds the amount of available memory. Either increase the amount of server memory or reduce the server workload.

    Action
    To decrease the server workload, reduce the number of users currently using SQL Server. To prevent additional users from logging in to SQL Server, pause the server. For more information, see Pausing and Resuming SQL Server.

    To increase server memory:

    Check the settings for both min server memory (MB) and max server memory (MB).
    If max server memory (MB) is a value close to the value of min server memory (MB), then increase the max server memory (MB) value.

    Check the size of the virtual memory paging file.
    If possible, increase the size of the file.

    Shut down any other applications running, if applicable, on the server.


    View the current memory usage information in Windows NT Performance Monitor.
    To view current memory usage information in preparation for tuning memory configuration, use the Total Server Memory (KB) Performance Monitor Counter of the SQLServer:General Statistics object.

    Add additional memory to the server.
    But, first answer the post about 32 or 64.
    Luis Martin, Dec 7, 2010
    #8

  8. catchvaas New Member

    Hi,
    Its windows 2003 Ent 32 bit
    catchvaas, Dec 7, 2010
    #9

  9. Luis Martin Moderator

    Well, you don't tell me about sql 2005 version!!!
    Anyway this is what you have to read:
    http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
    and if you can reach 8 GB, then live dynamically memory use.
    Is better to begin with max memory than some rare value you already have set.
    Luis Martin, Dec 7, 2010
    #10

  10. catchvaas New Member

    Hi Luis,
    Thanks for your help.
    Yea..Its windows 2003 Enterprise edition 32 bit and SQL server 2005 32 bit.
    So Do i set the max memory setting for sql server 2005 to 8gb?
    Could you please jot down the points that I can try to fix the problem?
    Thanks in advance.
    Regards,
    Vaas
    catchvaas, Dec 9, 2010
    #11

  11. Luis Martin Moderator

    My suggestion is : leave sql memory dynamically.
    I presume is SQL server dedicated.
    Luis Martin, Dec 10, 2010
    #12

Share This Page