I have a SQL 2000 server with a DTS package that is crashing with insufficient memory errors, where we've never had an issue before.
The DTS package is returning the error "There is insufficient system memory to run this query." The SQL error log shows stuff like: (trimmed duplicate lines for simplicity)
BPool::Map: no remappable address found. Buffer Distribution: Stolen=198908 Free=779 Procedures=349 Inram=0 Dirty=10291 Kept=0 Buffer Counts: Commited=917376 Target=917376 Hashed=717340 InternalReservation=174 ExternalReservation=24 Min Free=1024 Visible= 199856 Procedure Cache: TotalProcs=17 TotalPages=349 InUsePages=340 Dynamic Memory Manager: Stolen=164278 OS Reserved=1048 OS Committed=1026 OS In Use=1024 Query Plan=416 Optimizer=141466 General=16874 Buffer Counts: Commited=917376 Target=917376 Hashed=717340 Utilities=5840 Connection=206 InternalReservation=174 ExternalReservation=24 Min Free=1024 Visible= 199856 Procedure Cache: TotalProcs=17 TotalPages=349 InUsePages=340 Utilities=5840 Connection=206 Global Memory Objects: Resource=1272 Locks=279 SQLCache=52 Replication=2 LockBytes=2 ServerGlobal=23 Xact=35 Query Memory Manager: Grants=1 Waiting=0 Maximum=35238 Available=270
Can someone help me interpret/decipher some of these memory numbers?
This is a dedicated Windows2003 server running SQL2000 Enterprise edition, build 8.00.2282 (SP4). It has a total of 8GB of RAM. The SQL instance is configured with Min Memory = 0, Max Memory = 7167. AWE is enabled.
I've found a ton of articles that seem kinda related:
- KB 838459 - But we're already on SP4, and this isn't a reindex.
- KB 815114 - Seems relevant, as our query does have a LOT of tables in the join, more than half of them with a LEFT OUTER, but as I said, we're already on SP4.
- KB 831999 - Ditto. Already on SP4.
I admit this is a big hairy query, but we've run the identical query for years without trouble, and even if the query isn't really optimal, it shouldn't crash the server, or fail to execute, right?
Any ideas? Should we try the trace flag 3940 mentioned in kb838459, even though the scenario isn't exactly the same?
Yes, we've been encouraging them to upgrade to SQL2008 64-bit, but that's a while off.