- 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.
Could not create server event thread.
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
- 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 procedures.
- 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.
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 version:
|Databases||SQL Server 7.0||SQL Server 2000|
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:
There is insufficient system memory to run this query.
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.