March 6, 2012 at 1:46 pm
I'm troubleshooting a stored procedure that's generating a lot of COMPILE locks and cannot determine why it's not being inserted into the procedure cache. I can profile it and all I ever see is a CacheMiss, never a CacheInsert. If I remove a section of the code from the SPROC that contains an XMLNAMESPACE and a couple of CTE's, the stored procedure is then cached and the plan is reused. Is anyone aware of a bug in SQL Server or perhaps a design feature why this might be the case? There's no doubt in my mind that it's not storing the plan in cache, I just can't figure out why.
Here's the code that I believe is causing it not to cache the plan.
WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Cmc.CampusLink.Client.BusinessEntities.FinancialAid.FaAppProcess' as XMLName),
cteFileterISIRData
as (
select
FaISIRMain.FaISIRMainId,
FaStudentPell.VerifStatus,
FaISIRMain.ISIRRecordStatus,
FaISIRMain.BatchID,
rtrim(FaYear.Code) As Code,
FaYear.FaYearID,
FaISIRMain.TransactionId,
FaISIRData.ISIRData ,
FaISIRData.DateAdded,
FaISIRData.DateLstMod,
FaISIRData.UserID
from dbo.FaISIRData (nolock)
inner join dbo.FaISIRMain (nolock)
on FaISIRMain.FaISIRMainId = FaISIRData.FaISIRMainId
INNER JOIN dbo.FaSTudentPell (nolock) ON FaSTudentPell.TransactionID = FaISIRMain.TransactionID
inner join dbo.FaISIRAwardYearSchema (nolock)
on FaISIRMain.FaISIRAwardYearSchemaId = FaISIRAwardYearSchema.FaISIRAwardYearSchemaId
Inner join dbo.Fayear (Nolock)
On FaISIRAwardYearSchema.FaYearID = FaYear.FaYearID
Inner join dbo.FaiSIRStudentMatch(nolock) oN
FaISIRMain.FaISIRMainId = FaiSIRStudentMatch.FaISIRMainId
And FaiSIRStudentMatch.SyStudentID = @SyStudentID
),
cteMasterList
as (
select
FaISIRMainId,
BatchID,
VerifStatus ,
ISIRRecordStatus ,
rtrim(Code) As AwardYear,
FaYearID,
TransactionId,
DateAdded,
DateLstMod,
UserID,
tmpMasterList.ID.value('XMLName:IsirCode[1]','varchar(max)') as FieldName,
tmpMasterList.ID.value('XMLName:IsirValue[1]','varchar(max)') as FieldValue
from cteFileterISIRData
cross apply cteFileterISIRData.ISIRData.nodes('XMLName:IsirDataValues/XMLName:ValueList/XMLName:IsirValueType') tmpMasterList(ID)
)
SELECT dTableFaISIR.* FROM (
SELECT
FaISIR.FaISIRMainID,
FaISIR.BatchID,
FaISIR.TransactionID,
FaISIR.YearIndicator AS BatchYear,
FaISIR.SSN AS OriginalSSN,
FaISIR.OriginalNameID,
FaISIR.TransactionNumber,
FaISIR.LastName,
FaISIR.FirstName,
FaISIR.MI,
FaISIR.Address,
FaISIR.City,
FaISIR.State,
FaISIR.Zip,
FaISIR.DOB,
FaISIR.StudentPhone,
FaISIR.StudentDLNumber,
FaISIR.StudentDLState,
FaISIR.StudentEmail,
FaISIR.Citizen,
FaISIR.AlienNumber,
FaISIR.StudentMaritalStatus,
FaISIR.StudentMaritalStatusDate,
FaISIR.StudentLegState,
FaISIR.StudentLegResBef,
FaISIR.Male,
FaISIR.SelServiceReg,
FaISIR.Degree,
FaISIR.CollegeGradeLevel AS YearCollege,
FaISIR.EnrollStatus,
FaISIR.InterestedInAid,
FaISIR.HSGEDReceived,
FaISIR.FirstBachDegree,
FaISIR.FatherHighGrade,
FaISIR.MotherHighGrade,
FaISIR.DrugOffense,
FaISIR.StudentTaxFiled,
FaISIR.StudentTaxFormType,
FaISIR.StudentElig1040,
FaISIR.StudentGross,
FaISIR.StudentIncomeTax,
FaISIR.StudentExemptions,
FaISIR.StudentIncome,
FaISIR.SpouseIncome,
FaISIR.StudentCash,
FaISIR.StudentInvestment,
FaISIR.StudentBusiness,
FaISIR.BornBefore,
FaISIR.DegreeBeyond,
FaISIR.StudentMarried,
FaISIR.Children,
FaISIR.LegalDependents,
FaISIR.Orphan,
FaISIR.ActiveDutyMilitary,
FaISIR.Veteran,
FaISIR.ParentMaritalStatus,
FaISIR.ParentMaritalStatusDate,
FaISIR.FatherSSN,
FaISIR.FatherLastName,
FaISIR.FatherFInitial AS FatherFirstNameInitial,
FaISIR.FatherDOB,
FaISIR.MotherSSN,
FaISIR.MotherLastName,
FaISIR.MotherFirstNameInitial,
FaISIR.MotherDOB,
FaISIR.ParentNumFamily,
FaISIR.ParentNumCollege,
FaISIR.ParentLegState,
FaISIR.ParentLegResBef,
FaISIR.ParentLegResDate,
FaISIR.ParentFoodStamps,
FaISIR.ParentFreeLunch,
FaISIR.ParentTANFBenefits,
FaISIR.ParentWICBenefits,
FaISIR.ParentTaxFiled,
FaISIR.ParentTaxFormType,
FaISIR.ParentElig1040,
FaISIR.ParentGross,
FaISIR.ParentIncomeTax,
FaISIR.ParentExemptions,
FaISIR.FatherIncome,
FaISIR.MotherIncome,
FaISIR.ParentCash,
FaISIR.ParentInvestment,
FaISIR.ParentBusiness,
FaISIR.StudentNumFamily,
FaISIR.StudentNumCollege,
FaISIR.StudentSSIBenefits,
FaISIR.StudentFoodStamps,
FaISIR.StudentFreeLunch,
FaISIR.StudentTANFBenefits,
FaISIR.StudentWICBenefits,
FaISIR.FirstCollegeChoice,
FaISIR.FirstCollegeHousing,
FaISIR.SecondCollegeChoice,
FaISIR.SecondCollegeHousing,
FaISIR.ThirdCollegeChoice,
FaISIR.ThirdCollegeHousing,
FaISIR.FourthCollegeChoice,
FaISIR.FourthCollegeHousing,
FaISIR.FifthCollegeChoice,
FaISIR.FifthCollegeHousing,
FaISIR.SixthCollegeChoice,
FaISIR.SixthCollegeHousing,
FaISIR.DateCompleted,
FaISIR.SignedByFlag,
FaISIR.PreparerSSN,
FaISIR.PreparerEIN,
FaISIR.PreparerSignature,
FaISIR.ModelOverride,
FaISIR.FAAFederalSchoolCode,
FaISIR.Model,
FaISIR.TransactionSourceTypeCode,
FaISIR.TransactionRcptDate,
(FaISIR.AssumptionOverrides1+FaISIR.AssumptionOverrides2+FaISIR.AssumptionOverrides3+FaISIR.AssumptionOverrides4+FaISIR.AssumptionOverrides5+FaISIR.AssumptionOverrides6) AS AssumptionOverrides,
FaISIR.ETIDestCode,
(FaISIR.RejectOverrides3+FaISIR.RejectOverrides12+FaISIR.RejectOverrides20+FaISIR.RejectOverridesA+FaISIR.RejectOverridesB+FaISIR.RejectOverridesC+FaISIR.RejectOverridesG+FaISIR.RejectOverridesJ+FaISIR.RejectOverridesK+FaISIR.RejectOverridesN+FaISIR.RejectOverridesW) AS RejectOverrides,
FaISIR.ParentEmail,
FaISIR.CurrentSSN,
FaISIR.HistoryCorrectionApplied,
FaISIR.FAAadjust,
FaISIR.ApplicationSourceTypeCode,
FaISIR.ApplicationRcptDate,
FaISIR.AddressOnlyChangeFlag,
FaISIR.CPSpushedISIRflag,
FaISIR.EFCChangeFlag,
FaISIR.SSNChangeFlag,
FaISIR.RejectStatusChangeFlag,
FaISIR.SARCchangeFlag,
FaISIR.VerificationSelChangeFlag,
FaISIR.ComputeNum,
FaISIR.SourceofCorrection,
FaISIR.DuplicateSSN,
FaISIR.GraduateFlag,
FaISIR.PellEligFlag,
FaISIR.TransactionProcessedDate,
FaISIR.RecordType,
FaISIR.RejectCodes,
FaISIR.ReprocessedReasonCode,
FaISIR.SARCFlag,
FaISIR.Auto0EFCFlag,
FaISIR.SimplifiedNeeds,
FaISIR.ParentCalcTaxStatus,
FaISIR.StudentCalcTaxStatus,
FaISIR.AssumedCitizenship,
FaISIR.AssumedStudentMarital,
FaISIR.AssumedStudentGross,
FaISIR.AssumedStudentIncomeTax,
FaISIR.AssumedStudentIncome,
FaISIR.AssumedSpouseIncome,
FaISIR.AssumedStudentAmountWSC,
FaISIR.AssumedDOBPrior,
FaISIR.AssumedStudentMarried,
FaISIR.AssumedChildren,
FaISIR.AssumedStudentLegDep,
FaISIR.AssumedStudentNumFam,
FaISIR.AssumedStudentNumColl,
FaISIR.AssumedFSSN,
FaISIR.AssumedMSSN,
FaISIR.AssumedParentNumFam,
FaISIR.AssumedParentNumColl,
FaISIR.AssumedParentGross,
FaISIR.AssumedParentIncomeTax,
FaISIR.AssumedFatherIncome,
FaISIR.AssumedMotherIncome,
FaISIR.AssumedParentAmountWSC,
FaISIR.PEFCType,
FaISIR.SEFCType,
FaISIR.PAEFC01,
FaISIR.PAEFC02,
FaISIR.PAEFC03,
FaISIR.PAEFC04,
FaISIR.PAEFC05,
FaISIR.PAEFC06,
FaISIR.PAEFC07,
FaISIR.PAEFC08,
FaISIR.PAEFC10,
FaISIR.PAEFC11,
FaISIR.PAEFC12,
FaISIR.SAEFC01,
FaISIR.SAEFC02,
FaISIR.SAEFC03,
FaISIR.SAEFC04,
FaISIR.SAEFC05,
FaISIR.SAEFC06,
FaISIR.SAEFC07,
FaISIR.SAEFC08,
FaISIR.SAEFC10,
FaISIR.SAEFC11,
FaISIR.SAEFC12,
FaISIR.CorrectionFlags,
FaISIR.HighlightFlags,
FaISIR.FAFSAdataVerifyFlags,
FaISIR.DHSMatchFlag,
FaISIR.SecondaryDHSMatchFlag,
FaISIR.DHSVerificationNum,
FaISIR.NSLDSMatchFlag,
FaISIR.NSLDSPostReasonCode,
FaISIR.FatherSSNMatchFlag,
FaISIR.MotherSSNMatchFlag,
FaISIR.SelServiceMatchFlag,
FaISIR.SelServiceRegFlag,
FaISIR.SSACitizenFlag,
FaISIR.SSNMatchFlag,
FaISIR.VAMatchFlag,
FaISIR.CommentCodes,
FaISIR.EFedSchoolCodeIndicator,
FaISIR.ETrxIndicatorFlag,
FaISIR.MultiSchoolCodeFlags,
FaISIR.VerificationTrackingFlag,
FaISIR.SelectedForVerification,
FaISIR.ISIRRecordStatus AS Processed,
FaISIR.VerifStatus as VerificationComplete,
FaISIR.UserID,
FaISIR.DateAdded,
FaISIR.DateLstMod,
FaISIR.TotalIncome AS IntermediateResults_TI,
FaISIR.ATI as IntermediateResults_ATI ,
FaISIR.STX as IntermediateResults_STX ,
FaISIR.EA as IntermediateResults_EA ,
FaISIR.IPA as IntermediateResults_IPA ,
FaISIR.AI As IntermediateResults_AI,
FaISIR.CAI as IntermediateResults_CAI ,
FaISIR.DNW as IntermediateResults_DNW ,
FaISIR.NW as IntermediateResults_NW ,
FaISIR.APA as IntermediateResults_APA ,
FaISIR.PCA as IntermediateResults_PCA ,
FaISIR.AAI as IntermediateResults_AAI ,
FaISIR.TSC as IntermediateResults_TSC ,
FaISIR.TPC as IntermediateResults_TPC ,
FaISIR.PC as IntermediateResults_PC ,
FaISIR.STI as IntermediateResults_STI ,
FaISIR.SATI As IntermediateResults_SATI ,
FaISIR.SIC As IntermediateResults_SIC ,
FaISIR.SDNW as IntermediateResults_SDNW ,
FaISIR.SCA as IntermediateResults_SCA ,
FaISIR.FTI as IntermediateResults_FTI ,
FaISIR.SECTI as IntermediateResults_SECTI,
FaISIR.SECATI As IntermediateResults_SECATI,
FaISIR.SECSTX as IntermediateResults_SECSTX,
FaISIR.SECEA as IntermediateResults_SECEA,
FaISIR.SECIPA as IntermediateResults_SECIPA,
FaISIR.SECAI as IntermediateResults_SECAI ,
FaISIR.SECCAI AS IntermediateResults_SECCAI,
FaISIR.SECDNW as IntermediateResults_SECDNW,
FaISIR.SECNW as IntermediateResults_SECNW ,
FaISIR.SECAPA as IntermediateResults_SECAPA,
FaiSIR.SECPCA as IntermediateResults_SECPCA,
FaISIR.SECAAI as IntermediateResults_SECAAI,
FaISIR.SECTSC as IntermediateResults_SECTSC,
FaISIR.SECTPC as IntermediateResults_SECTPC,
FaISIR.SECPC as IntermediateResults_SECPC ,
FaISIR.SECSTI as IntermediateResults_SECSTI,
FaISIR.SECSATI as IntermediateResults_SECSATI,
FaISIR.SECSEC as IntermediateResults_SECSEC ,
FaISIR.SECSDNW as IntermediateResults_SECSDNW,
FaISIR.SECSCA as IntermediateResults_SECSCA ,
FaISIR.SECFTI as IntermediateResults_SECFTI
FROM
(SELECT FaISIRMainId, BatchID,AwardYear,FaYearID,TransactionId,UserID,DateAdded,DateLstMod,ISIRRecordStatus,VerifStatus,FieldName,FieldVAlue FROM cteMasterList CTE
WHERE CTE.Fieldname IN ('SSN','YearIndicator','OriginalNameID','TransactionNumber','LastName','FirstName','MI','Address','City',
'State','Zip','DOB','StudentPhone','StudentDLNumber','StudentDLState','StudentEmail','Citizen','AlienNumber','StudentMaritalStatus',
'StudentMaritalStatusDate','StudentLegState','StudentLegResBef','Male','SelServiceReg','Degree','CollegeGradeLevel','EnrollStatus',
'InterestedInAid','HSGEDReceived','FirstBachDegree','FatherHighGrade','MotherHighGrade','DrugOffense','StudentTaxFiled','StudentTaxFormType',
'StudentElig1040','StudentGross','StudentIncomeTax','StudentExemptions','StudentIncome','SpouseIncome','StudentCash','StudentInvestment',
'StudentBusiness','BornBefore','DegreeBeyond','StudentMarried','Children','LegalDependents','Orphan','ActiveDutyMilitary','Veteran',
'ParentMaritalStatus','ParentMaritalStatusDate','FatherSSN','FatherLastName','FatherFInitial','FatherDOB','MotherSSN','MotherLastName','MotherFirstNameInitial','MotherDOB','ParentNumFamily',
'ParentNumCollege','ParentSSIBenefits','ParentLegState','ParentLegResBef','ParentLegResDate','ParentFoodStamps','ParentFreeLunch','ParentTANFBenefits',
'ParentWICBenefits','ParentTaxFiled','ParentTaxFormType','ParentElig1040','ParentGross','ParentIncomeTax','ParentExemptions','FatherIncome','MotherIncome',
'ParentCash','ParentInvestment','ParentBusiness','StudentNumFamily','StudentNumCollege','StudentSSIBenefits','StudentFoodStamps','StudentFreeLunch',
'StudentTANFBenefits','StudentWICBenefits','FirstCollegeChoice','FirstCollegeHousing','SecondCollegeChoice','SecondCollegeHousing',
'ThirdCollegeChoice','ThirdCollegeHousing','FourthCollegeChoice','FourthCollegeHousing','FifthCollegeChoice','FifthCollegeHousing','SixthCollegeChoice','SixthCollegeHousing',
'DateCompleted','SignedByFlag','PreparerSSN','PreparerEIN','PreparerSignature','ModelOverride','FAAFederalSchoolCode','Model','TransactionSourceTypeCode',
'TransactionRcptDate','AssumptionOverrides1','AssumptionOverrides2','AssumptionOverrides3','AssumptionOverrides4','AssumptionOverrides5','AssumptionOverrides6','ETIDestCode',
'RejectOverrides3','RejectOverrides12','RejectOverrides20','RejectOverridesA','RejectOverridesB','RejectOverridesC','RejectOverridesG','RejectOverridesJ','RejectOverridesK',
'RejectOverridesN','RejectOverridesW','ParentEmail','CurrentSSN','HistoryCorrectionApplied','FAAadjust','ApplicationSourceTypeCode',
'ApplicationRcptDate','AddressOnlyChangeFlag','CPSpushedISIRflag','EFCChangeFlag','SSNChangeFlag','RejectStatusChangeFlag','SARCchangeFlag','VerificationSelChangeFlag',
'ComputeNum','SourceofCorrection','DuplicateSSN','GraduateFlag','PellEligFlag','TransactionProcessedDate','RecordType','RejectCodes','ReprocessedReasonCode',
'SARCFlag','Auto0EFCFlag','SimplifiedNeeds','ParentCalcTaxStatus','StudentCalcTaxStatus','AssumedCitizenship','AssumedStudentMarital','AssumedStudentGross',
'AssumedStudentIncomeTax','AssumedStudentIncome','AssumedSpouseIncome','AssumedStudentAmountWSC','AssumedDOBPrior','AssumedStudentMarried','AssumedChildren',
'AssumedStudentLegDep','AssumedStudentNumFam','AssumedStudentNumColl','AssumedParentMarital','AssumedFSSN','AssumedMSSN','AssumedParentNumFam','AssumedParentNumColl',
'AssumedParentGross','AssumedParentIncomeTax','AssumedFatherIncome','AssumedMotherIncome','AssumedParentAmountWSC','PEFC','SEFC','PEFCType','SEFCType','PAEFC01',
'PAEFC02','PAEFC03','PAEFC04','PAEFC05','PAEFC06','PAEFC07','PAEFC08','PAEFC10','PAEFC11','PAEFC12','SAEFC01','SAEFC02','SAEFC03','SAEFC04','SAEFC05',
'SAEFC06','SAEFC07','SAEFC08','SAEFC10','SAEFC11','SAEFC12','TotalIncome','ATI','STX','EA','IPA','AI','CAI','DNW', 'NW', 'APA','PCA','AAI','TSC','TPC','PC','STI','SATI',
'SIC', 'SDNW','SCA','FTI', 'SECTI', 'SECATI','SECSTX','SECEA', 'SECIPA','SECAI','SECCAI','SECDNW','SECNW','SECAPA','SECPCA','SECAAI','SECTSC','SECTPC','SECPC','SECSTI','SECSATI',
'SECSEC','SECSDNW','SECSCA','SECFTI','CorrectionFlags','HighlightFlags','FAFSAdataVerifyFlags','DHSMatchFlag','SecondaryDHSMatchFlag','DHSVerificationNum','NSLDSMatchFlag',
'NSLDSPostReasonCode','FatherSSNMatchFlag','MotherSSNMatchFlag','SelServiceMatchFlag','SelServiceRegFlag','SSACitizenFlag','SSNMatchFlag','VAMatchFlag',
'CommentCodes','EFedSchoolCodeIndicator','ETrxIndicatorFlag','MultiSchoolCodeFlags','VerificationTrackingFlag','SelectedForVerification'))P
PIVOT(MAX(FieldVAlue) FOR Fieldname IN (
[SSN],[OriginalNameID],[YearIndicator],[TransactionNumber],[LastName],[FirstName],[MI],[Address],[City],
[State],[Zip],[DOB],[StudentPhone],[StudentDLNumber],[StudentDLState],[StudentEmail],[Citizen],[AlienNumber],[StudentMaritalStatus],
[StudentMaritalStatusDate],[StudentLegState],[StudentLegResBef],[Male],[SelServiceReg],[Degree],[CollegeGradeLevel],[EnrollStatus],
[InterestedInAid],[HSGEDReceived],[FirstBachDegree],[FatherHighGrade],[MotherHighGrade],[DrugOffense],[StudentTaxFiled],[StudentTaxFormType],
[StudentElig1040],[StudentGross],[StudentIncomeTax],[StudentExemptions],[StudentIncome],[SpouseIncome],
[StudentCash],[StudentInvestment],[StudentBusiness],[BornBefore],[DegreeBeyond],
[StudentMarried],[Children],[LegalDependents],[Orphan],[ActiveDutyMilitary],[Veteran],[ParentMaritalStatus],[ParentMaritalStatusDate],[FatherSSN],
[FatherLastName],[FatherFInitial],[FatherDOB],[MotherSSN],[MotherLastName],[MotherFirstNameInitial],[MotherDOB],[ParentNumFamily],[ParentNumCollege],[ParentSSIBenefits],
[ParentLegState],[ParentLegResBef],[ParentLegResDate],[ParentFoodStamps],[ParentFreeLunch],[ParentTANFBenefits],
[ParentWICBenefits],[ParentTaxFiled],[ParentTaxFormType],[ParentElig1040],[ParentGross],[ParentIncomeTax],[ParentExemptions],[FatherIncome],[MotherIncome],
[ParentCash],[ParentInvestment],[ParentBusiness],[StudentNumFamily],[StudentNumCollege],[StudentSSIBenefits],
[StudentFoodStamps],[StudentFreeLunch],[StudentTANFBenefits],[StudentWICBenefits],[FirstCollegeChoice],[FirstCollegeHousing],[SecondCollegeChoice],[SecondCollegeHousing],
[ThirdCollegeChoice],[ThirdCollegeHousing],[FourthCollegeChoice],[FourthCollegeHousing],[FifthCollegeChoice],[FifthCollegeHousing],[SixthCollegeChoice],[SixthCollegeHousing],
[DateCompleted],[SignedByFlag],[PreparerSSN],[PreparerEIN],[PreparerSignature],[ModelOverride],[FAAFederalSchoolCode],[Model],[TransactionSourceTypeCode],
[TransactionRcptDate],[AssumptionOverrides1],[AssumptionOverrides2],[AssumptionOverrides3],[AssumptionOverrides4],[AssumptionOverrides5],[AssumptionOverrides6],[ETIDestCode],
[RejectOverrides3],[RejectOverrides12],[RejectOverrides20],[RejectOverridesA],[RejectOverridesB],[RejectOverridesC],[RejectOverridesG],[RejectOverridesJ],[RejectOverridesK],
[RejectOverridesN],[RejectOverridesW],[ParentEmail],[CurrentSSN],[HistoryCorrectionApplied],[FAAadjust],[ApplicationSourceTypeCode],
[ApplicationRcptDate],[AddressOnlyChangeFlag],[CPSpushedISIRflag],[EFCChangeFlag],[SSNChangeFlag],[RejectStatusChangeFlag],[SARCchangeFlag],[VerificationSelChangeFlag],
[ComputeNum],[SourceofCorrection],[DuplicateSSN],[GraduateFlag],[PellEligFlag],[TransactionProcessedDate],[RecordType],[RejectCodes],[ReprocessedReasonCode],
[SARCFlag],[Auto0EFCFlag],[SimplifiedNeeds],[ParentCalcTaxStatus],[StudentCalcTaxStatus],[AssumedCitizenship],[AssumedStudentMarital],[AssumedStudentGross],
[AssumedStudentIncomeTax],[AssumedStudentIncome],[AssumedSpouseIncome],[AssumedStudentAmountWSC],[AssumedDOBPrior],[AssumedStudentMarried],[AssumedChildren],
[AssumedStudentLegDep],[AssumedStudentNumFam],[AssumedStudentNumColl],[AssumedParentMarital],[AssumedFSSN],[AssumedMSSN],[AssumedParentNumFam],[AssumedParentNumColl],
[AssumedParentGross],[AssumedParentIncomeTax],[AssumedFatherIncome],[AssumedMotherIncome],[AssumedParentAmountWSC],[PEFC],[SEFC],[PEFCType],[SEFCType],[PAEFC01],
[PAEFC02],[PAEFC03],[PAEFC04],[PAEFC05],[PAEFC06],[PAEFC07],[PAEFC08],[PAEFC10],[PAEFC11],[PAEFC12],[SAEFC01],[SAEFC02],[SAEFC03],[SAEFC04],[SAEFC05],
[SAEFC06],[SAEFC07],[SAEFC08],[SAEFC10],[SAEFC11],[SAEFC12],[TotalIncome],[ATI],[STX],[EA],[IPA],[AI],[CAI],[DNW],[NW],[APA],[PCA],[AAI],[TSC],[TPC],[PC],[STI],[SATI],
[SIC],[SDNW],[SCA],[FTI],[SECTI],[SECATI],[SECSTX],[SECEA],[SECIPA],[SECAI],[SECCAI],[SECDNW],[SECNW],[SECAPA],[SECPCA],[SECAAI],[SECTSC],[SECTPC],[SECPC],[SECSTI],[SECSATI],
[SECSEC],[SECSDNW],[SECSCA],[SECFTI],[CorrectionFlags],[HighlightFlags],[FAFSAdataVerifyFlags],[DHSMatchFlag],[SecondaryDHSMatchFlag],[DHSVerificationNum],[NSLDSMatchFlag],
[NSLDSPostReasonCode],[FatherSSNMatchFlag],[MotherSSNMatchFlag],[SelServiceMatchFlag],[SelServiceRegFlag],[SSACitizenFlag],[SSNMatchFlag],[VAMatchFlag],
[CommentCodes],[EFedSchoolCodeIndicator],[ETrxIndicatorFlag],[MultiSchoolCodeFlags],[VerificationTrackingFlag],[SelectedForVerification])) AS FaISIR ) dTableFaISIR
INNER JOIN FaISIRStudentMatch (nolock)
On FaISIRStudentMatch.FaISIRMainID = dTableFaISIR.FaISIRMainID
Inner Join FaYear (nolock)
On FaISIRStudentMatch.FaYearID = FaYear.FaYearID
And FAISIRStudentMatch.FaYearID = @YearID
Join SyCampus (nolock)
On ( SyCampus.PellID = FAISIRStudentMatch.PellID Or
SyCampus.AttRfmsPellID = FAISIRStudentMatch.PellID Or
SyCampus.FirstCollegeChoice = FAISIRStudentMatch.PellID )
Where FAISIRStudentMatch.SyStudentID = @SyStudentID
And @SyCampusID = Case @SyCampusID When 0 Then 0 Else SyCampus.SyCampusID End
March 7, 2012 at 4:25 pm
That's one heck of a piece of code!
You've got 250 columns being selected using sub-selects, CTEs, PIVOT and CROSS APPLY in there. Any chance of simplifying it?
How about moving all the sub-selects into CTEs?
March 7, 2012 at 4:45 pm
one piece of it is a catch all query, right?
And @SyCampusID = Case @SyCampusID When 0 Then 0 Else SyCampus.SyCampusID End
Lowell
March 7, 2012 at 4:47 pm
It's vendor code to they'll need to be the ones to fix it, I'm just trying to figure out why it won't cache the query plan.
April 18, 2012 at 11:23 am
George,
I'm working through the same exact issue with the same vendor right now. I have a couple procs, and I think this is one of them. Have you found any solution to this yet? For us it's causing quite a bit of blocking as one execution has to complete before the next one can start compiling.
Let me know if you find anything, and you can always reply to this post if you have any other questions on how we handled issues in this app.
Thanks,
Steve
April 18, 2012 at 12:57 pm
Who is the vendor? Looks like I could sell my skills to them... What a lot of NOLOCKs!
Jared
CE - Microsoft
April 18, 2012 at 1:12 pm
I would say that the optimizer deems it too complex and it probably throws a memory error trying to cache it. This sort of thing can happen with vary large IN clauses (and you have multiples of that)
CTE.Fieldname IN ('SSN','YearIndicator','OriginalNameID','TransactionNumber','LastName','FirstName','MI','Address','City',
'State','Zip','DOB','StudentPhone','StudentDLNumber','StudentDLState','StudentEmail','Citizen','AlienNumber','StudentMaritalStatus',
'StudentMaritalStatusDate','StudentLegState','StudentLegResBef','Male','SelServiceReg','Degree','CollegeGradeLevel','EnrollStatus',
'InterestedInAid','HSGEDReceived','FirstBachDegree','FatherHighGrade','MotherHighGrade','DrugOffense','StudentTaxFiled','StudentTaxFormType',
'StudentElig1040','StudentGross','StudentIncomeTax','StudentExemptions','StudentIncome','SpouseIncome','StudentCash','StudentInvestment',
'StudentBusiness','BornBefore','DegreeBeyond','StudentMarried','Children','LegalDependents','Orphan','ActiveDutyMilitary','Veteran',
'ParentMaritalStatus','ParentMaritalStatusDate','FatherSSN','FatherLastName','FatherFInitial','FatherDOB','MotherSSN','MotherLastName','MotherFirstNameInitial','MotherDOB','ParentNumFamily',
'ParentNumCollege','ParentSSIBenefits','ParentLegState','ParentLegResBef','ParentLegResDate','ParentFoodStamps','ParentFreeLunch','ParentTANFBenefits',
'ParentWICBenefits','ParentTaxFiled','ParentTaxFormType','ParentElig1040','ParentGross','ParentIncomeTax','ParentExemptions','FatherIncome','MotherIncome',
'ParentCash','ParentInvestment','ParentBusiness','StudentNumFamily','StudentNumCollege','StudentSSIBenefits','StudentFoodStamps','StudentFreeLunch',
'StudentTANFBenefits','StudentWICBenefits','FirstCollegeChoice','FirstCollegeHousing','SecondCollegeChoice','SecondCollegeHousing',
'ThirdCollegeChoice','ThirdCollegeHousing','FourthCollegeChoice','FourthCollegeHousing','FifthCollegeChoice','FifthCollegeHousing','SixthCollegeChoice','SixthCollegeHousing',
'DateCompleted','SignedByFlag','PreparerSSN','PreparerEIN','PreparerSignature','ModelOverride','FAAFederalSchoolCode','Model','TransactionSourceTypeCode',
'TransactionRcptDate','AssumptionOverrides1','AssumptionOverrides2','AssumptionOverrides3','AssumptionOverrides4','AssumptionOverrides5','AssumptionOverrides6','ETIDestCode',
'RejectOverrides3','RejectOverrides12','RejectOverrides20','RejectOverridesA','RejectOverridesB','RejectOverridesC','RejectOverridesG','RejectOverridesJ','RejectOverridesK',
'RejectOverridesN','RejectOverridesW','ParentEmail','CurrentSSN','HistoryCorrectionApplied','FAAadjust','ApplicationSourceTypeCode',
'ApplicationRcptDate','AddressOnlyChangeFlag','CPSpushedISIRflag','EFCChangeFlag','SSNChangeFlag','RejectStatusChangeFlag','SARCchangeFlag','VerificationSelChangeFlag',
'ComputeNum','SourceofCorrection','DuplicateSSN','GraduateFlag','PellEligFlag','TransactionProcessedDate','RecordType','RejectCodes','ReprocessedReasonCode',
'SARCFlag','Auto0EFCFlag','SimplifiedNeeds','ParentCalcTaxStatus','StudentCalcTaxStatus','AssumedCitizenship','AssumedStudentMarital','AssumedStudentGross',
'AssumedStudentIncomeTax','AssumedStudentIncome','AssumedSpouseIncome','AssumedStudentAmountWSC','AssumedDOBPrior','AssumedStudentMarried','AssumedChildren',
'AssumedStudentLegDep','AssumedStudentNumFam','AssumedStudentNumColl','AssumedParentMarital','AssumedFSSN','AssumedMSSN','AssumedParentNumFam','AssumedParentNumColl',
'AssumedParentGross','AssumedParentIncomeTax','AssumedFatherIncome','AssumedMotherIncome','AssumedParentAmountWSC','PEFC','SEFC','PEFCType','SEFCType','PAEFC01',
'PAEFC02','PAEFC03','PAEFC04','PAEFC05','PAEFC06','PAEFC07','PAEFC08','PAEFC10','PAEFC11','PAEFC12','SAEFC01','SAEFC02','SAEFC03','SAEFC04','SAEFC05',
'SAEFC06','SAEFC07','SAEFC08','SAEFC10','SAEFC11','SAEFC12','TotalIncome','ATI','STX','EA','IPA','AI','CAI','DNW', 'NW', 'APA','PCA','AAI','TSC','TPC','PC','STI','SATI',
'SIC', 'SDNW','SCA','FTI', 'SECTI', 'SECATI','SECSTX','SECEA', 'SECIPA','SECAI','SECCAI','SECDNW','SECNW','SECAPA','SECPCA','SECAAI','SECTSC','SECTPC','SECPC','SECSTI','SECSATI',
'SECSEC','SECSDNW','SECSCA','SECFTI','CorrectionFlags','HighlightFlags','FAFSAdataVerifyFlags','DHSMatchFlag','SecondaryDHSMatchFlag','DHSVerificationNum','NSLDSMatchFlag',
'NSLDSPostReasonCode','FatherSSNMatchFlag','MotherSSNMatchFlag','SelServiceMatchFlag','SelServiceRegFlag','SSACitizenFlag','SSNMatchFlag','VAMatchFlag',
'CommentCodes','EFedSchoolCodeIndicator','ETrxIndicatorFlag','MultiSchoolCodeFlags','VerificationTrackingFlag','SelectedForVerification'))P
You may be seeing errors in the error log regarding the query processor running out of memory for this query.
I would have the vendor alter this code to move those "IN" lists into a table variable and then join to that table variable. It would greatly simplify the query and memory required for the query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply