MORE INFORMATION
As a 32-bit operating system, Windows NT can address a
maximum of 4 GB of virtual memory. Windows NT divides this virtual address
range into 2 GB for user processes and another 2 GB for the system. Thus, SQL
Server can address a maximum of 2 GB of virtual memory (or physical memory if 2
GB or more is present on the system).
If you are attempting to
maximize the amount of memory allocated to SQL Server on a system with 2 GB or
more of physical memory, you may find that you are not able to set the SQL
Server memory parameter as high as you expect. For example, setting SQL
Server's memory parameter to 900,000 pages (approximately 1.8 GB) may result in
either of the following:
- An error when starting the server: initdata: suballocation
for buffer pages failed (x bytes requested).
-or- - An error when attempting to connect users: Not enough
memory for pss allocation.
Both errors are written to the Windows NT event log and the SQL
Server errorlog.
On initial analysis, it may seem reasonable that
you should be able to allocate 1.8 GB to SQL Server and leave the remaining
portion for the Windows NT system. However, by default, each thread created by
SQL Server is assigned 1 MB of virtual stack space by Windows NT, and this is
virtual memory that is not accounted for in the SQL Server memory configuration
parameter value. If SQL Server is configured to use several hundred worker
threads, a significant amount of virtual memory can be dedicated to thread
stack space, and thus unavailable for either server startup or the dynamic
memory allocation necessary as users connect. For example, 200 worker threads
would consume about 200 MB of virtual memory for stack space alone. Actually,
the bulk of the reserved virtual memory addresses are never used by the SQL
Server threads. However, Windows NT assigns them by default when SQL Server
creates the threads.
In cases where you have 2 GB or more of
physical memory on the system, and you are attempting to use as much memory as
possible for SQL Server (and for the SQL Server data cache), it is possible to
reduce the default thread virtual stack size, to effectively use these memory
addresses. The effect of reducing the stack size can be seen in increasing the
amount of memory allocated to SQL Server (by increasing the SQL Server memory
configuration parameter, or by simply being able to connect more users without
running out of memory).
To modify the SQL Server thread stack size,
you must use the Microsoft Binary File Editor (Editbin.exe), included with the
Microsoft Visual C++ development environment. The Editbin utility, run from the
Windows NT command prompt, is used to modify object files, executable files,
and dynamic-link libraries (DLLs). For more information on the Editbin utility,
see the Microsoft Visual C++ documentation.
To decrease the virtual
address range dedicated to each Windows NT thread used by SQL Server, run the
following command:
EDITBIN /STACK:reserve sqlservr.exe
This option sets the size of the stack in bytes, where the
reserve argument specifies the total stack allocation in virtual memory for
each thread. Editbin rounds the specified value up to the nearest four bytes.
Note that the optional commit argument is not required when adjusting the
thread stack size for SQL Server. For example, to set the thread stack size for
SQL Server to 64K, you would use the following command in the Mssql\Binn
directory:
EDITBIN /STACK:65536 sqlservr.exe
Be sure to take the following points into consideration
before running this command against Sqlservr.exe:
- Do not run this command unless your system has 2 GB or more
of physical memory.
- You should make a backup copy of the Sqlservr.exe file
before running this command.
- You should thoroughly test your application and its
interaction with SQL Server to observe any performance differences, before
making a change to the Sqlservr.exe in production.
Keep in mind that the amount of thread stack space will vary
from application to application. If you specify a stack size that is too low
for your application, SQL Server will report stack overflow errors.
Unfortunately, there is no easy way to estimate the amount of stack space
necessary. Therefore, it is recommended that the stack space not be set below
16K. Testing has shown that this amount should be adequate for most
applications.
To check the current thread stack size, you can use
Dumpbin.exe utility (included with Microsoft Visual C++) to look at the "size
of the stack reserve" value.
The following command (run from a
Windows NT command prompt) provides an example:
DUMPBIN /headers sqlservr.exe
This command shows the following for "size of stack reserve"
for Sqlservr.exe 6.5 Service Pack1 (Intel), under the heading OPTIONAL HEADER
VALUES:
100000 size of stack reserve
The value is displayed in a hexadecimal format (100,000 =
1,048,576 bytes, or 1 MB).
For more information on Dumpbin, see the
Microsoft Visual C++ documentation.