There may not be enough available virtual address space in
the Microsoft SQL Server process if all of the following conditions are true when you are
running SQL Server with the default configuration values:
- The server has 2 GB or more of RAM.
- There are a large number of databases on the system (for
example, more than 500).
- Most of the databases are updated (for example, the
databases do not have a "read only" status).
- There are enough concurrently active user connections to
use most of the 255 SQL Server worker threads.
SQL Server may generate the following error messages after the
whole 2-GB virtual address space is used up (or after the whole 3-GB virtual
address space is used up on SQL Server Enterprise Edition with the /3GB
switch in the Boot.ini file).
Error: 17802, Severity: 18, State: 3
Could not create server event thread.
SQL Server could not spawn process_loginread thread.
WARNING: Clearing procedure cache to free contiguous memory.
Buffer Distribution: Stolen=3454 Free=2540 Procedures=138
Inram=0 Dirty=1108 Kept=35
I/O=0, Latched=0, Other=214821
Buffer Counts: Committed=222096 Target=222096 Hashed=215964
InternalReservation=547 ExternalReservation=0 Min Free=512
Procedure Cache: TotalProcs=8 TotalPages=138 InUsePages=138
Dynamic Memory Manager: Stolen=3556
Query Plan=755 Optimizer=0
Global Memory Objects: Resource=1119 Locks=163 XDES=1 SQLCache=90 Replication=5 LockBytes=2 ServerGlobal=20
Query Memory Manager: Grants=0 Waiting=0 Maximum=164370 Available=164370
For each updated database, SQL Server allocates at least
one 64-KB block for use in formatting log records before they are written to
disk. This allocation occurs when the first log record is generated for the
database, such as during an INSERT, UPDATE, or DELETE statement. Depending on
the activity and the size of the generated log records, subsequent
modifications might trigger
64-KB allocations. SQL Server 7.0 will allocate no
more than three 54-KB blocks.
In SQL Server 2000, the upper number of allocations for each database
is a function of the number of processors that SQL Server is configured to use.
Use the -g
startup parameter to leave additional, unreserved virtual memory
available for these database allocations. The -g
parameter is documented in the Readme.txt of the SQL Server 7.0
service pack, and in SQL Server 2000 Books Online. The "More Information"
section in this article includes the settings that Microsoft recommends you use
to determine the appropriate value for this setting.
On a computer with 2 GB or more of RAM, SQL Server reserves
all but 256 MB (SQL Server 7.0) or 384 MB (SQL Server 2000) of virtual address
space during the startup process for use by the buffer pool. Additionally, to
storing the data and procedure cache, SQL Server uses the buffer pool memory to
service most other memory requests from SQL Server processes that are less than
8 KB. The remaining unreserved memory is intended for use with other
allocations that cannot be serviced from the buffer pool. These allocations
include, but are not limited to:
- Stacks and the associated thread environment block for any
threads that SQL Server creates. After SQL Server creates all 255 worker
threads, this is approximately 140 MB.
- Allocations that are made by other DLLs or processes that
are running in the SQL Server address space (which varies from system to
system), such as:
- OLE DB providers from any linked servers.
- COM objects that are loaded by use of the sp_OA system stored procedures or extended stored
- Any images (.exe or .dll) that are loaded in the address
space, which commonly use 20 to 25 MB, but possibly more if you are using
linked servers, sp_OA, or extended stored procedures.
- The process heap and any other heaps that SQL Server might
create. During the startup process, this is typically 10 MB, but may be more if
you are using linked servers, sp_OA, or extended stored procedures.
- Allocations from SQL Server processes that are greater than
8 KB, such as those required for large query plans, send and receive buffers if
the network packet size configuration option is close to 8 KB, and so on. To see this
number, look for the OS Reserved value that is reported in DBCC MEMORYSTATUS and that is reported
as number of 8-KB pages. Typical values for this are 5 MB.
- An array to track status information for each buffer that
is in the buffer pool. This is typically about 20 MB, unless SQL Server is
running with Address Windowing Extensions (AWE) enabled, in which case it can
be significantly higher.
On systems that have a large number of databases, the
64-KB allocations that are required for log formatting might occupy all of the
remaining virtual memory. At that point, subsequent allocations may fail,
resulting in one or more of the errors that are listed in the "Symptoms"
section in this article.
By using the -g
startup parameter, you can instruct SQL Server to leave
additional virtual memory available so that the combination of these
log-related allocations and other normal allocations does not run out of
virtual address space.
The following table lists some suggested
starting points for the -g
value depending on the number of databases and the server
Collapse this tableExpand this table
|Databases||SQL Server 7.0||SQL Server 2000|
This table was calculated by using the typical values
that are listed and it is also based on the assumption that no linked server
or extended stored procedures are in use. It is also based on the
assumption that you are not using AWE, and that SQL Profiler is not in use. Any
of these conditions may require you to increase the value of -g
Microsoft recommends that you take serious
consideration before you run a server with more databases than this because the
overhead that is required for having this number of databases on the system is
taking a lot of virtual memory away from the buffer pool, which may result in
poor performance for the system as a whole.
Additionally, creating lots of databases has the most significant effect on virtual memory. There are also per-database memory allocations that may cause a buffer pool out-of-memory condition. For example, you may receive the following error message:
Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.
This kind of out-of-memory condition may be more common in SQL Server 2005 because SQL Server 2005 tracks more per-database metadata than earlier versions of SQL Server.
When you track the index usage by using the sys.dm_db_index_usage_stats
stored procedure, the operation may require a significant amount of memory. The operation requires a significant amount of memory if each database has lots of indexes.