Solved

MSSQLSERVER_2814 error - A possible infinite recompile was detected

Posted on 2013-12-24
2
1,246 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Designing and Implementing a Data Warehouse 3 35
Correct Thousand and decimal separator in sql server 2008 3 25
Count with a subquery showing details 10 40
Query Syntax 17 31
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

12 Experts available now in Live!

Get 1:1 Help Now