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

    http://www.imceda.com

  • 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 @a = 'A', @b-2 = 45

    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

     &nbsp A  ON A.UE_ID = RST.UE_ID AND A.PRC_ID =RST.PRC_ID AND A.RST_MONDE = RST.RST_MONDE

     WHERE 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

         &nbsp

     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

    &nbsp

     IF @@ERROR <> 0 GOTO TRAITER_ERR

     INSERT INTO PARITOP_CD_BAREME( 

           VAR_ID,

           CDB_BAREME,

           CDB_BAREMEMIN,

           CDB_BAREMEMAX,

           RST_ID

          &nbsp

     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

     &nbsp A

     IF @@ERROR <> 0 GOTO TRAITER_ERR

     INSERT INTO PARITOP_CD_BAREMEFORF( 

           

           VAF_ID,

           CBF_BAREME,

           CBF_BAREMEMIN,

           CBF_BAREMEMAX,

           RST_ID

        &nbsp

     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

     &nbsp A

     IF @@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

       &nbsp

     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

       &nbsp

     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

     &nbsp A

     IF @@ERROR <> 0 GOTO TRAITER_ERR

     

     INSERT INTO PARITOP_VARAJUSTCOMPFORF( 

          VAF_ID,

          VEN_ID,

          VFC_AJUST,

          VFC_AJUSTMIN,

          VFC_AJUSTMAX,

          RST_ID

       &nbsp

     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

     &nbsp A

     IF @@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

       &nbsp

     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

     &nbsp

    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