Failed to reserve contiguous memory
-
Bonjour!
I got the following messages about 3-4 times a day on my SQL Server 2000 8.00.760 Service Pack 3A.
1- WARNING: Failed to reserve contiguous memory of Size= 131072.
2- Global Memory Objects: Resource=1770 Locks=191
SQLCache=181 Replication=2
LockBytes=2 ServerGlobal=43
Xact=22
3- Query Memory Manager: Grants=0 Waiting=0 Maximum=251252 Available=251252
4- Buffer Distribution: Stolen=1440 Free=1219 Procedures=4405
Inram=0 Dirty=75766 Kept=0
I/O=0, Latched=30, Other=255732
5- Buffer Counts: Commited=338592 Target=338592 Hashed=331528
InternalReservation=148 ExternalReservation=0 Min Free=128
6- Procedure Cache: TotalProcs=1216 TotalPages=4405 InUsePages=2331
7- Dynamic Memory Manager: Stolen=5845 OS Reserved=2032
OS Committed=2014
OS In Use=1881
Query Plan=5159 Optimizer=154
General=2088
Utilities=7 Connection=84
When this happened, users can't access the SQL Server anymore until I restart SQL services.
Sometimes, the error number= 17803, Severity = 20 "Insufficient memory available" appears, but not most of the time.
The OS is Windows 2000 Service Pack 4, with 3968MB of memory. Also, there is about 115 GB of free space on the data disk. There is 4.4 GB of free space on the drive on which SQL Server is installed.
The /3GB option is activated on the server. Also, the AWE option has been tried (with an addition on 2 GB of memory) without success.
I was wondering if the TEXT parameters in my stored procedures were the cause of this missing of memory space? Those TEXT parameters represent XML documents which are opened in the Stored procedures, and closed at the end of it. (The XMLs documents have about 3k to 10K maximum each and those Stored Proc have 7 parameters of this type).
There may be about up to 50 concurrent users on the SQL Server.
Also, I have 3 linked Server on Oracle 9i databases. It appears to deconnected some times by day also.
Thank you for helping me!
Anne-Marie
-
Hi,
Your SQL Server Kernel memory is getting fragmented.
Can you tell me why you are using the 3GB option?
Regards
Douglas Chrystall
Imceda Software
-
I had this same error a few months ago. I tracked it down, in my case, to an SQL select query that had just been modified to add 2 more 'LEFT JOINS' to it. When I checked the execution plan the extra left joins were causing Hash Joins (veeeery slow) which seemed to need around 64K of contiguous memory in total. However, after SQL server had been running for a few days it could not find enough contiguous memory to run the query and went into GO SLOW mode for all users until the query ended. There were already about 15 left joins in the SQL statement. Rewriting the SELECT solved the problem.
Apparently this problem can also occur if you have lots of columns specified in your WHERE condition of a query that are not 'typed' (SQL server doesn't know what type they are or how big they are). A number of KB (can't remember how much) of contiguous memory is allocated for each column. For example:
select * from tablea WHERE col1 = 'A' and col2 = 45
would need 2 times the amount of contigious space need for untyped variables. Whereas the following SQL need no contiguous memory (for untyped variables) because SQL server knows how much memory to allocate for the compares:
declare @a varchar(10), @b-2 int
select * from tablea WHERE col1 = @a and col2 = @b-2
Hope this gives you some ideas. 'Rule 1' of systems support probably applies here: find out what has just changed, its probably the cause of your problem.
Peter
-
Several possible reason cause this error.
1. memory fragment
2. memory leak
You need track down which one is the root cause of the problem is leak or fragment, first. Then, find what SQL statement or connection did not close correctly (login connection, cursor connection, odbc, jdbc ...etc). In my case, I had some jdbc connection was not close correctly, which cause the same error message. If you use jdbc, do you apply the SP ? The G.A. jdbc also had known memory leak problem.
-
Thanks Peter,
I can't apply Rule 1 now, because the server always has been configured like that. But I don't know why (maybe because of the affluence?) this warning message appears more and more often.
Here is my stored Proc which use XML documents. As you can see, I don't use left joins....
Regards
Anne-Marie
---------------------------------------------
--------------------------------------------------------------------------------
CREATE PROCEDURE PARITOP_SAVECDRESULTATMASSE
/*****************************************************************************************************************/
/* */
/* Objectif : Enregistrement des résultats du cacul de masse de façon atomique */
/* */
/* Date Auteur Modification */
/* ---------- --------------- ------------------------------------------------------------------------ */
/* 2003-04-29 Anne-Marie Guay Création initiale */
/* */
/*****************************************************************************************************************/
(
@PRC_ID INT,
@MONDE TINYINT,
@DATA_COMPARABLE TEXT,
@DATA_RESULTAT TEXT,
@DATA_RESIDUEL TEXT,
@DATA_CDBAREME TEXT,
@DATA_CDBAREMEFORF TEXT,
@DATA_VARAJUSTCOMP TEXT,
@DATA_VARAJUSTCOMPFORF TEXT
)
AS
DECLARE @hDoc_Comparable int
DECLARE @hDoc_Resultat int
DECLARE @hDoc_CDBareme int
DECLARE @hDoc_CDBaremeForf int
DECLARE @hDoc_Residuel int
DECLARE @hDoc_VarAjustComp int
DECLARE @hDoc_VarAjustCompForf int
exec sp_xml_preparedocument @hDoc_Comparable OUTPUT,@DATA_COMPARABLE, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_Resultat OUTPUT,@DATA_RESULTAT, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_CDBareme OUTPUT,@DATA_CDBAREME, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_CDBaremeForf OUTPUT,@DATA_CDBAREMEFORF, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_Residuel OUTPUT,@DATA_RESIDUEL, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_VarAjustComp OUTPUT,@DATA_VARAJUSTCOMP, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
exec sp_xml_preparedocument @hDoc_VarAjustCompForf OUTPUT,@DATA_VARAJUSTCOMPFORF, '<root xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"/>'
BEGIN TRAN SAVERESULT
---Dans tous les cas, on supprime le résultat du monde de l,analyse
--Soit pour faire un ménage, ou soit pour permettre de faire une insertion d'un résultat du monde de l'analyse, sans collision de clés.
UPDATE PARITOP_RESULTAT
SET RST_A_SUPPRIMER = 1
FROM PARITOP_RESULTAT RST INNER JOIN
OPENXML (@hDoc_Resultat,'xml/rs:data/z:row',0)
WITH ( PRC_ID INT,
UE_ID INT,
RST_MONDE TINYINT,
RST_PARITE FLOAT,
RST_PARITEMIN FLOAT,
RST_PARITEMAX FLOAT,
RST_INDICEVRAISS FLOAT,
RST_NIVCONCORDANCE FLOAT,
RST_ENVAJUST FLOAT,
RST_DATECALCUL SMALLDATETIME,
RST_STATUTCALCUL TINYINT
 
A ON A.UE_ID = RST.UE_ID AND A.PRC_ID =RST.PRC_ID AND A.RST_MONDE = RST.RST_MONDEWHERE RST.RST_MONDE = 0 -- Monde de l'analyse
INSERT INTO PARITOP_RESULTAT(
PRC_ID,
UE_ID,
RST_MONDE,
RST_PARITE,
RST_PARITEMIN,
RST_PARITEMAX,
RST_INDICEVRAISS,
RST_NIVCONCORDANCE,
RST_ENVAJUST,
RST_DATECALCUL,
RST_STATUTCALCUL,
RST_A_SUPPRIMER,
RST_ELIG_NB_VENTES,
RST_ELIG_VAL_MOY_ROLE,
RST_ELIG_PV_MOYEN,
RST_ELIG_RATIO_DISPERS,
RST_ELIG_IND_SEL_MIN,
RST_ELIG_IND_SEL_MAX,
RST_ELIG_IND_SEL_MOY
 
SELECT PRC_ID,
UE_ID,
RST_MONDE,
RST_PARITE,
RST_PARITEMIN,
RST_PARITEMAX,
RST_INDICEVRAISS,
RST_NIVCONCORDANCE,
RST_ENVAJUST,
RST_DATECALCUL,
RST_STATUTCALCUL,
0,
RST_ELIG_NB_VENTES,
RST_ELIG_VAL_MOY_ROLE,
RST_ELIG_PV_MOYEN,
RST_ELIG_RATIO_DISPERS,
RST_ELIG_IND_SEL_MIN,
RST_ELIG_IND_SEL_MAX,
RST_ELIG_IND_SEL_MOY
FROM OPENXML (@hDoc_Resultat,'xml/rs:data/z:row',0)
WITH ( PRC_ID INT,
UE_ID INT,
RST_MONDE TINYINT,
RST_PARITE FLOAT,
RST_PARITEMIN FLOAT,
RST_PARITEMAX FLOAT,
RST_INDICEVRAISS FLOAT,
RST_NIVCONCORDANCE FLOAT,
RST_ENVAJUST FLOAT,
RST_DATECALCUL SMALLDATETIME,
RST_STATUTCALCUL TINYINT,
RST_ELIG_NB_VENTES INT,
RST_ELIG_VAL_MOY_ROLE FLOAT,
RST_ELIG_PV_MOYEN FLOAT,
RST_ELIG_RATIO_DISPERS FLOAT,
RST_ELIG_IND_SEL_MIN FLOAT,
RST_ELIG_IND_SEL_MAX FLOAT,
RST_ELIG_IND_SEL_MOY FLOAT
 
IF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_CD_BAREME(
VAR_ID,
CDB_BAREME,
CDB_BAREMEMIN,
CDB_BAREMEMAX,
RST_ID
 
SELECT
VAR_ID,
CDB_BAREME,
CDB_BAREMEMIN,
CDB_BAREMEMAX,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_CDBareme,'xml/rs:data/z:row',0)
WITH (
PRC_ID INT,
UE_ID INT,
RST_MONDE TINYINT,
VAR_ID INT,
CDB_BAREME FLOAT,
CDB_BAREMEMIN FLOAT,
CDB_BAREMEMAX FLOAT
 
AIF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_CD_BAREMEFORF(
VAF_ID,
CBF_BAREME,
CBF_BAREMEMIN,
CBF_BAREMEMAX,
RST_ID
 
SELECT
VAF_ID,
CBF_BAREME,
CBF_BAREMEMIN,
CBF_BAREMEMAX,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_CDBaremeForf,'xml/rs:data/z:row',0)
WITH (
PRC_ID INT,
UE_ID INT,
RST_MONDE TINYINT,
VAF_ID INT,
CBF_BAREME FLOAT,
CBF_BAREMEMIN FLOAT,
CBF_BAREMEMAX FLOAT
 
AIF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_COMPARABLE(
VEN_ID,
CMP_SELMAN,
CMP_INDICESELECTION,
CMP_PVRES,
CMP_UTILISE,
CMP_PVAJUSTE,
CMP_PVRHAB,
CMP_TVM,
RST_ID
 
SELECT
VEN_ID,
CASE CMP_SELMAN
WHEN 'False' THEN 0
WHEN 'True' THEN 1
END,
CMP_INDICESELECTION,
CMP_PVRES,
CASE CMP_UTILISE
WHEN 'False' THEN 0
WHEN 'True' THEN 1
END,
CMP_PVAJUSTE,
CMP_PVRHAB,
CMP_TVM,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_Comparable,'xml/rs:data/z:row',0)
WITH (PRC_ID INT,UE_ID INT,VEN_ID INT,RST_MONDE TINYINT,CMP_SELMAN VARCHAR(10),CMP_INDICESELECTION FLOAT,CMP_PVRES FLOAT,CMP_UTILISE VARCHAR(10),CMP_PVAJUSTE FLOAT ,CMP_PVRHAB FLOAT, CMP_TVM FLOAT) A
IF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_VARAJUSTCOMP(
VAR_ID,
VEN_ID,
VJC_AJUST,
VJC_AJUSTMIN,
VJC_AJUSTMAX,
RST_ID
 
SELECT
VAR_ID,
VEN_ID,
VJC_AJUST,
VJC_AJUSTMIN,
VJC_AJUSTMAX,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_VarAjustComp,'xml/rs:data/z:row',0)
WITH (
PRC_ID INT,
VAR_ID INT,
UE_ID INT,
VEN_ID INT,
RST_MONDE TINYINT,
VJC_AJUST FLOAT,
VJC_AJUSTMIN FLOAT,
VJC_AJUSTMAX FLOAT
 
AIF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_VARAJUSTCOMPFORF(
VAF_ID,
VEN_ID,
VFC_AJUST,
VFC_AJUSTMIN,
VFC_AJUSTMAX,
RST_ID
 
SELECT
VAF_ID,
VEN_ID,
VFC_AJUST,
VFC_AJUSTMIN,
VFC_AJUSTMAX,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_VarAjustCompForf,'xml/rs:data/z:row',0)
WITH (
PRC_ID INT,
VAF_ID INT,
UE_ID INT,
VEN_ID INT,
RST_MONDE TINYINT,
VFC_AJUST FLOAT,
VFC_AJUSTMIN FLOAT,
VFC_AJUSTMAX FLOAT
 
AIF @@ERROR <> 0 GOTO TRAITER_ERR
INSERT INTO PARITOP_RESIDUEL(
VAR_ID,
VEN_ID,
RES_PV,
RES_ENVAJUST_1,
RES_ENVAJUST_2,
RES_FACTEUR_1,
RES_FACTEUR_2,
RES_PVRHABILLE,
RST_ID
 
SELECT
VAR_ID,
VEN_ID,
RES_PV,
RES_ENVAJUST_1,
RES_ENVAJUST_2,
RES_FACTEUR_1,
RES_FACTEUR_2,
RES_PVRHABILLE,
(SELECT Top 1 RST_ID FROM PARITOP_RESULTAT RST WHERE RST.PRC_ID = A.PRC_ID AND RST.UE_ID = A.UE_ID AND RST.RST_MONDE = A.RST_MONDE AND RST_A_SUPPRIMER = 0)
FROM OPENXML (@hDoc_Residuel,'xml/rs:data/z:row',0)
WITH (PRC_ID INT,VAR_ID INT,UE_ID INT,VEN_ID INT,RST_MONDE TINYINT,RES_PV FLOAT,RES_ENVAJUST_1 FLOAT,RES_ENVAJUST_2 FLOAT,RES_FACTEUR_1 FLOAT,RES_FACTEUR_2 FLOAT,RES_PVRHABILLE FLOAT) A
IF @@ERROR <> 0 GOTO TRAITER_ERR
COMMIT TRAN SAVERESULT
CLEANUP:
EXEC sp_xml_removedocument @hDoc_Comparable
EXEC sp_xml_removedocument @hDoc_Resultat
EXEC sp_xml_removedocument @hDoc_CDBareme
EXEC sp_xml_removedocument @hDoc_CDBaremeForf
EXEC sp_xml_removedocument @hDoc_Residuel
EXEC sp_xml_removedocument @hDoc_VarAjustComp
EXEC sp_xml_removedocument @hDoc_VarAjustCompForf
RETURN
TRAITER_ERR:
ROLLBACK TRAN SAVERESULT
GOTO CLEANUP
GO
----------------------------------------------
-
Wow. I haven't any way of easily testing something like that here, without alot of work.
I suspect that the INNER JOIN OPENXML near the top is doing a hash merge as there will be no index for the recordset returned by the OPENXML. I suggest that you run the stored procedure in Query Analyser and set it to 'Show Execution Plan'. Look out for any high costs, merge joins or hash joins. I believe the hash joins were the reason my query needed so much contiguous memory.
Good luck
Peter
-
That procedure will certainly use a lot of memory.
/3GB is only required if you are using products such as exchange, it is not needed for SQL Server.
If you stop/start SQL, does your query run ok.
Douglas
-
Yes, the procedure works OK with the same data when I restart the SQL Service on the server.
My conclusion is that the procedure itself is ok, but the memory management is the problem...
Anne-Marie
-
Anne,
-
Yes, Douglas???
-
Don't know what happened there.
Could you try running your server without the 3GB option.
Douglas
-
Yes, the next step is to remove the 3GB option and to add 2 Gig of memory on the server.
I was reading a microsoft KB
http://support.microsoft.com/default.aspx?scid=kb;en-us;832674&Product=sql2k
I was wondering if it could be the same problem but with TEXT parameter instead of sql_variant?
Anne-Marie
-
The procedure will work OK if you stop and start SQL server, because it will have no trouble getting the large amount of contiguous memory that it needs. The problem will always appear after the system has been running for a time and the memory is fragmented.
What you need to do is look at ways of getting the procedure to need less continuous memory.
I am not sure exactly what causes a lot of contiguous memory to be needed, but I believe hash merges do. In which case it would help if you put the result of the :
OPENXML (@hDoc_Resultat,'xml/rs:data/z:row',0)
WITH ( PRC_ID INT,
UE_ID INT,
RST_MONDE TINYINT,
RST_PARITE FLOAT,
RST_PARITEMIN FLOAT,
RST_PARITEMAX FLOAT,
RST_INDICEVRAISS FLOAT,
RST_NIVCONCORDANCE FLOAT,
RST_ENVAJUST FLOAT,
RST_DATECALCUL SMALLDATETIME,
RST_STATUTCALCUL TINYINT
 
statement into a temporary table and build an index on it before doing your inner join to table PARITOP_RESULTAT.
Hope this helps
Peter
-
Peter is making some very good points there.
I would still run without /3gb, I wouldn't put extra memory in just yet, see how you get on without it.
We have some code which should display the largest contig mem block, I will see if I can get that to you, you will be able to use this to help debug your memory usage. Also if you take on Peter's thought's, you will be able to find out which one consumes less memory.
Douglas
-
If you suspect it could be the same issue as the KB, then I would raise a call with MS.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply