Article ID: 825225 - Last Review: October 25, 2005 - Revision: 2.1

FIX: You receive an error message when you run a parallel query that uses an aggregation function or the GROUP BY clause

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

SYMPTOMS

When you run a parallel query that uses an aggregation function or that uses the GROUP BY clause in Microsoft SQL Server 2000 Service Pack 3 (SP3), you may receive an error message that is similar to the following:

Server: Msg 701, Level 17, State 1, Line 3
There is insufficient system memory to run this query.

This problem may occur if the parallel query references a derived table that contains a UNION ALL operator and that contains an IN operator with a large list of values. You may also notice a stack dump file that is similar to the following for an instance of SQL Server that runs SQL Server 2000 SP3:

* 0087F20E Module(sqlservr+0047F20E) (CStackDump::GetContextAndDump+0000002E) 
* 00880D1F Module(sqlservr+00480D1F) (stackTrace+00000223) 
* 009361D3 Module(sqlservr+005361D3) (ex_raise2+00000160) 
* 0053DCCF Module(sqlservr+0013DCCF) (ex_raise+0000005C) 
* 00936469 Module(sqlservr+00536469) (ex_oom+00000011) 
* 00809CF8 Module(sqlservr+00409CF8) (SargManager::Init+0000004E) 
* 00550D2D Module(sqlservr+00150D2D) (RowsetSS::Init+00000131) 
* 005560D8 Module(sqlservr+001560D8) (OpenRowsetSS::OpenRowset+00000174) 
* 00556727 Module(sqlservr+00156727) (GetTableCursor+00000067) 
* 006F481A Module(sqlservr+002F481A) (CQScanRowset::ParallelGetRowset+0000010F)
* 006F3220 Module(sqlservr+002F3220) (CQScanRange::CQScanRange+00000088) 
* 00570662 Module(sqlservr+00170662) (CXteRange::QScanGet+000000D7) 
* 004D8C89 Module(sqlservr+000D8C89) (CQScanFilter::CQScanFilter+00000079) 
* 004D8D1F Module(sqlservr+000D8D1F) (CXteFilter::QScanGet+0000009C) 
* 004790E5 Module(sqlservr+000790E5) (CQScanFetch::CQScanFetch+00000146) 
* 006E54C4 Module(sqlservr+002E54C4) (CQScanPreFetchDelay::CQScanPreFetchDelay+0000001F) 
* 006E51DD Module(sqlservr+002E51DD) (CXteFetch::QScanGet+00000066) 
* 004790E5 Module(sqlservr+000790E5) (CQScanFetch::CQScanFetch+00000146) 
* 00478FAA Module(sqlservr+00078FAA) (CXteFetch::QScanGet+000000AE) 
* 006E67C5 Module(sqlservr+002E67C5) (CQScanConcat::CQScanConcat+00000227) 
* 006E6B62 Module(sqlservr+002E6B62) (CQScanXConcat::CQScanXConcat+00000011) 
* 006E64DD Module(sqlservr+002E64DD) (CXteConcat::QScanGet+00000099) 
* 0048D9D7 Module(sqlservr+0008D9D7) (CQScanSort::CQScanSort+000000BC) 
* 0048D8FA Module(sqlservr+0008D8FA) (CXteSort::QScanGet+0000012C) 
* 00560D9E Module(sqlservr+00160D9E) (CQScanStreamAggregate::CQScanStreamAggregate+000000CC) 
* 00560CD7 Module(sqlservr+00160CD7) (CXteStreamAggregate::QScanGet+000000D2) 
* 006FFE37 Module(sqlservr+002FFE37) (CQScanXProducer::CQScanXProducer+0000014E) 
* 006FFABD Module(sqlservr+002FFABD) (FnProducerThread+0000013C) 
* 0051DD5B Module(sqlservr+0011DD5B) (subproc_main+000000C2) 
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272) 
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098) 
* 7800C9EB Module(MSVCRT+0000C9EB) (beginthread+000000CE) 
* 77E887DD Module(KERNEL32+000087DD) (GetModuleFileNameA+000001D1)

