SYMPTOMS
Starting with hotfix build 8.00.0677 through 8.00.0779,
including SQL Server 2000 Service Pack 3 (SP3), any complex query that involves
a large number of OUTER JOINS consumes excessive amounts of Optimizer memory.
When the query runs, watch the performance monitor counter,
SQLServer:MemoryManager:OptimizerQuery(KB), to see the high optimizer memory
usage.
As a result of this extreme consumption of optimizer memory,
SQL Server can experience multiple memory related errors. For example:
2003-01-30 11:24:10.82 server Error: 17803, Severity: 20, State: 17
2003-01-30 11:24:10.82 server Insufficient memory available..
2003-01-30 11:28:11.90 server Error: 17803, Severity: 20, State: 14
2003-01-30 11:28:11.90 server Insufficient memory available..
2003-01-30 11:28:11.90 spid54 Buffer Distribution: Stolen=201493 Free=1206 Procedures=210
Inram=0 Dirty=12495 Kept=0
I/O=0, Latched=176, Other=570852
2003-01-30 11:28:11.90 spid54 Buffer Counts: Commited=786432 Target=786432 Hashed=583523
InternalReservation=427 ExternalReservation=37935 Min Free=1024
2003-01-30 11:28:11.90 spid54 Procedure Cache: TotalProcs=23 TotalPages=210 InUsePages=210
2003-01-30 11:28:11.90 spid54 Dynamic Memory Manager: Stolen=201666 OS Reserved=13368
OS Committed=13329
OS In Use=10313
Query Plan=303 Optimizer=208512 General=2416 Utilities=36 Connection=284 2003-01-30 11:28:11.90
spid54 Global Memory Objects: Resource=2149 Locks=129 SQLCache=107
Replication=2 LockBytes=2 ServerGlobal=43 Xact=52 2003-01-30 11:28:11.90 spid54
Query Memory Manager: Grants=2 Waiting=14 Maximum=37966 Available=0 2003-01-30
11:28:11.95 spid21 BPool::Map: no remappable address found. 2003-01-30
11:28:11.98 spid59 BPool::Map: no remappable address found. 2003-01-30
11:28:11.98 spid54 BPool::Map: no remappable address found. 2003-01-30
11:28:12.00 spid86 BPool::Map: no remappable address found.
Note: Of significance is the high value for
OptimizerMemory=208512.
RESOLUTION
Service pack information
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
Hotfix information
The English version of
this fix has the file attributes (or later) that are listed in the following
table. The dates and times for these files are listed in coordinated universal
time (UTC). When you view the file information, it is converted to local time.
To find the difference between UTC and local time, use the
Time Zone tab in the Date and Time tool in Control Panel.
Date Time Version Size File name
-------------------------------------------------------------------------
26-Feb-2003 03:41 2000.80.777.0 29,244 bytes Dbmslpcn.dll
08-Feb-2003 05:43 786,432 bytes Distmdl.ldf
08-Feb-2003 05:43 2,359,296 bytes Distmdl.mdf
30-Jan-2003 01:55 180 bytes Drop_repl_hotfix.sql
01-Feb-2003 00:32 2000.80.763.0 1,557,052 bytes Dtsui.dll
30-Jan-2003 05:18 746,470 bytes Instdist.sql
30-Jan-2003 01:55 1,402 bytes Inst_repl_hotfix.sql
08-Feb-2003 06:40 2000.80.765.0 90,692 bytes Msgprox.dll
08-Feb-2003 03:53 1,065,895 bytes Replmerg.sql
08-Feb-2003 06:40 2000.80.765.0 221,768 bytes Replprov.dll
08-Feb-2003 06:40 2000.80.765.0 307,784 bytes Replrec.dll
30-Jan-2003 05:18 1,084,318 bytes Replsys.sql
28-Feb-2003 01:34 2000.80.778.0 176,696 bytes Sqlmap70.dll
08-Feb-2003 06:40 2000.80.765.0 57,920 bytes Sqlrepss.dll
03-Mar-2003 19:15 2000.80.780.0 7,516,241 bytes Sqlservr.exe
08-Feb-2003 06:40 2000.80.765.0 45,644 bytes Sqlvdi.dll
26-Feb-2003 03:41 2000.80.777.0 29,244 bytes Ssmslpcn.dll
26-Feb-2003 03:41 2000.80.777.0 82,492 bytes Ssnetlib.dll
28-Feb-2003 01:34 2000.80.778.0 98,872 bytes Xpweb70.dll
Note: Because of file dependencies, the most recent hotfix or feature
that contains the files may also contain additional
files.