Guest

Cisco Unified MeetingPlace

Field Notice: FA00211 - SQL Server sqlservr.exe Process Taking Up More Memory Than It Needs and Not Relinquishing It Properly


May 26, 2004


Products Affected

Product

Comments

Microsoft SQL Server or MSDE

All releases of SQL server. SQL Server = full SQL server or mini-SQL Server, For example MSDE

Problem Description

Note: This Field Notice is a legacy Latitude Field Notice that has been converted to the Cisco format so the information would remain available to their customers.

Note:?This document only applies to SQL server that is local to MeetingPlace Web. In other words, if SQL server is remote (not on the same machine as MeetingPlace Web), then you DO NOT need to cap the SQL server memory on that remote SQL server.

Microsoft SQL Server, either full SQL or the MSDE is represented by the process sqlservr.exe in the Task Manager. By default, sqlservr.exe uses dynamic memory allocation in which it uses as much memory as it needs, limited only by the amount of RAM installed, and returns the memory that it no longer needs to the system resource pool.

However, sqlservr.exe does not seem to relinquish memory properly. This can be annoying as it "hogs" memory that would otherwise have been made available to other applications, including Latitude gateways and IIS web server.

Problem Symptoms

Access to MeetingPlace Web pages can become significantly slowed. It may also cause a "Server is busy. Please try again" message.

Workaround/Solution

WORKAROUND:

The SQL memory must be capped at 25 percent of total RAM of the system, unless the total RAM is 256 MB, in which case the memory cap should be set to 80 MB.

If full SQL server is installed, you can use Enterprise Manager to configure memory allocation.

SOLUTION:

RESOLUTION 1 - Configure memory for MSDE using osql command

  1. Find out how much RAM memory you have on the server.

    1. A quick way to find out is from Start > Run, and enter winver.

    2. If you have 256 RAM, set SQL memory cap to 80 MB.

    3. If you have more than that, set memory cap to 25 percent of total RAM. For example, if you have 1 GB RAM, and then set memory to 250 MB.

  2. Log in to osql.

    C:\>osql -U sa -P yoursapassword
    1> sp_configure 'Show Advanced Options',1
    2> go
    

    DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

  3. The way sp_configure works is that there is a "config_value", and then there is a "run_value". After you change the config_value, you have to run the reconfigure command to make it a "run_value".

    1> reconfigure
    2> go
    
  4. Check the current configuration for 'max server memory'. The value 2147483647 is Microsoft's default, which is equivalent to 'no limit'.

    1> sp_configure 'max server memory'
    2> go
    name minimum maximum config_value run_value
    ---------------------------------------------------------------------
    max server memory (MB) 4 2147483647 2147483647 2147483647
    1> 
    
  5. Reduce the memory usage by setting it to an appropriate value. This Windows server has 256 MB RAM, so we change it to 80 MB.

    1> sp_configure 'max server memory',80
    2> go
    

    DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator. Configuration option 'max server memory (MB)' changed from 2147483647 to 80. Run the RECONFIGURE statement to install.

  6. Run reconfigure command for the new value to take effect.

    1> reconfigure
    2> go
    1> exit 
    
  7. Stop and restart SQL Server.

RESOLUTION 2 - Configure memory for full SQL server using Enterprise Manager

  1. Find out how much RAM memory you have on the server.

    1. A quick way to find out is from Start > Run, and enter winver .

    2. If you have 256 RAM, set SQL memory cap to 80 MB.

    3. If you have more than that, set memory cap to 25 percent of total RAM. For example, if you have 1 GB RAM, and then set memory to 250 MB.

  2. Open SQL Server's Enterprise Manager.

  3. Right-click on the server unit that you want to configure, and select "Properties".

  4. Click on "Memory" tab.

  5. Select "Dynamically configure SQL Server memory", and set Minimum to 0, and Maximum to 80 MB or other desired value. In this example, the MPWeb server has 1 GB RAM, so the Maximum value has been set somewhat higher at 200 MB.

  6. Stop and restart SQL server.

For More Information

If you require further assistance, or if you have any further questions regarding this field notice, please contact the Cisco Systems Technical Assistance Center (TAC) by one of the following methods:

Receive Email Notification For New Field Notices

Product Alert Tool - Set up a profile to receive email updates about reliability, safety, network security, and end-of-sale issues for the Cisco products you specify.