October 7, 2011 at 1:03 am
An application our company uses runs this procedure literally thousands of times during the End of Day process. It only takes a few minutes after EOD starts to see hundeds of LOCK_M_U wait-types and keylock hobtid=XXXX...X, and the wait times begin to grow...this leads to many head blockers and appears to slow things down.
While it's obvious the application itself is calling this procedure from some "internal" application process (which we don't have access to), I need to figure out the cause (and hopefully solution).
I've looked at the query plan for this procedure and it shows 2 index seeks going on and that's about it. It looks straightforward to me, yet I don't understand how these keylocks get generated. Could someone help explain this to me?
CREATE PROCEDURE [dbo].[_TEST_]( @acctId int ) AS
SELECT
a.acctId
, a.ATID
, a.rowClassifier
, a.grpClassifier
, a.LAD
, a.LAPD
, a.NAD
, a.StatementDate
, a.parent01ATID
, a.parent01AID
, a.parent02ATID
, a.parent02AID
, a.parent03ATID
, a.parent03AID
, a.parent04ATID
, a.parent04AID
, a.parent05ATID
, a.parent05AID
, a.ccinhparent101ATID
, a.ccinhparent101AID
, a.ccinhparent102ATID
, a.ccinhparent102AID
, a.ccinhparent103ATID
, a.ccinhparent103AID
, a.ccinhparent104ATID
, a.ccinhparent104AID
, a.ccinhparent105ATID
, a.ccinhparent105AID
, a.ccinhparent106ATID
, a.ccinhparent106AID
, a.ccinhparent107ATID
, a.ccinhparent107AID
, a.ccinhparent108ATID
, a.ccinhparent108AID
, a.ccinhparent109ATID
, a.ccinhparent109AID
, a.ccinhparent110ATID
, a.ccinhparent110AID
, a.ccinhparent111ATID
, a.ccinhparent111AID
, a.ccinhparent112ATID
, a.ccinhparent112AID
, a.ccinhparent113ATID
, a.ccinhparent113AID
, a.ccinhparent114ATID
, a.ccinhparent114AID
, a.ccinhparent115ATID
, a.ccinhparent115AID
, a.ccinhparent116AID
, a.ccinhparent116ATID
, a.ccinhparent117AID
, a.ccinhparent117ATID
, a.ccinhparent118AID
, a.ccinhparent118ATID
, a.ccinhparent119AID
, a.ccinhparent119ATID
, a.ccinhparent120AID
, a.ccinhparent120ATID
, a.ccinhparent121AID
, a.ccinhparent121ATID
, a.ccinhparent122AID
, a.ccinhparent122ATID
, a.ccinhparent123AID
, a.ccinhparent123ATID
, a.ccinhparent124AID
, a.ccinhparent124ATID
, a.ccinhparent125AID
, a.ccinhparent125ATID
, a.ccinhparent126AID
, a.ccinhparent126ATID
, a.ccinhparent127AID
, a.ccinhparent127ATID
, a.ccinhparent128AID
, a.ccinhparent128ATID
, a.ccinhparent129AID
, a.ccinhparent129ATID
, a.ccinhparent130AID
, a.ccinhparent130ATID
, a.ccinhparent131AID
, a.ccinhparent131ATID
, a.ccinhparent132AID
, a.ccinhparent132ATID
, a.ccinhparent133AID
, a.ccinhparent133ATID
, a.ccinhparent134AID
, a.ccinhparent134ATID
, a.ccinhparent135AID
, a.ccinhparent135ATID
, a.MPLMerchantNumber
, a.MPLStoreNumber
, a.MPLRegisterNumber
, a.BillingCycle
, a.MPLMerchantDesc
, a.MPLMerchantLevel
, a.InventoryGrpCode
, a.MPLCredLimOTBDisplay
, a.MPLPrimeRateFloor
, a.MPLStmtPrintSeq
, a.MPLStatementForm
, a.FeeType
, a.DiscntFeeTiming
, a.FeeCalcBasis
, a.VolumeType
, a.FeeCalc
, a.AmtPerVolume
, a.AmtPerTran
, a.MRTTranCode
, a.FixedRate1
, a.FixedRate2
, a.FixedRate3
, a.FixedRate4
, a.FixedRate5
, a.TierLmtInd
, a.TierMaxBalance1
, a.TierMaxBalance2
, a.TierMaxBalance3
, a.TierMaxBalance4
, a.MerchFeeTiming
, a.BaseFee
, a.PerAcctFee
, a.PrimeRateKicker
, a.MPLBaseFeeCTD
, a.MPLBaseFeeCC1
, a.MPLBaseFeeDTD
, a.MPLBaseFeeLTD
, a.MPLBaseFeeYTD
, a.MPLBaseFeeWTD
, a.MPLBaseFeeMTD
, a.MPLDiscountCTD
, a.MPLDiscountDTD
, a.MPLDiscountLTD
, a.MPLDiscountYTD
, a.MPLDiscountMTD
, a.MPLDiscountWTD
, a.MPLDiscountCountDTD
, a.MPLDiscountCountCTD
, a.MPLDiscountCountMTD
, a.MPLDiscountCountWTD
, a.MPLDiscountCountYTD
, a.MPLDiscountCountLTD
, a.MPLMerchantFeeCTD
, a.MPLMerchantFeeDTD
, a.MPLMerchantFeeMTD
, a.MPLMerchantFeeLTD
, a.MPLMerchantFeeWTD
, a.MPLMerchantFeeYTD
, a.MPLMerchantFeeCountCTD
, a.MPLMerchantFeeCountDTD
, a.MPLMerchantFeeCountLTD
, a.MPLMerchantFeeCountMTD
, a.MPLMerchantFeeCountWTD
, a.MPLMerchantFeeCountYTD
, a.MPLSettlementAmtCTD
, a.MPLNetVolumeAmtCTD
, a.MPLNetVolumeAmtDTD
, a.MPLNetVolumeAmtMTD
, a.MPLNetVolumeAmtQTD
, a.MPLNetVolumeAmtLTD
, a.MPLNetVolumeAmtYTD
, a.MPLNetVolumeCountCTD
, a.MPLNetVolumeCountDTD
, a.MPLNetVolumeCountMTD
, a.MPLNetVolumeCountQTD
, a.MPLNetVolumeCountLTD
, a.MPLNetVolumeCountYTD
, a.MPLNewReturnCreditAmtCTD
, a.MPLNewReturnCreditAmtDTD
, a.MPLNewReturnCreditAmtLTD
, a.MPLNewReturnCreditAmtYTD
, a.MPLNewReturnCreditCountCTD
, a.MPLNewReturnCreditCountDTD
, a.MPLNewReturnCreditCountLTD
, a.MPLNewReturnCreditCountYTD
, a.MPLSumAllCreditLimitCTD
, a.MPLPastDueAmtCTD
, a.MPLNewSalesAmtCTD
, a.MPLNewSalesAmtDTD
, a.MPLNewSalesAmtWTD
, a.MPLNewSalesAmtMTD
, a.MPLNewSalesAmtQTD
, a.MPLNewSalesAmtLTD
, a.MPLNewSalesAmtYTD
, a.MPLNewSalesCountCTD
, a.MPLNewSalesCountDTD
, a.MPLNewSalesCountMTD
, a.MPLNewSalesCountQTD
, a.MPLNewSalesCountLTD
, a.MPLNewSalesCountYTD
, a.MPLNewSalesCountWTD
, a.MPLNewCreditsAmtCTD
, a.MPLNewCreditsAmtDTD
, a.MPLNewCreditsAmtLTD
, a.MPLNewCreditsAmtYTD
, a.MPLNewCreditsCountCTD
, a.MPLNewCreditsCountDTD
, a.MPLNewCreditsCountLTD
, a.MPLNewCreditsCountYTD
, a.MPLNewReturnsAmtCTD
, a.MPLNewReturnsAmtDTD
, a.MPLNewReturnsAmtMTD
, a.MPLNewReturnsAmtLTD
, a.MPLNewReturnsAmtYTD
, a.MPLNewReturnsCountCTD
, a.MPLNewReturnsCountDTD
, a.MPLNewReturnsCountMTD
, a.MPLNewReturnsCountLTD
, a.MPLNewReturnsCountYTD
, a.MPLParticipationCTD
, a.MPLParticipationDTD
, a.MPLParticipationLTD
, a.MPLParticipationYTD
, a.MPLParticipationWTD
, a.MPLParticipationMTD
, a.MPLSettlementAmtYTD
, a.MPLSettlementAmtLTD
, a.MPLOtherChargesCTD
, a.MPLOtherChargesYTD
, a.MPLOtherChargesLTD
, a.MPLPrimeRateAdjustmentCTD
, a.MPLPrimeRateAdjustmentDTD
, a.MPLPrimeRateAdjustmentLTD
, a.MPLPrimeRateAdjustmentYTD
, a.HoldAcctStatus1
, a.HoldAcctStatus2
, a.HoldAcctStatus3
, a.HoldAcctStatus4
, a.HoldCreditBalances
, a.DebitBalancesUnder
, a.FromAcctNum
, a.ToAcctNum
, a.TempNewReserve
, a.MPLCDRoutine
, a.MPLNbrBegin
, a.MPLNbrEnd
, a.MPLNbrIncrementBy
, a.MPLNbrLastUsed
, a.MPLNbrAutoGenFlag
, a.MPLParamAN1
, a.MPLParamAN2
, a.MPLParamAN3
, a.MPLParamCur1
, a.MPLParamCur2
, a.MPLParamCur3
, a.MPLParamCur4
, a.MPLParamInt1
, a.MPLParamInt2
, a.MPLParamInt3
, a.PrimaryCurrencyCode
, a.SecondaryCurrencyCode
, a.NbrOfMerchantTxns2
, a.MerchantDebitsAmt2
, a.MerchantCreditsAmt2
, a.NbrGenTxns
, a.AmtGenDebits
, a.AmtGenCredits
, a.NbrOfOnUsTxns
, a.AmtOfOnUsDebits
, a.AmtOfOnUsCredits
, a.NbrMerchantNotOnUsTxns
, a.MerchantNotOnUsDebitsAmt
, a.MerchantNotOnUsCreditsAmt
, a.NetworkName
, a.TxnProcessing
, a.AcqAggIntrchgBal
, a.IsPhysical
, a.AcqPhysicalMerchant
, a.AcqPhysicalStore
, a.AcqAuthFeesBnp
, a.AcqAuthFeesCountCTD
, a.deAcqAuthFeesCountDTD
, a.AcqAuthFeesCountLTD
, a.AcqAuthFeesCountMTD
, a.AcqAuthFeesCountYTD
, a.AcqAuthFeesCTD
, a.AcqAuthFeesDTD
, a.AcqAuthFeesLTD
, a.AcqAuthFeesMTD
, a.AcqAuthFeesYTD
, a.AcqEquipFeesBnp
, a.AcqEquipFeesCountCTD
, a.AcqEquipFeesCountDTD
, a.AcqEquipFeesCountLTD
, a.AcqEquipFeesCountMTD
, a.AcqEquipFeesCountYTD
, a.AcqEquipFeesCTD
, a.AcqEquipFeesDTD
, a.AcqEquipFeesLTD
, a.AcqEquipFeesMTD
, a.AcqEquipFeesYTD
, a.AcqStoreLstActivityDt
, a.AcqStoreLastCrDate
, a.AcqStoreLastDebitDate
, a.AcqMiscFeesBnp
, a.AcqMiscFeesCntCTD
, a.AcqMiscFeesCntDTD
, a.AcqMiscFeesCntLTD
, a.AcqMiscFeesCntMTD
, a.AcqMiscFeesCntYTD
, a.AcqMiscFeesCTD
, a.AcqMiscFeesDTD
, a.AcqMiscFeesLTD
, a.AcqMiscFeesMTD
, a.AcqMiscFeesYTD
, a.AcqTranFeeBnp
, a.AcqTranFeesCntCTD
, a.AcqTranFeesCntDTD
, a.AcqTranFeesCntLTD
, a.AcqTranFeesCntMTD
, a.AcqTranFeesCntYTD
, a.AcqTranFeesCTD
, a.AcqTranFeeDTD
, a.AcqTranFeesLTD
, a.AcqTranFeesMTD
, a.AcqTranFeesYTD
, a.AcqStoreLastDebit
, a.AcqStoreLastCr
, a.AmtApprCashAuthRespCTD
, a.AmtApprCashAuthRespDTD
, a.AmtApprCashAuthRespYTD
, a.AmtApprAuthRespCTD
, a.AmtApprAuthRespDTD
, a.AmtApprAuthRespYTD
, a.AmtApprPurAuthRespCTD
, a.AmtApprPurAuthRespDTD
, a.AmtApprPurAuthRespYTD
, a.AmtAuthAttemptsCTD
, a.AmtAuthAttemptsDTD
, a.AmtAuthAttemptsYTD
, a.AmtCashAuthAttemptsCTD
, a.AmtCashAuthAttemptsDTD
, a.AmtCashAuthAttemptsYTD
, a.AmtCashRevRespCTD
, a.AmtCashRevRespDTD
, a.AmtCashRevRespYTD
, a.AmtDeclCashAuthRespCTD
, a.AmtDeclCashAuthRespDTD
, a.AmtDeclCashAuthRespYTD
, a.AmtDeclAuthRespCTD
, a.AmtDeclAuthRespDTD
, a.AmtDeclAuthRespYTD
, a.AmtDeclPurAuthRespCTD
, a.AmtDeclPurAuthRespDTD
, a.AmtDeclPurAuthRespYTD
, a.AmtNotOnUsAuthRespCTD
, a.AmtNotOnUsAuthRespDTD
, a.AmtNotOnUsAuthRespYTD
, a.AmtOnUsAuthRespCTD
, a.AmtOnUsAuthRespDTD
, a.AmtOnUsAuthRespYTD
, a.AmtPurAuthAttemptsCTD
, a.AmtPurAuthAttemptsDTD
, a.AmtPurAuthAttemptsYTD
, a.AmtPurRevRespCTD
, a.AmtPurRevRespDTD
, a.AmtPurRevRespYTD
, a.AmtRevRespCTD
, a.AmtRevRespDTD
, a.AmtRevRespYTD
, a.NumApprCashAuthRespCTD
, a.NumApprCashAuthRespDTD
, a.NumApprCashAuthRespYTD
, a.NumApprPurAuthRespCTD
, a.NumApprPurAuthRespDTD
, a.NumApprPurAuthRespYTD
, a.NumApprAuthRespCTD
, a.NumApprAuthRespDTD
, a.NumApprAuthRespYTD
, a.NumAuthAttemptsCTD
, a.NumAuthAttemptsDTD
, a.NumAuthAttemptsYTD
, a.NumCashAuthAttemptsCTD
, a.NumCashAuthAttemptsDTD
, a.NumCashAuthAttemptsYTD
, a.NumDeclAuthRespCTD
, a.NumDeclAuthRespDTD
, a.NumDeclAuthRespYTD
, a.NumPurAuthAttemptsCTD
, a.NumPurAuthAttemptsDTD
, a.NumPurAuthAttemptsYTD
, a.NumRevRespCTD
, a.NumRevRespDTD
, a.NumRevRespYTD
, a.NumCashRevRespCTD
, a.NumCashRevRespDTD
, a.NumCashRevRespYTD
, a.NumDeclCashAuthRespCTD
, a.NumDeclCashAuthRespDTD
, a.NumDeclCashAuthRespYTD
, a.NumDeclPurAuthRespCTD
, a.NumDeclPurAuthRespDTD
, a.NumDeclPurAuthRespYTD
, a.NumNotOnUsAuthRespCTD
, a.NumNotOnUsAuthRespDTD
, a.NumNotOnUsAuthRespYTD
, a.NumOnUsAuthRespCTD
, a.NumOnUsAuthRespDTD
, a.NumOnUsAuthRespYTD
, a.NumPurRevRespCTD
, a.NumPurRevRespDTD
, a.NumPurRevRespYTD
, a.TotalGallonsApprDTD
, a.TotalGallonsApprMTD
, a.TotalGallonsApprCTD
, a.TotalGallonsApprQTD
, a.TotalGallonsApprYTD
, a.TotalGallonsApprLTD
, a.EmailSendAlertTo
, a.AmtHighestDailyReqEst
, a.NumMultReqForSameAmtEst
, a.NumMultReqOnSameAcctEst
, a.AmtAuthsEstDTD
, a.AmtAuthsEstWTD
, a.AmtAuthsEstMTD
, a.AmtAuthsDTD
, a.AmtAuthsWTD
, a.AmtAuthsMTD
, a.NumAuthsEstDTD
, a.NumAuthsEstWTD
, a.NumAuthsEstMTD
, a.NumAuthsDTD
, a.NumAuthsWTD
, a.NumAuthsMTD
, a.AmtAvgTicketEstDTD
, a.AmtAvgTicketEstWTD
, a.AmtAvgTicketEstMTD
, a.AmtAvgTicketDTD
, a.AmtAvgTicketWTD
, a.AmtAvgTicketMTD
, a.NumApprovedAuthDTD
, a.NumApprovedAuthWTD
, a.NumApprovedAuthMTD
, a.PercentApprAuthEstDTD
, a.PercentApprAuthEstWTD
, a.PercentApprAuthEstMTD
, a.PercentApprAuthDTD
, a.PercentApprAuthWTD
, a.PercentApprAuthMTD
, a.NumNonApprovedAuthDTD
, a.NumNonApprovedAuthWTD
, a.NumNonApprovedAuthMTD
, a.PercentNonApprAuthEstDTD
, a.PercentNonApprAuthEstWTD
, a.PercentNonApprAuthEstMTD
, a.PercentNonApprAuthDTD
, a.PercentNonApprAuthWTD
, a.PercentNonApprAuthMTD
, a.NumSwipedAuthDTD
, a.NumSwipedAuthWTD
, a.NumSwipedAuthMTD
, a.PercentSwipedAuthEstDTD
, a.PercentSwipedAuthEstWTD
, a.PercentSwipedAuthEstMTD
, a.PercentSwipedAuthDTD
, a.PercentSwipedAuthWTD
, a.PercentSwipedAuthMTD
, a.NumKeyedAuthDTD
, a.NumKeyedAuthWTD
, a.NumKeyedAuthMTD
, a.PercentKeyedAuthEstDTD
, a.PercentKeyedAuthEstWTD
, a.PercentKeyedAuthEstMTD
, a.PercentKeyedAuthDTD
, a.PercentKeyedAuthWTD
, a.PercentKeyedAuthMTD
, a.AmtChargebacksEstCTD
, a.NumChargebacksEstCTD
, a.AcqMerVisitation
, a.CardSwipe
, a.MerchantType
, a.MerchantMTCGrp
, a.ReserveAmount
, a.LastReserveDebitAmt
, a.LastReserveCalcDate
, a.LastReserveRefundAmt
, a.LastResRefundDate
, a.ReserveHeldDTD
, a.ReserveHeldWTD
, a.ReserveHeldCTD
, a.ReserveHeldMTD
, a.ReserveHeldYTD
, a.ReserveRefundedDTD
, a.ReserveRefundedWTD
, a.ReserveRefundedCTD
, a.ReserveRefundedMTD
, a.ReserveRefundedYTD
, a.SettlementType
, a.SettlementFrequency
, a.SettlementDelay
, a.SettlementTranCodeDebit
, a.SettlementTranCodeCredit
, a.MBCProcessor
, a.SettlementRouting
, a.SettlementAccount
, a.InstACHActiveDate
, a.MerchantACHActiveDate
, a.origroutingtransit
, a.origination
, a.destroutingtransit
, a.destination
, a.SettlementBalance
, a.ACHFileDescription
, a.MPLInStorePaymentAmtCTD
, a.MPLInStorePaymentAmtDTD
, a.MPLInStorePaymentAmtLTD
, a.MPLInStorePaymentAmtMTD
, a.MPLInStorePaymentAmtWTD
, a.MPLInStorePaymentAmtYTD
, a.MPLInStorePaymentCountCTD
, a.MPLInStorePaymentCountDTD
, a.MPLInStorePaymentCountLTD
, a.MPLInStorePaymentCountMTD
, a.MPLInStorePaymentCountWTD
, a.MPLInStorePaymentCountYTD
, a.IBCreditsAmountCTD
, a.IBCreditsAmountDTD
, a.IBCreditsAmountMTD
, a.IBCreditsAmountWTD
, a.IBCreditsAmountYTD
, a.IBCreditsCountCTD
, a.IBCreditsCountDTD
, a.IBCreditsCountLTD
, a.IBCreditsCountMTD
, a.IBCreditsCountWTD
, a.IBCreditsCountYTD
, a.IBCreditsAmountLTD
, a.IBDebitsAmountCTD
, a.IBDebitsAmountDTD
, a.IBDebitsAmountLTD
, a.IBDebitsAmountMTD
, a.IBDebitsAmountWTD
, a.IBDebitsAmountYTD
, a.IBDebitsCountCTD
, a.IBDebitsCountDTD
, a.IBDebitsCountLTD
, a.IBDebitsCountMTD
, a.IBDebitsCountWTD
, a.IBDebitsCountYTD
, a.MPLTotalPaymentsAmtCTD
, a.MPLTotalPaymentsAmtDTD
, a.MPLTotalPaymentsAmtLTD
, a.MPLTotalPaymentsAmtYTD
, a.IBTotalAmountCTD
, a.IBTotalAmountDTD
, a.IBTotalAmountMTD
, a.IBTotalAmountWTD
, a.IBTotalAmountYTD
, a.IBTotalCountCTD
, a.IBTotalCountDTD
, a.IBTotalCountMTD
, a.IBTotalCountWTD
, a.IBTotalCountYTD
, a.AcqICExpAmtMTD
, a.AcqICExpAmt
, a.AcqIndirExpAmtMTD
, a.AcqIndirExpYTD
, a.AcqNetProfitCountCTD
, a.AcqNetProfitCountDTD
, a.AcqNetProfitCountMTD
, a.AcqNetProfitCountYTD
, a.AcqTotalDirExpAmtMTD
, a.AcqTotalDirExpAmtYTD
, a.AcqTotalExpCountMTD
, a.AcqTotalExpCountYTD
, a.AcqTotIncomeAmtMTD
, a.AcqTotIncomeAmtYTD
, a.AcqTotIndirAmtMTD
, a.AcqTotIndirAmtYTD
, a.MPLChbacksAdjustAmtCTD
, a.MPLChbacksAdjustAmtDTD
, a.MPLChbacksAdjustAmtMTD
, a.MPLChbacksAdjustAmtWTD
, a.MPLChbacksAdjustAmtYTD
, a.MPLChbacksAdjustAmtLTD
, a.MPLChbacksAdjustCountLTD
, a.MPLChbacksAdjustCountYTD
, a.MPLChbacksAdjustCountWTD
, a.MPLChbacksAdjustCountCTD
, a.MPLChbacksAdjustCountMTD
, a.MPLChbacksAdjustCountDTD
, a.AcqDateOpened
, a.AcqDateSuspended
, a.deAcqDateRenewed
, a.AcqDateLastVisit
, a.AcqDateNextVisit
, a.AcqDateLastSettlmnt
, a.AcqDateLastStatemnt
, a.ProduceStatement
, a.StatementLevel
, a.StmtDistribution
, a.StateMessGrpName
, a.AcqRackCode
, a.FPRFuelAmountDTD
, a.FPRFuelCountDTD
, a.FPRFuelAmountCTD
, a.FPRFuelCountCTD
, a.FPRFuelAmountMTD
, a.FPRFuelCountMTD
, a.FPRFuelAmountYTD
, a.FPRFuelCountYTD
, a.FPRFuelAmountLTD
, a.FPRFuelCountLTD
, a.ACQMPMonetaryBeginDate
, a.ACQMPMonetaryEndDate
, a.ACQMPNonMonetaryCommissions
, a.PCGroupFuel
, a.PCGroupMaintenence
, a.PCGroupAdditives
, a.PCGroupDisburse
, a.PCGroupCash
, a.PCGroupMisc
, a.SystemStatus
, a.PendingSettlement
, a.DebitSettleCondDate
, a.DelayType
, a.VariableSettlement
, a.PreviousSystemStatus
, a.AutomaticDebitSettlement
, a.MPLNewSalesAmtSM
, a.MPLNewSalesCountSM
, a.MPLParticipationSM
, a.MPLDiscountSM
, a.MPLNewReturnsAmtSM
, a.MPLNewReturnsCountSM
, a.MPLMerchantFeeSM
, a.AcqAuthFeesSM
, a.AcqTranFeesSM
, a.AcqMiscFeesSM
, a.AcqEquipmentFeesSM
, a.MPLInStorePaymentAmtSM
, a.MPLInStorePaymentCountSM
, a.MPLChbacksAdjustAmtSM
, a.ReserveHeldSM
, a.ReserveRefundedSM
, a.MPLChbacksAdjustCountSM
, a.FeesGroupId
, a.GenMerchantNumberID
, a.AggregateFeeTiers
, a.AggProgramFeeTiers
, a.SalesTaxIncludeExclude
, a.LaidInCostID
, a.AmountCashAuthReqMTD
, a.NumberCashAuthReqMTD
, a.AmountPurAuthReqMTD
, a.NumberPurAuthReqMTD
, a.TotAmtCashPurAuthReqMTD
, a.TotNoCashPurAuthReqMTD
, a.amtappcaadvauthsMTD
, a.noappcaadvauthsMTD
, a.amtapppseauthsMTD
, a.noapppseauthsMTD
, a.totamtapcaauthsMTD
, a.totnoapppcaauthsMTD
, a.amtcaadvrevMTD
, a.Nocaadvrev_MTD
, a.amtPurRev_MTD
, a.NoPurRev_MTD
, a.AmtPurandCashreturns_MTD
, a.NoPurandCashrev_MTD
, a.amtdeccaadvauthsMTD
, a.nodeccaadvauthsMTD
, a.amtdeclpseauthsMTD
, a.nodeclpseauthsMTD
, a.totamtdpcaauthsMTD
, a.totnodpcaauthsMTD
, a.MerchantName
, a.StoreName
, a.RegisterName
, a.MPLDisputeAmt
, a.MPLDisputeCnt
, a.TireDispatchMerchant
, a.DispatchSettlementFee
, a.TireDispatchSettleTranCode
, a.TotalDeposit
, a.MPLSettlementAmtDTD
, a.MPLSettlementAmtMTD
, a.IBDirBillAmountSM
, a.IBDirBillAmountDTD
, a.IBDirBillAmountWTD
, a.IBDirBillAmountMTD
, a.IBDirBillAmountCTD
, a.IBDirBillAmountYTD
, a.IBDirBillAmountLTD
, a.TireUnitCost
, a.SettleDispatchFeeTo
, a.OccurrenceFeeTranCode
, a.tpyNAD
, a.tpyLAD
, a.param_sig
, a.param_state
, a.param_lcd
, a.param_fvt
, a._paramLVT
, a.accessID
, a.RLSKey
, a.IncludeVoiceAuths
, a.IncludeReversals
, a.SettlementStopDate
, a.Activated
, a.SettLvlChangedDate
, b.AddCreditBal
, b.AddNoFinChgAccts
, b.AddPastDue90
, b.PastDueAmtBal
, b.AddUnMailed
, b.PctNewBalAdd
, b.IncludeAcctStatus1
, b.IncludeAcctStatus2
, b.IncludeAcctStatus3
, b.IncludeAcctStatus4
, b.IncludeAcctStatus5
, b.IBDiscountCC1
, b.IBDiscountCC2
, b.IBDiscountCC3
, b.IBDiscountDD1
, b.IBDiscountDD2
, b.IBDiscountDD3
, b.IBDiscountYY1
, b.IBDiscountYY2
, b.IBDiscountYY3
, b.MPLMerchantFeeCC1
, b.IBNetVolumeAmtCC1
, b.IBNetVolumeAmtCC2
, b.IBNetVolumeAmtCC3
, b.IBNetVolumeAmtDD1
, b.IBNetVolumeAmtDD2
, b.IBNetVolumeAmtDD3
, b.IBNetVolumeAmtYY1
, b.IBNetVolumeAmtYY2
, b.IBNetVolumeAmtYY3
, b.IBNetVolumeCountCC1
, b.IBNetVolumeCountCC2
, b.IBNetVolumeCountCC3
, b.IBNetVolumeCountDD1
, b.IBNetVolumeCountDD2
, b.IBNetVolumeCountDD3
, b.IBNetVolumeCountYY1
, b.IBNetVolumeCountYY2
, b.IBNetVolumeCountYY3
, b.MPLPastDueAmtCC1
, b.MPLPastDueAmtCC2
, b.MPLPastDueAmtCC3
, b.MPLPastDueAmtCC4
, b.MPLPastDueAmtCC5
, b.MPLPastDueAmtCC6
, b.MPLPastDueAmtCC7
, b.MPLTotalPastDueAmt
, b.MPLCurrentDueAmt
, b.MPLTotalDueAmt
, b.MPLNewSalesAmtCC1
, b.MPLNewSalesAmtCC2
, b.MPLNewSalesAmtCC3
, b.MPLNewSalesAmtDD1
, b.MPLNewSalesAmtDD2
, b.MPLNewSalesAmtDD3
, b.MPLNewSalesAmtDD4
, b.MPLNewSalesAmtDD5
, b.MPLNewSalesAmtDD6
, b.MPLNewSalesAmtWW1
, b.MPLNewSalesAmtWW2
, b.MPLNewSalesAmtWW3
, b.MPLNewSalesAmtMM1
, b.MPLNewSalesAmtMM2
, b.MPLNewSalesAmtMM3
, b.MPLNewSalesAmtYY1
, b.MPLNewSalesAmtYY2
, b.MPLNewSalesCountCC1
, b.MPLNewSalesCountCC2
, b.MPLNewSalesCountCC3
, b.MPLNewSalesCountDD1
, b.MPLNewSalesCountDD2
, b.MPLNewSalesCountDD3
, b.MPLNewSalesCountDD4
, b.MPLNewSalesCountDD5
, b.MPLNewSalesCountDD6
, b.MPLNewSalesCountMM1
, b.MPLNewSalesCountMM2
, b.MPLNewSalesCountMM3
, b.MPLNewSalesCountYY1
, b.MPLNewSalesCountYY2
, b.MPLNewSalesCountWW1
, b.MPLNewSalesCountWW2
, b.MPLNewSalesCountWW3
, b.IBPParticipationCC1
, b.IBPParticipationCC2
, b.IBPParticipationCC3
, b.IBPParticipationDD1
, b.deIBPParticipationDD2
, b.deIBPParticipationDD3
, b.IBPParticipationYY1
, b.IBPParticipationYY2
, b.IBPParticipationYY3
, b.MPLPastDue
, b.MPLSettlementAmtCC1
, b.MPLOtherChargesCC1
, b.MPLPrimeRateAdjustmentCC1
, b.AmtAuthsDD1
, b.AmtAuthsWW1
, b.AmtAuthsMM1
, b.AmtAuthsMM2
, b.AmtAuthsMM3
, b.AmtAuthsMM4
, b.AmtAuthsMM5
, b.AmtAuthsMM6
, b.AmtAuthsMM7
, b.AmtAuthsMM8
, b.AmtAuthsMM9
, b.AmtAuthsMM10
, b.AmtAuthsMM11
, b.AmtAuthsMM12
, b.NumAuthsDD1
, b.NumAuthsWW1
, b.NumAuthsMM1
, b.NumAuthsMM2
, b.NumAuthsMM3
, b.NumAuthsMM4
, b.NumAuthsMM5
, b.NumAuthsMM6
, b.NumAuthsMM7
, b.NumAuthsMM8
, b.NumAuthsMM9
, b.NumAuthsMM10
, b.NumAuthsMM11
, b.NumAuthsMM12
, b.AmtAvgTicketDD1
, b.AmtAvgTicketWW1
, b.AmtAvgTicketMM1
, b.AmtAvgTicketMM2
, b.AmtAvgTicketMM3
, b.AmtAvgTicketMM4
, b.AmtAvgTicketMM5
, b.AmtAvgTicketMM6
, b.AmtAvgTicketMM7
, b.AmtAvgTicketMM8
, b.AmtAvgTicketMM9
, b.AmtAvgTicketMM10
, b.AmtAvgTicketMM11
, b.AmtAvgTicketMM12
, b.PercentApprAuthDD1
, b.PercentApprAuthWW1
, b.PercentApprAuthMM1
, b.PercentApprAuthMM2
, b.PercentApprAuthMM3
, b.PercentApprAuthMM4
, b.PercentApprAuthMM5
, b.PercentApprAuthMM6
, b.PercentApprAuthMM7
, b.PercentApprAuthMM8
, b.PercentApprAuthMM9
, b.PercentApprAuthMM10
, b.PercentApprAuthMM11
, b.PercentApprAuthMM12
, b.PercentNonApprAuthDD1
, b.PercentNonApprAuthWW1
, b.PercentNonApprAuthMM1
, b.PercentNonApprAuthMM2
, b.PercentNonApprAuthMM3
, b.PercentNonApprAuthMM4
, b.PercentNonApprAuthMM5
, b.PercentNonApprAuthMM6
, b.PercentNonApprAuthMM7
, b.PercentNonApprAuthMM8
, b.PercentNonApprAuthMM9
, b.PercentNonApprAuthMM10
, b.PercentNonApprAuthMM11
, b.PercentNonApprAuthMM12
, b.PercentSwipedAuthDD1
, b.PercentSwipedAuthWW1
, b.PercentSwipedAuthMM1
, b.PercentSwipedAuthMM2
, b.PercentSwipedAuthMM3
, b.PercentSwipedAuthMM4
, b.PercentSwipedAuthMM5
, b.PercentSwipedAuthMM6
, b.PercentSwipedAuthMM7
, b.PercentSwipedAuthMM8
, b.PercentSwipedAuthMM9
, b.PercentSwipedAuthMM10
, b.PercentSwipedAuthMM11
, b.PercentSwipedAuthMM12
, b.PercentKeyedAuthDD1
, b.PercentKeyedAuthWW1
, b.PercentKeyedAuthMM1
, b.PercentKeyedAuthMM2
, b.PercentKeyedAuthMM3
, b.PercentKeyedAuthMM4
, b.PercentKeyedAuthMM5
, b.PercentKeyedAuthMM6
, b.PercentKeyedAuthMM7
, b.PercentKeyedAuthMM8
, b.PercentKeyedAuthMM9
, b.PercentKeyedAuthMM10
, b.PercentKeyedAuthMM11
, b.PercentKeyedAuthMM12
, b.AcqStmtInsert1
, b.AcqStmtInsert2
, b.AcqStmtInsert3
, b.AcqStmtInsert4
, b.AcqStmtInsert5
, b.AcqStmtInsert6
, b.ReserveHeldDD1
, b.ReserveHeldDD2
, b.ReserveHeldDD3
, b.ReserveHeldDD4
, b.ReserveHeldDD5
, b.ReserveHeldDD6
, b.ReserveHeldWW1
, b.ReserveHeldWW2
, b.ReserveHeldWW3
, b.ReserveHeldCC1
, b.ReserveHeldCC2
, b.ReserveHeldCC3
, b.ReserveHeldMM1
, b.ReserveHeldMM2
, b.ReserveHeldMM3
, b.ReserveHeldYY1
, b.ReserveHeldYY2
, b.ReserveRefundedDD1
, b.ReserveRefundedDD2
, b.ReserveRefundedDD3
, b.ReserveRefundedDD4
, b.ReserveRefundedDD5
, b.ReserveRefundedDD6
, b.ReserveRefundedWW1
, b.ReserveRefundedWW2
, b.ReserveRefundedWW3
, b.ReserveRefundedCC1
, b.ReserveRefundedCC2
, b.ReserveRefundedCC3
, b.ReserveRefundedMM1
, b.ReserveRefundedMM2
, b.ReserveRefundedMM3
, b.ReserveRefundedYY1
, b.ReserveRefundedYY2
, b.IBCreditsAmountCC1
, b.IBCreditsAmountCC2
, b.IBCreditsAmountCC3
, b.IBCreditsAmountDD1
, b.IBCreditsAmountDD2
, b.IBCreditsAmountDD3
, b.IBCreditsAmountDD4
, b.IBCreditsAmountDD5
, b.IBCreditsAmountDD6
, b.IBCreditsAmountDD7
, b.IBCreditsAmountMM1
, b.IBCreditsAmountMM2
, b.IBCreditsAmountMM3
, b.IBCreditsAmountWW1
, b.IBCreditsAmountWW2
, b.IBCreditsAmountWW3
, b.IBCreditsAmountWW4
, b.IBCreditsAmountYY1
, b.IBCreditsAmountYY2
, b.IBCreditsAmountYY3
, b.IBCreditsCountCC1
, b.IBCreditsCountCC2
, b.IBCreditsCountCC3
, b.IBCreditsCountDD1
, b.IBCreditsCountDD2
, b.IBCreditsCountDD3
, b.IBCreditsCountDD4
, b.IBCreditsCountDD5
, b.IBCreditsCountDD6
, b.IBCreditsCountDD7
, b.IBCreditsCountMM1
, b.IBCreditsCountMM2
, b.IBCreditsCountMM3
, b.IBCreditsCountWW1
, b.IBCreditsCountWW2
, b.IBCreditsCountWW3
, b.IBCreditsCountWW4
, b.IBCreditsCountYY1
, b.IBCreditsCountYY2
, b.IBCreditsCountYY3
, b.IBDebitsAmountCC1
, b.IBDebitsAmountCC2
, b.IBDebitsAmountCC3
, b.IBDebitsAmountDD1
, b.IBDebitsAmountDD2
, b.IBDebitsAmountDD3
, b.IBDebitsAmountDD4
, b.IBDebitsAmountDD5
, b.IBDebitsAmountDD6
, b.IBDebitsAmountDD7
, b.IBDebitsAmountMM1
, b.IBDebitsAmountMM2
, b.IBDebitsAmountWW1
, b.IBDebitsAmountWW2
, b.IBDebitsAmountWW3
, b.IBDebitsAmountWW4
, b.IBDebitsAmountYY1
, b.IBDebitsAmountYY2
, b.IBDebitsAmountYY3
, b.IBDebitsCountCC1
, b.IBDebitsCountCC2
, b.IBDebitsCountCC3
, b.IBDebitsCountDD1
, b.IBDebitsCountDD2
, b.IBDebitsCountDD3
, b.IBDebitsCountDD4
, b.IBDebitsCountDD5
, b.IBDebitsCountDD6
, b.IBDebitsCountDD7
, b.IBDebitsCountMM1
, b.IBDebitsCountMM2
, b.IBDebitsCountMM3
, b.IBDebitsCountWW1
, b.IBDebitsCountWW2
, b.IBDebitsCountWW3
, b.IBDebitsCountWW4
, b.IBDebitsCountYY1
, b.IBDebitsCountYY2
, b.IBDebitsCountYY3
, b.IBTotalAmountCC1
, b.IBTotalAmountCC2
, b.IBTotalAmountCC3
, b.IBTotalAmountDD1
, b.IBTotalAmountDD2
, b.IBTotalAmountDD3
, b.IBTotalAmountDD4
, b.IBTotalAmountDD5
, b.IBTotalAmountDD6
, b.IBTotalAmountDD7
, b.IBTotalAmountMM1
, b.IBTotalAmountMM2
, b.IBTotalAmountMM3
, b.IBTotalAmountWW1
, b.IBTotalAmountWW2
, b.IBTotalAmountWW3
, b.IBTotalAmountWW4
, b.IBTotalAmountYY1
, b.IBTotalAmountYY2
, b.IBTotalAmountYY3
, b.IBTotalCountCC1
, b.IBTotalCountCC2
, b.IBTotalCountCC3
, b.IBTotalCountDD1
, b.IBTotalCountDD2
, b.IBTotalCountDD3
, b.IBTotalCountDD4
, b.IBTotalCountDD5
, b.IBTotalCountDD6
, b.IBTotalCountDD7
, b.IBTotalCountMM1
, b.IBTotalCountMM2
, b.IBTotalCountMM3
, b.IBTotalCountWW1
, b.IBTotalCountWW2
, b.IBTotalCountWW3
, b.IBTotalCountWW4
, b.IBTotalCountYY1
, b.IBTotalCountYY2
, b.IBTotalCountYY3
, b.MPLChbacksAdjustAmtCC1
, b.MPLChbacksAdjustAmtCC2
, b.MPLChbacksAdjustAmtCC3
, b.MPLChbacksAdjustAmtDD1
, b.MPLChbacksAdjustAmtDD2
, b.MPLChbacksAdjustAmtDD3
, b.MPLChbacksAdjustAmtDD4
, b.MPLChbacksAdjustAmtDD5
, b.MPLChbacksAdjustAmtDD6
, b.MPLChbacksAdjustAmtMM1
, b.MPLChbacksAdjustAmtMM2
, b.MPLChbacksAdjustAmtMM3
, b.MPLChbacksAdjustAmtWW1
, b.MPLChbacksAdjustAmtWW2
, b.MPLChbacksAdjustAmtWW3
, b.MPLChbacksAdjustAmtYY1
, b.MPLChbacksAdjustAmtYY2
, b.MPLChbacksAdjustCountYY1
, b.MPLChbacksAdjustCountYY2
, b.MPLChbacksAdjustCountWW1
, b.MPLChbacksAdjustCountWW2
, b.MPLChbacksAdjustCountWW3
, b.MPLChbacksAdjustCountCC1
, b.MPLChbacksAdjustCountCC2
, b.MPLChbacksAdjustCountCC3
, b.MPLChbacksAdjustCountMM1
, b.MPLChbacksAdjustCountMM2
, b.MPLChbacksAdjustCountMM3
, b.MPLChbacksAdjustCountDD1
, b.MPLChbacksAdjustCountDD2
, b.MPLChbacksAdjustCountDD3
, b.MPLChbacksAdjustCountDD4
, b.MPLChbacksAdjustCountDD5
, b.MPLChbacksAdjustCountDD6
, b.FPRFuelAmountCC1
, b.FPRFuelCountCC1
, b.FPRFuelAmountCC2
, b.FPRFuelCountCC2
, b.LoyaltyPrograms1
, b.LoyaltyPrograms2
, b.LoyaltyPrograms3
, b.LoyaltyPrograms4
, b.LoyaltyPrograms5
, b.RACountDTD
, b.RACountDD1
, b.RACountDD2
, b.RACountDD3
, b.RACountCTD
, b.RACountCC1
, b.RACountCC2
, b.RACountCC3
, b.RACountMTD
, b.RACountYTD
, b.RACountYY1
, b.RACountYY2
, b.RACountLTD
, b.RAAmountDTD
, b.RAAmountDD1
, b.RAAmountDD2
, b.RAAmountDD3
, b.RAAmountCTD
, b.RAAmountCC1
, b.RAAmountCC2
, b.RAAmountCC3
, b.RAAmountMTD
, b.RAAmountYTD
, b.RAAmountYY1
, b.RAAmountYY2
, b.RAAmountLTD
, b.RARebateAmountDD1
, b.RARebateAmountDD2
, b.RARebateAmountDD3
, b.RARebateAmountCTD
, b.RARebateAmountCC1
, b.RARebateAmountCC2
, b.RARebateAmountCC3
, b.RARebateAmountMTD
, b.RARebateAmountYTD
, b.RARebateAmountYY1
, b.RARebateAmountYY2
, b.RARebateAmountLTD
, b.RAPaymentDTD
, b.RAPaymentDD1
, b.RAPaymentDD2
, b.RAPaymentDD3
, b.RAPaymentCTD
, b.RAPaymentCC1
, b.RAPaymentCC2
, b.RAPaymentCC3
, b.RAPaymentMTD
, b.RAPaymentYTD
, b.RAPaymentYY1
, b.RAPaymentYY2
, b.RAPaymentLTD
, b.RARebateAmountDTD
, b.IBGallonVolumeCTD
, b.IBGallonVolumeDTD
, b.IBGallonVolumeMTD
, b.IBGallonVolumeWTD
, b.IBGallonVolumeYTD
, b.IBGallonVolumeLTD
, b.Unattendent
, b.ApprovalCodeLength
, b.IsCached
FROM Merchant_Primary a WITH(UPDLOCK)
LEFT OUTER JOIN Merchant_Secondary b WITH(UPDLOCK) ON
a.acctid = b.acctid
WHERE a.acctId = @acctId
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 7, 2011 at 1:16 am
First question. Why are you forcing update locks in a select?
If that's running a few thousand time, there will be at least 2 key locks per query (one for each row), so a few thousand executions = a few thousand locks. They should be short-lived, if that is being run alone. With no transaction and no other statements, SQL will take, read and release the locks very fast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2011 at 1:04 pm
Gail, the update locks are set by the developers of the application. I've ask them the same question and their reponse was along the lines that the application was calling the procedure and need to lock the record while an external process "did something" with it.
Strange voo-doo I tell ya...
Unfortunately it isn't the only thing running, there are many other application workflows running at the same time...often hitting the same tables, some using NOLOCK (yes i know...) and others that have no hints at all.
Would there be any benefit to using ROWLOCK over the update lock? Or going further, disabling lock escalation altogether on the table, and using ROWLOCK? Or is there really no other way to skin this cat other than coming to a conclusion that it's an issue in the application code?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 8, 2011 at 8:50 pm
MyDoggieJessie (10/7/2011)
Would there be any benefit to using ROWLOCK over the update lock?
No. You already have rowlocks.
Or going further, disabling lock escalation altogether on the table, and using ROWLOCK?
This is not lock escalation.
As far as I can see, that procedure's as optimal as it can be (other than the use of updlock which, without there being a transaction, is just reducing concurrency)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2011 at 10:59 am
It sounds like the sproc is part of a RBAR process (a loop where a few thousand acctId values are "collected" - e.g. using a c.u.r.s.o.r. *cough*).
From my point of view the sproc itself is not the problem. The process calling it is a much better candidate.
October 9, 2011 at 1:17 pm
Yeah that's what we arrived at as well...was just hoping for magic... 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 9, 2011 at 11:13 pm
Sorry, no magic to make bad code good. Just have to get stuck in and fix it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply