INF: Estimating the Initial SQL Server Memory Setting

Last reviewed: July 21, 1997
Article ID: Q168697
The information in this article applies to:
  • Microsoft SQL Server, version 6.5

SUMMARY

The following equation, where 'y' is the sp_configure memory setting in 2-KB pages, and 'x' is the amount of RAM on the computer in MB

   y=(.95x-22)*512

Provides a way to estimate what the initial SQL Server memory setting should be on a computer that is dedicated solely to SQL Server. You can effectively use this equation on computers that have an amount of RAM ranging from 64 MB to 2 GB. On smaller systems, ratioing the values in the table below will provide a more accurate recommended configuration setting.

MORE INFORMATION

From the Systems Administration for Microsoft SQL Server 6.5 Student Workbook, the following data is provided for a start in allocating memory to SQL Server:

Physical RAM on a computer dedicated to

SQL Server       Memory dedicated to       sp_configure 'memory'
 (MB) {x}        SQL Server (MB) {y}       setting (in 2-KB pages)

   16                5.468                 2,800 (minimum, not recommended)
   24                  8                   4,096 (SQL Server 6.5 default)
   32                 16                   8,192
   48                 28                  14,336
   64                 40                  20,480
 128                 100                  51,200
 256                 216                 110,592
 512                 464                 237,568

Using Excel Linear Regression capability, the last four values can be used to generate slope and intercepts for a best-fit line of the form y = mx+b. In this case, m = .9462 and b = (-22.09). For this purpose, .95 and (-22) will suffice, and the equation becomes y = (0.95)*x-22.

Thus, the appropriate SQL Server memory setting in MB is: (0.95)*(the amount of physical RAM on the computer, in MB) - 22.

Because the SQL Server memory setting is specified in 2-KB pages (for example, 2,048 bytes) and because 1 MB equals 1,048,576 bytes, the conversion factor from MB to 2-KB pages is (1,048,576/2,048) = 512 (2-KB pages/MB), the following equation delivers is the result:

The appropriate SQL Server sp_configure memory setting in 2-KB pages is: (((0.95)*(the amount of physical RAM on the computer, in MB) - 22)*512).

That is, y = (.95x-22)*512

Notes

  1. This estimation is only for computers dedicated solely to the use of SQL Server (that is, the computer is not running as a primary or backup domain controller, nor is it running WINS, DHCP, IIS, file and print services, or anything else that takes memory other than Windows NT Server or Workstation and SQL Server). If the computer is running other services, you can typically size the largest amount of memory that all of the other services or applications will use (and add some extra) and increase the '22' number (that is, the intercept/offset/'b') in the equation by that amount of additional RAM (in MB) that is unavailable.

  2. The data this is based on is from 64 to 512 MB of RAM. As with all statistics, you need to be aware of the statistical insignificance when going outside of the range of the raw dataset. Specifically, when the amount of available RAM is less than 64, ratio to nearest number in the table to determine what your value should be.

    For example, if your computer had 36 MB of RAM and was dedicated to SQL Server, you would make the following calculations:

          36 is 4 MB greater than 32, and because the gap in the table between
          32 and 48 is 16 MB, your 36 MB of RAM should call for a memory
          setting of 4/16=.25 of the gap between these to settings. That is,
          28 - 16 = 12 and (.25)*12 = 3, so for your 36-MB SQL Server computer
          dedicated to the server, a memory setting  of 16+3=19 MB is
          appropriate. Converting 19 MB to 2-KB pages implies an sp_configure
          memory setting of 19*512=9,728.
    

    On computers with more than 512 MB of RAM, this equation often provides a good benchmark.

  3. Although this data set was specifically listed for SQL Server 6.5, it seems to work well for SQL Server 6.0 too.


Additional query words: configure calculation formula
Keywords : kbusage SSrvGen
Version : 6.5
Platform : WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 21, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.