Note This problem is a regression in SQL Server 2000 SP3 (build 789). This problem does not occur in SQL Server 2000 SP2 or earlier.

Back to the top

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 (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack

Back to the top

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) 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
   ---------------------------------------------------------------------
   31-May-2003  06:15  2000.80.818.0      78,400  Console.exe      
   24-Jun-2003  12:31  2000.80.818.0      33,340  Dbmslpcn.dll     
   24-Apr-2003  13:42                    786,432  Distmdl.ldf
   24-Apr-2003  13:42                  2,359,296  Distmdl.mdf
   29-Jan-2003  13:25                        180  Drop_repl_hotfix.sql
   23-Jun-2003  10:10  2000.80.837.0   1,557,052  Dtsui.dll        
   23-Jun-2003  10:10  2000.80.837.0     639,552  Dtswiz.dll       
   23-Apr-2003  14:21                    747,927  Instdist.sql
   02-May-2003  13:26                      1,581  Inst_repl_hotfix.sql
   07-Feb-2003  18:10  2000.80.765.0      90,692  Msgprox.dll      
   31-Mar-2003  13:37                      1,873  Odsole.sql
   04-Apr-2003  13:16  2000.80.800.0      62,024  Odsole70.dll     
   07-May-2003  08:11  2000.80.819.0      25,144  Opends60.dll     
   02-Apr-2003  09:18  2000.80.796.0      57,904  Osql.exe         
   02-Apr-2003  10:45  2000.80.797.0     279,104  Pfutil80.dll     
   22-May-2003  10:27                     19,195  Qfe469571.sql
   11-Jul-2003  04:34                  1,084,147  Replmerg.sql
   04-Apr-2003  09:23  2000.80.798.0     221,768  Replprov.dll     
   07-Feb-2003  18:10  2000.80.765.0     307,784  Replrec.dll      
   11-Jul-2003  04:26                  1,085,925  Replsys.sql
   31-May-2003  12:31  2000.80.818.0     492,096  Semobj.dll       
   31-May-2003  05:57  2000.80.818.0     172,032  Semobj.rll
   28-May-2003  11:59                    115,944  Sp3_serv_uni.sql
   31-May-2003  12:31  2000.80.818.0   4,215,360  Sqldmo.dll       
   07-Apr-2003  05:14                     25,172  Sqldumper.exe    
   19-Mar-2003  05:50  2000.80.789.0      28,672  Sqlevn70.rll
   01-Jul-2003  11:48  2000.80.834.0     180,736  Sqlmap70.dll     
   07-Feb-2003  18:10  2000.80.765.0      57,920  Sqlrepss.dll     
   17-Jul-2003  08:41  2000.80.841.0   7,553,105  Sqlservr.exe     
   07-Feb-2003  18:10  2000.80.765.0      45,644  Sqlvdi.dll       
   24-Jun-2003  12:31  2000.80.818.0      33,340  Ssmslpcn.dll     
   31-May-2003  12:31  2000.80.818.0      82,492  Ssnetlib.dll     
   31-May-2003  12:31  2000.80.818.0      25,148  Ssnmpn70.dll     
   31-May-2003  12:31  2000.80.818.0     158,240  Svrnetcn.dll     
   31-May-2003  06:29  2000.80.818.0      76,416  Svrnetcn.exe     
   30-Apr-2003  11:22  2000.80.816.0      45,132  Ums.dll          
   01-Jul-2003  11:49  2000.80.834.0      98,816  Xpweb70.dll

Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

Back to the top

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

824684 (http://support.microsoft.com/kb/824684/ ) Description of the standard terminology that is used to describe Microsoft software updates

327562 (http://support.microsoft.com/kb/327562/ ) FIX: Joins with a UNION ALL view may take a long time to compile and may use large amounts of memory

274030 (http://support.microsoft.com/kb/274030/ ) BUG: Outer joins with many tables may cause 701 error message "...Insufficient system memory..."

Back to the top