Solved

MSSQLSERVER_2814 error - A possible infinite recompile was detected

Posted on 2013-12-24
2
1,214 Views
Last Modified: 2014-01-04
MSSQLSERVER_2814-

the actual error is 2013-12-24 08:13:22.400      spid62      A possible infinite recompile was detected for SQLHANDLE 0x0300100074E50629CDF295007FA100000100000000000000, PlanHandle 0x0500100074E506294081CBDE000000000000000000000000, starting offset 974, ending offset 4688.  The last recompile reason was 2.


how would you trouble shoot it?
0
Comment
Question by:25112
2 Comments
 
LVL 5

Author Comment

by:25112
ID: 39738153
the query in the link http://technet.microsoft.com/en-us/library/cc645599(v=sql.105).aspx
points to a proc.. whose code is below.. are you able detect if the code has any leaks to cause multiple recompiles?
--------------------------------
 
CREATE       PROCEDURE [dbo].[BatchSingleXMLPayeeUpload]      
 @pFileName text,      
 @pXmlPayees text,      
 @pAuditUserName varchar(24)      
AS      
BEGIN      
 DECLARE @TempPayCenterFK as varchar(20)      
       
 ----------------Import into @tmpPayee----------------      
 DECLARE @tmpPayee TABLE      
 (      
  [Seq] [smallint] ,      
  [InfoDt] [varchar] (15) NULL ,      
  [ExID] [varchar] (50) NULL ,      
  [LNm] [varchar] (50) NULL ,      
  [FNm] [varchar] (50) NULL ,      
  [MI] [char] (1) NULL ,      
  [Sfx] [char] (10) NULL ,      
  [IDType] [varchar] (10) NULL ,      
  [IDNum] [varchar] (15) NULL ,      
  [DOB] [varchar] (15) NULL ,      
  [EthCd] [varchar] (10) NULL ,      
  [PrLan] [varchar] (10) NULL ,      
  [GenCd] [varchar] (10) NULL ,      
  [MilCd] [varchar] (10) NULL ,      
  [StAdd] [varchar] (100) NULL ,      
  [City] [varchar] (50) NULL ,      
  [StCd] [varchar] (5) NULL ,      
  [ZipCd] [varchar] (10) NULL ,      
  [SubJIDCd] [varchar] (5) NULL ,      
  [EdLvl] [varchar] (10) NULL ,      
  [LastYr] [varchar] (10) NULL ,      
  [PPay] [char] (1) NULL ,      
  [SU01] [char] (1) NULL ,      
  [SU02] [char] (1) NULL ,      
  [TCCd] [varchar] (20) NULL ,      
  [ICCd] [varchar] (10) NULL ,      
  [CredStat] [char] (1) NULL ,      
  [CredDt] [varchar] (15) NULL,      
  [PayCenterFK] [varchar] (20) NULL      
 )      
       
 DECLARE @hdoc INT      
 EXEC sp_xml_preparedocument @hdoc OUTPUT, @pXmlPayees      
       
 INSERT INTO @tmpPayee      
 (      
  Seq,      
  InfoDt,      
  ExID,      
  LNm,      
  FNm,      
  MI,      
  Sfx,      
  IDType,      
  IDNum,      
  DOB,      
  EthCd,      
  PrLan,      
  GenCd,      
  MilCd,      
  StAdd,      
  City,      
  StCd,      
  ZipCd,      
  SubJIDCd,      
  EdLvl,      
  LastYr,      
  PPay,      
  SU01,      
  SU02,      
  TCCd,      
  ICCd,      
  CredStat,      
  CredDt      
 )      
 (SELECT      
  Seq,      
  InfoDt,      
  ExID,      
  UPPER(RTRIM(LNm)),      
  UPPER(RTRIM(FNm)),      
  UPPER(RTRIM(MI)),      
  UPPER(RTRIM(Sfx)),      
  IDType,      
  Right(IDNum,9),    
  DOB,      
  EthCd,      
  PrLan,      
  GenCd,      
  MilCd,      
  UPPER(RTRIM(StAdd)),      
  UPPER(RTRIM(City)),      
  UPPER(RTRIM(StCd)),      
  left(ZipCd,5),    
  SubJIDCd,      
  EdLvl,      
  LastYr,      
  PPay,      
  SU01,      
  SU02,      
  TCCd,      
  ICCd,      
  CredStat,      
  CredDt      
 FROM OPENXML(@hdoc,'/Root/Demo') WITH (      
  Seq [smallint] 'Seq',      
  InfoDt [varchar] (15) 'InfoDt',      
  ExID [varchar] (50) 'ExID',      
  LNm [varchar] (50) 'LNm',      
  FNm [varchar] (50) 'FNm',      
  MI [char] (1) 'MI',      
  Sfx [char] (10) 'Sfx',      
  IDType [varchar] (10) 'IDType',      
  IDNum [varchar] (15) 'IDNum',      
  DOB [varchar] (15) 'DOB',      
  EthCd [varchar] (10) 'EthCd',      
  PrLan [varchar] (10) 'PrLan',      
  GenCd [varchar] (10) 'GenCd',      
  MilCd [varchar] (10) 'MilCd',      
  StAdd [varchar] (100) 'StAdd',      
  City [varchar] (50) 'City',      
  StCd [varchar] (5) 'StCd',      
  ZipCd [varchar] (10) 'ZipCd',      
  SubJIDCd [varchar] (5) 'SubJIDCd',      
  EdLvl [char] (10) 'EdLvl',      
  LastYr [varchar] (10) 'LastYr',      
  PPay [char] (1) 'PPay',      
  SU01 [char] (1) 'SU01',      
  SU02 [char] (1) 'SU02',      
  TCCd [varchar] (20) 'TCCd',      
  ICCd [varchar] (10) 'ICCd',      
  CredStat [char] (1) 'CredStat',      
  CredDt [varchar] (15) 'CredDt')      
 )      
       
 ------------------------------------------      
 UPDATE  @tmpPayee      
 SET PayCenterFK = PayCenterPK      
 FROM  tblPayCenter AS TC      
 WHERE TC.PayCenterNumber =      
  CASE      
   When ExID LIKE 'DEK%' Then  
    SUBSTRING(TCCd, LEN(TCCd)-3, 4)  
   ELSE      
    SUBSTRING(TCCd, LEN(TCCd)-5, 4)      
   END      
       
 UPDATE  @tmpPayee      
 SET PayCenterFK = 'OSS' +      
 CASE      
   When ExID LIKE 'DEK%' Then      
    TCCd        
   ELSE      
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)  
   END      
 WHERE PayCenterFK IS NULL      
 ----------------------------------------------      
 UPDATE  @tmpPayee      
 SET EdLvl =      
   CASE EdLvl      
   WHEN '1' THEN '1'    
   WHEN '2' THEN '3'    
   WHEN '3' THEN '4'      
   WHEN '4' THEN '5'      
   WHEN '5' THEN '6'      
   WHEN '6' THEN '7'      
   WHEN '7' THEN '8'      
   WHEN '8' THEN '9'      
   WHEN '9' THEN '10'      
   WHEN '10' THEN '11'      
   WHEN '11' THEN '12'      
   WHEN '12+' THEN '12'  
   ELSE NULL      
  END      
 WHERE ExID LIKE 'DEK%'      
 ----------------------------------------------      
     
     
 DECLARE @tmpPayAmount TABLE        
 (      
  [Seq] [smallint] NULL,      
  [ExID] [varchar] (50)  NULL ,      
  [PayDt] [varchar] (15)  NULL ,      
  [PayNbr] [varchar] (10)  NULL ,      
  [TCcd] [varchar] (20) NULL ,      
  [Fmt] [varchar] (10)  NULL ,      
  [Frm] [varchar] (10)  NULL ,      
  [Lan] [varchar] (10) NULL ,      
  [Std] [varchar] (10)  NULL ,      
  [Rnk] [varchar] (10)  NULL ,      
  [FErr] [varchar] (10)  NULL ,      
  [AErr] [varchar] (10)  NULL ,      
  [MErr] [varchar] (10)  NULL ,      
  [QErr] [varchar] (10)  NULL ,      
  [StagePayFK] [int] NULL ,      
  [PayFormFK] [varchar] (20) NULL ,      
  [PayCenterFK] [varchar] (20) NULL ,      
  [StagePayeeFK] [int] NULL      
 )      
       
 INSERT INTO @tmpPayAmount      
 (      
  Seq,      
  ExID,      
  PayDt,      
  PayNbr,      
  TCCd,      
  Fmt,      
  Frm,      
  Lan,      
  Std,      
  Rnk,      
  FErr,      
  AErr,      
  MErr,      
  QErr      
 )      
 (SELECT      
  Seq,      
  ExID,      
  PayDt,      
  PayNbr,      
  TCCd,      
  Fmt,      
  Frm,      
  Lan,      
  Std,      
  Rnk,      
  FErr,      
  AErr,      
  MErr,      
  QErr      
 FROM OPENXML(@hdoc,'/Root/Pay') WITH (        
  Seq [smallint] 'Seq',      
  ExID [varchar] (50)  'ExID' ,      
  PayDt [varchar] (15)  'PayDt' ,      
  PayNbr [varchar] (10)  'PayNbr' ,      
  TCCd [varchar] (20)  'TCCd' ,      
  Fmt [varchar] (10)  'Fmt' ,      
  Frm [varchar] (10)  'Frm' ,      
  Lan [varchar] (10)  'Lan' ,      
  Std [varchar] (10)  'Std' ,      
  Rnk [varchar] (10)  'Rnk',      
  FErr [varchar] (10)  'FErr',      
  AErr [varchar] (10)  'AErr',      
  MErr [varchar] (10)  'MErr',      
  QErr [varchar] (10)  'MErr')      
       
 )      
       
 UPDATE  @tmpPayAmount      
 SET PayCenterFK = PayCenterPK, @TempPayCenterFK = PayCenterPK      
 FROM  tblPayCenter AS TC        
 WHERE TC.PayCenterNumber =      
  CASE      
   When ExID LIKE 'DEK%' Then
    SUBSTRING(TCCd, LEN(TCCd)-3, 4)    
   ELSE      
    SUBSTRING(TCCd, LEN(TCCd)-5, 4)  
   END      
       
 UPDATE  @tmpPayAmount      
 SET PayCenterFK = 'OSS' +            
 CASE      
   When SUBSTRING(ExID, 1, 3) = 'DEK' Then    
    TCCd      
   ELSE      
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)      
   END      
 WHERE PayCenterFK IS NULL      
       
 UPDATE  @tmpPayAmount      
 SET @TempPayCenterFK = 'OSS' +          
 CASE      
   When ExID LIKE 'DEK%' Then
    TCCd      
   ELSE      
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)    
   END      
 WHERE PayCenterFK IS NULL      
       
       
       
 UPDATE @tmpPayAmount      
 SET PayFormFK =      
  CASE dbo.udf_GetPayFormPK(Fmt, Frm, SUBSTRING(PayDt,1,4))      
   WHEN 0 THEN 'OSS' + Fmt + '~' + Frm + '~' + SUBSTRING(PayDt,1,4)      
   ELSE CONVERT(VARCHAR(20),dbo.udf_GetPayFormPK(Fmt, Frm, SUBSTRING(PayDt,1,4)))      
  END      
       
       
 UPDATE @tmpPayAmount      
 SET Lan =          
  CASE Lan      
   WHEN '1' THEN '1'      
   WHEN '6' THEN '1'      
   WHEN '7' THEN '1'      
   WHEN '3' THEN '3'      
   WHEN '4' THEN '2'      
   WHEN '5' THEN '1'      
   WHEN '17' THEN '1'  
   WHEN '19' THEN '3'    
   WHEN '20' THEN '2'    
   ELSE 'OSS' + Lan      
  END      
 ---------------------------------------      
 DECLARE @maxHandle AS INT      
 SELECT @maxHandle = MAX(SEQ) FROM @tmpPayee      
 DECLARE @handle AS INT      
 SET @handle = 1      
 DECLARE @ExID AS VARCHAR(50)      
       
 WHILE (@handle <= @maxHandle)      
 BEGIN      
  SELECT @ExID = ExID FROM @tmpPayee WHERE Seq = @handle      
  DECLARE @PayeeExists AS BIT      
  SELECT @PayeeExists=COUNT(*) FROM tblStagePayee WHERE PayeeID=@ExID      
       
  IF(@PayeeExists>0)      
   UPDATE tblStagePayee      
   SET  DEKID = IDNum,      
    DEKIDTypeFK = IDType,      
    FirstName = FNm,      
    MiddleInitial = MI,      
    LastName = LNm,      
    NameSuffix = Sfx,      
    AddressLine1 = StAdd,      
    City = tE.City,      
    State = StCd,      
    ZipCode = SUBSTRING(ZipCd,1,5),      
    ZipCodeExtn =      
     CASE      
      WHEN LEN(ZipCd) > 5 THEN REPLACE(SUBSTRING(ZipCd, 6, LEN(ZipCd)), '-','')      
      ELSE NULL      
     END,      
    EdNA_CountyFK = SubJIDCd,      
    PrimaryLanguage = PrLan,      
    DateofBirth = DOB,      
    Gender = GenCd,      
    DEKEthnicCodeFK = EthCd,      
    HighestGradeCompleted = EdLvl,      
    WithdrawalYear = LastYr,      
    PAResidencyInd =      
     CASE SU01      
      WHEN 'N' THEN 0      
      ELSE 1      
     END,      
    SDIssuedDiplomaInd =      
     CASE SU02      
      WHEN 'Y' THEN 1      
      ELSE  0      
     END,      
    PracticePayInd = PPay,      
    CredStatusInd = CredStat,      
    CredStatusDate = CredDt,      
    InstructionalCenterCode = ICCd,      
    StagePayeeRecordStatusFK = 1,      
    ByPassInd = 0,          
    PayCenterFK = @TempPayCenterFK,      
    MilitaryPayeeInd = MilCd,      
    AuditChanDEKate = GETDATE(),      
    AuditChangeUserName = @pAuditUserName      
   FROM @tmpPayee AS tE      
   WHERE  Seq = @handle AND      
    PayeeID = @ExID    
  ELSE      
   INSERT INTO tblStagePayee(      
    PayeeID,      
    DEKID,      
    DEKIDTypeFK,      
    FirstName,      
    MiddleInitial,      
    LastName,      
    NameSuffix,      
    AddressLine1,      
    City,      
    State,      
    ZipCode,      
    ZipCodeExtn,      
    EdNA_CountyFK,      
    PrimaryLanguage,      
    DateofBirth,      
    Gender,      
    DEKEthnicCodeFK,      
    HighestGradeCompleted,      
    WithdrawalYear,      
    PAResidencyInd,      
    SDIssuedDiplomaInd,      
    PracticePayInd,      
    CredStatusInd,      
    CredStatusDate,      
    InstructionalCenterCode,      
    StagePayeeRecordStatusFK,      
    ByPassInd,      
    PayCenterFK,      
    MilitaryPayeeInd,      
    AuditAddDate,      
    AuditAddUserName,      
    AuditChanDEKate,      
    AuditChangeUserName)      
   (SELECT      
    ExID,      
    IDNum,      
    IDType,      
    FNm,      
    MI,      
    LNm,      
    Sfx,      
    StAdd,      
    City,      
    StCd,      
    SUBSTRING(ZipCd,1,5),      
    CASE      
     WHEN LEN(ZipCd) > 5 THEN REPLACE(SUBSTRING(ZipCd, 6, LEN(ZipCd)), '-','')      
     ELSE NULL      
    END,      
    SubJIDCd,      
    PrLan,      
    DOB,      
    GenCd,      
    EthCd,      
    EdLvl,      
    LastYr,      
    CASE SU01      
     WHEN 'N' THEN 0      
     ELSE 1      
    END,      
    CASE SU02      
     WHEN 'Y' THEN 1      
     ELSE 0      
    END,      
    PPay,          
    CredStat,      
    CredDt,      
    ICCd,      
    1,      
    0,        
    @TempPayCenterFK,      
    MilCd,      
    GETDATE(),      
    @pAuditUserName,      
    GETDATE(),      
    @pAuditUserName      
   FROM @tmpPayee AS tE      
   WHERE  Seq = @handle      
   )      
       
  DECLARE @StagePayeePK AS INT      
  SELECT @StagePayeePK = StagePayeePK      
  FROM tblStagePayee      
  WHERE PayeeID=@ExID      
       
  ----------------Import into tblStagePay----------------      
  UPDATE @tmpPayAmount      
  SET StagePayeeFK = SE.StagePayeePK      
  FROM tblStagePayee AS SE      
  WHERE Seq = @handle AND      
   PayeeID = @ExID    
       
  INSERT INTO tblStagePay(      
   StagePayeeFK,      
   PayCenterFK,      
   PayDate,      
   AuditAddDate,      
   AuditAddUserName,      
   AuditChanDEKate,      
   AuditChangeUserName)      
  (SELECT DISTINCT      
   StagePayeeFK,      
   tTS.PayCenterFK,      
   PayDt,      
   GETDATE(),      
   @pAuditUserName,      
   GETDATE(),      
   @pAuditUserName      
  FROM @tmpPayAmount AS tTS      
  WHERE Seq = @handle AND      
   CONVERT(VARCHAR(10),tTS.StagePayeeFK) + '~' + tTS.PayDt + '~' + tTS.PayCenterFK NOT IN      
    (SELECT CONVERT(VARCHAR(10),StagePayeeFK) + '~' + PayDate + '~' + PayCenterFK FROM tblStagePay)      
  )      
       
