How to use Editbin to adjust SQL Server thread stack size

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.

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:

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:

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:

This command shows the following for "size of stack reserve" for Sqlservr.exe 6.5 Service Pack1 (Intel), under the heading OPTIONAL HEADER VALUES:

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.