INF: How to Use Editbin to Adjust SQL Server Thread Stack Size

• Microsoft SQL Server, version 6.5

SUMMARY

The Microsoft Binary File Editor (Editbin.exe) can be used to adjust the stack size for SQL Server threads, to allow a large memory configuration for SQL Server on computers that have 2 GB or more of physical RAM.

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.
  

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. In reality, 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 sqlserver.exe

      EDITBIN /STACK:65536 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.

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

      100000 size of stack reserve

For more information on Dumpbin, see the Microsoft Visual C++ documentation.