-- Added to ignore Payees not having Pay data      
       
  UPDATE tblStagePayee      
   SET  ByPassInd = 1      
  WHERE StagePayeePK not in (Select distinct StagePayeeFK from tblStagePay)      
       
  DECLARE @count Int      
  SELECT @count = count(*)  from tblStagePayee where StagePayeePK = @StagePayeePK and ByPassInd = 1      
  IF (@count > 0)      
  BEGIN      
   EXEC sp_xml_removedocument @hdoc      
   RETURN      
  END    
     
  UPDATE @tmpPayAmount      
  SET StagePayFK = ST.StagePayPK      
  FROM @tmpPayAmount AS tTS      
  INNER JOIN tblStagePay AS ST ON      
   tTS.StagePayeeFK = ST.StagePayeeFK AND      
   tTS.PayDt = ST.PayDate AND      
   tTS.PaycenterFK = ST.PaycenterFK      
  WHERE Seq = @handle      
       
  ----------------Import into tblStagePayAmount----------------      
  UPDATE  tblStagePayAmount      
  SET PayFormFK = tTS.PayFormFK,      
   PayLanguageFK = Lan,      
   SubjectAmount = Std,      
   AmountPercent = Rnk,      
   InvalidPayForm = FErr,       InvalidAge = AErr,      
   MaxRePayError =MErr,      
   QuestionableActivity = QErr,      
   AuditChanDEKate = GETDATE(),      
   AuditChangeUserName = @pAuditUserName      
  FROM @tmpPayAmount tTS      
  WHERE Seq = @handle AND      
   tTS.StagePayFK = tblStagePayAmount.StagePayFK AND      
   tTS.PayNbr = CONVERT(VARCHAR(10),PaySubjectFK)          
       
  INSERT INTO tblStagePayAmount(      
   StagePayFK,      
   PayFormFK,      
   PaySubjectFK,      
   PayLanguageFK,      
   SubjectAmount,      
   AmountPercent,      
   InvalidPayForm,      
   InvalidAge,      
   MaxRePayError,      
   QuestionableActivity,      
   AuditAddDate,      
   AuditAddUserName,      
   AuditChanDEKate,      
   AuditChangeUserName)      
  (SELECT      
   StagePayFK,      
   PayFormFK,      
   PayNbr,      
   Lan,      
   Std,      
   Rnk,      
   FErr,      
   AErr,      
   MErr,      
   QErr,      
   GETDATE(),      
   @pAuditUserName,      
   GETDATE(),      
   @pAuditUserName      
  FROM @tmpPayAmount tTS      
  WHERE Seq = @handle AND      
   CONVERT(VARCHAR(10),tTS.StagePayFK) + '~' + tTS.PayNbr NOT IN      (SELECT CONVERT(VARCHAR(10),StagePayFK) + '~' + CONVERT(VARCHAR(10),PaySubjectFK) FROM tblStagePayAmount)      
  )      
       
       
       
  ---------------------------------------------------------------------------      
  ----------------------Import into tblPayeeReason------------------------      
  INSERT INTO tblStagePayeeReason      
  (      
   StagePayeeFK,      
   PayReasonFK,      
   AuditAddDate,      
   AuditAddUserName      
  )      
  (      
  SELECT      
   SE.StagePayeePK,      
   RFT,      
   GETDATE(),      
   @pAuditUserName      
  FROM OPENXML(@hdoc,'/Root/Reason') WITH (      
   Seq [smallint] 'Seq' ,      
   ExID [varchar] (50)  'ExID' ,      
   RFT [varchar] (5)  'RFT') AS XR      
  INNER JOIN tblStagePayee AS SE ON      
   XR.ExID = SE.PayeeID      
  WHERE  Seq = @handle AND      
   CONVERT(VARCHAR(10),StagePayeePK) + '~' + RFT NOT IN      
    (SELECT CONVERT(VARCHAR(10),StagePayeeFK) + '~' + CONVERT(VARCHAR(5),PayReasonFK) FROM tblStagePayeeReason)      
  )      
  SET @handle = @handle + 1      
 END      
       
 EXEC sp_xml_removedocument @hdoc      
       EXEC BchBatchProcessStagePayee @StagePayeePK, @pFileName, @pAuditUserName      
     
       
END
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39738766
I would not consider it an error, but more of a warning that you need to optimize your Stored Procedure.

Since all your variables of type table are heaps (this would explain the reason of 2 "Statistics Changed"), I would start by replacing them with temporary tables that are appropriately indexed.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now