100% CPU Utilization! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

100% CPU Utilization!

I’m running SQL 2005 Enterprise x64 on a HP with AMD Opteron processors. Its been running fine for several months but as the usage went up I began to get deadlock errors. I fixed that by wrapping my stored procedures in transactions. I didn’t do that initially because most procedures are just one simple SQL statement. Doing that eliminated the deadlock issues but several days later the transaction log growth reached it limits and the server stopped accepting requests (100% CPU). I truncated the log and it seemed to work fine (that was on Tuesday) since then on the same issue has happened (100% CPU) but without the log problem on five or six occassions. I ran performance monitor while it was peaked and the the system was 99+% of the requests were cached and it was only handling on average 35 transactions per second. It seems to be failing on a simple insert. I can post the stored procedure code for the insert. I’m not a SQL expert by any means may background is Foxpro. Thanks. Don.
Hi, Would you mind to give some information of your database server (RAID information, RAM).
Would you also mind to post your database information
1- size
2- table you are trying to access via store procedure.
3- no of records in table.
4- you can post sp code, it will be a good idea to view sp. regards,
Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
I appreciate your response. Bear with me as I’m new to SQL Server. As far as RAID we are using RAID 5 with 4GB of RAM. Below is the directory output, I’m not certain what you were looking for with regards to size. AtlasDB is the primary database we are using. The biggest table with regards to number of columns is ‘PolicyAttibutes’ it only has about 115,000 rows. The biggest table with regards to record counts has around 2,000,000 records but only about 4 or 5 columns. Further below is the insert statement that usually fails as the server begins to fail ie. 100% CPU. You can see we first tried a try/catch to prevent deadlocks but had better ‘success’ with the begin/commit. Thanks again. Don. Volume in drive E is Data
Volume Serial Number is 4E7B-0D7F Directory of E:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA 07/23/2006 08:20 PM <DIR> .
07/23/2006 08:20 PM <DIR> ..
07/23/2006 12:21 PM 1,859,584,000 AtlasDB.mdf
07/22/2006 10:25 PM 14,389,346,304 AtlasDB_log.ldf
04/14/2006 04:28 AM 2,883,584 distmdl.ldf
04/14/2006 04:28 AM 5,242,880 distmdl.mdf
07/21/2006 05:48 PM 517 E52FDC3C-174B-4693-B0E2-AE16AAEA5C67.cer
07/22/2006 10:25 PM 4,194,304 master.mdf
07/22/2006 10:25 PM 1,310,720 mastlog.ldf
07/22/2006 10:25 PM 1,245,184 model.mdf
07/22/2006 10:25 PM 524,288 modellog.ldf
07/22/2006 10:25 PM 318,308,352 msdbdata.mdf
07/22/2006 10:25 PM 67,698,688 msdblog.ldf
04/14/2006 04:17 AM 524,288 mssqlsystemresource.ldf
04/14/2006 04:17 AM 39,845,888 mssqlsystemresource.mdf
07/22/2006 10:25 PM 100,859,904 ReportServer.mdf
07/22/2006 10:25 PM 63,111,168 ReportServerTempDB.mdf
07/22/2006 10:25 PM 24,969,216 ReportServerTempDB_log.LDF
07/22/2006 10:25 PM 3,211,264 ReportServer_log.LDF
07/22/2006 10:28 PM 62,914,560 tempdb.mdf
07/22/2006 10:33 PM 1,048,576 templog.ldf
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertNewPolicy] (
@CallersUserGUID uniqueidentifier,
@PolicyID uniqueidentifier OUTPUT,
@PolicyEffectiveDate datetime,
@PolicyExpirationDate datetime,
@PolicyFormTypeTextChoice nvarchar(10),
@PolicyLocationStreet1 nvarchar(100),
@PolicyLocationStreet2 nvarchar(100),
@PolicyLocationCity nvarchar(100),
@PolicyLocationState nvarchar(10),
@PolicyLocationZip nvarchar(20),
@PolicyLocationTerritoryCode nvarchar(20),
@PolicyCounty nvarchar(20),
@CoverageAAmount money,
@CoverageBAmount money,
@CoverageCAmount money,
@CoverageDAmount money,
@CoverageEAmount money,
@CoverageFAmount money,
@CoverageLAmount money,
@CoverageMAmount money,
@PhyRiskProtectionClassTextChoice nvarchar(10),
@PhyRiskBCEGCode smallint,
@PremiumProtectiveDiscountsCentralBAlarmYN bit,
@PremiumProtectiveDiscountsCentralFAlarmYN bit,
@PremiumAutomaticSprinklersChoice nvarchar(10),
@PhyRiskConstructionTypeTextChoice nvarchar(50),
@PhyRiskPropertyTypeTextChoice nvarchar(50),
@PhyRiskNumberOfFamilies int,
@PhyRiskPropertyXWindYN bit,
@DeductiblesAOPAmount money,
@DeductiblesHurricane money,
@OptionalCovPersonalPropRCYN bit,
@OptionalCovIncidentalOccReqAmount money,
@OptionalCovIncidentalOccReqLiabilityYN bit,
@OptionalCovIncidentalOccReqRentedOthersAmount money,
@OptionalDPOtherStructuresDesc nvarchar(60),
@OptionalDPImprovementsAmountmoney,
@OptionCovDPNamedInsuredItemPLB bit,
@OptionalCovSectionILimitsTextChoiceAmount nvarchar(50),
@OptionalCovLossAssessCovIncTextChoice nvarchar(10),
@OptionalDPCondoAmount money,
@OptionalCovHO3LawOrdinanceYN bit,
@OptionalCovHO8EndorsementsACV2RCLossYN bit,
@OptionalCovHO6SpecialCovAYN bit,
@OptionalCovHO6RentedToOthersYN bit,
@OptionalCovDPExtendedYN bit,
@OptionalCovDPVMMYN bit,
@OptionalCovOnPremiseTheftAmount money,
@OptionalCovOffPremiseTheftYN bit,
@PremiumMaintainedInsurancePastYearYN bit,
@PremiumMaintainedInsuranceRequiredYN bit,
@PhyRiskYearBuiltTextChoice nvarchar(10),
@PhyRiskDwellingUpdatedYN bit,
@PhyRiskOccupancyLengthTextChoice nvarchar(20),
@PhyRiskPropertyInWindPoolYN bit,
@OptionalCovIncidentalOccReqText nvarchar(10),
@OptionalCovIncidentalOccReqLoc nvarchar(20),
@OptionalCovIncidentalOccReqEmp nvarchar(10),
@OptionalCovIncidentalOccReqOthers nvarchar(60),
@PhyRiskElectricalRenDate nvarchar(10),
@PhyRiskElectricRenExtentTextChoice nvarchar(10),
@PhyRiskHeatingRenDate nvarchar(10),
@PhyRiskHeatingRenExtentTextChoice nvarchar(10),
@PhyRiskRoofRenDate nvarchar(10),
@PhyRiskRoofRenExtentTextChoice nvarchar(10),
@PhyRiskPlumbingRenDate nvarchar(10),
@PhyRiskPlumbingRenExtentTextChoice nvarchar(10),
@PhyRiskIsBuilderRiskYN bit,
@PhyRiskBuildersRiskEstCompletionDate datetime,
@PhyRiskUsageTypeTextChoice nvarchar(10),
@PhyRiskOccupancyTypeTextChoice nvarchar(10),
@PhyRiskDistanceToHydrantFeetChoice int,
@PhyRiskDistanceToFireStationMilesChoice int,
@PhyRiskSecuredByTextChoice nvarchar(50),
@PhyRiskInsideCityLimitsYN bit,
@PhyRiskRespondingFireDeptTextChoice nvarchar(50),
@PhyRiskNumUnitsInFirDiv int,
@PhyRiskMunicipalityCodeTextChoice nvarchar(50),
@PhyRiskMonthUnoccJanYN bit,
@PhyRiskMonthUnoccFebYN bit,
@PhyRiskMonthUnoccMarYN bit,
@PhyRiskMonthUnoccAprYN bit,
@PhyRiskMonthUnoccMayYN bit,
@PhyRiskMonthUnoccJuneYN bit,
@PhyRiskMonthUnoccJulyYN bit,
@PhyRiskMonthUnoccAugYN bit,
@PhyRiskMonthUnoccSeptYN bit,
@PhyRiskMonthUnoccOctYN bit,
@PhyRiskMonthUnoccNovYN bit,
@PhyRiskMonthUnoccDecYN bit,
@PhyRiskUnitFloorLocIntChoice int,
@PhyRiskNumOfApartments int,
@PhyRiskSquareFootage int,
@PhyRiskNumStoriesInBuilding int,
@PhyRiskYearCertOccIssued nvarchar(10),
@PhyRiskRoofShapeTextChoice nvarchar(10),
@PhyRiskRoofTypeTextChoice nvarchar(10),
@PhyRiskPrimaryHeatSourceTextChoice nvarchar(10),
@PhyRiskPrimaryHeatSourceProInstalledYN bit,
@UWQSinkHoleYN bit,
@UWQSinkHoleExplain nvarchar(255),
@UWQHomeCondemnedYN bit,
@UWQHomeCondemnedExplain nvarchar(255),
@UWQHomeDamagedYN bit,
@UWQHomeDamageCompletionDate datetime,
@UWQHomeDamageRepairedYN bit,
@UWQHasSwimmingPoolYN bit,
@UWQHasApprovedPoolFenceYN bit,
@UWQFenceHeight int,
@UWQHasDiveBoardYN bit,
@UWQHasTrampolineYN bit,
@UWQPostHurricaneInspectionYN bit,
@UWQPostHurricaneInspectionDate smalldatetime,
@UWQPostHurricaneInspectionTime smalldatetime,
@UWQPropOverWaterYN bit,
@UWQPropOverWaterExplain nvarchar(255),
@UWQPropOverSandYN bit,
@UWQPropOverSandExplain nvarchar(255),
@ApplicantQuestion1YN bit,
@ApplicantQuestion2YN bit,
@ApplicantQuestion3YN bit,
@ApplicantQuestion4YN bit,
@ApplicantQuestion5YN bit,
@ApplicantQuestion6YN bit,
@ApplicantQuestion7YN bit,
@ApplicantQuestion8YN bit,
@ApplicantQuestion9YN bit,
@ApplicantQuestion10YN bit,
@ApplicantQuestion10Explain nvarchar(255),
@ApplicantQuestion11YN bit,
@ApplicantQuestion11Explain nvarchar(255),
@UWQIsHomeUnoccupiedAtAnyTimeYN bit,
@UWQHomeCheckedText nvarchar(20),
@UWQNameHomeCheckPerson nvarchar(50),
@UWQNameHomeCheckPersonPhone nvarchar(20),
@UWQNeighborCanSeeHomeYN bit,
@UWQBusinessOnPremisesYN bit,
@PriorCoverageInsurerName nvarchar(60),
@PriorCoverageInsurerPolicy nvarchar(30),
@PriorCoveragePolicyExpires datetime,
@RVReplacementValue money,
@RVMarketValue money,
@RVDateOfPurchase datetime,
@RVPurchasePrice money,
@PhyRiskCopperPipesYN bit,
@PhyRiskAgeOfHotWaterHeaterYears int,
@PhyRiskBinderWashingMachineHosesReplacedPast5YearsYN bit,
@PhyRiskKnobTubeWiresYN bit,
@PhyRiskElectricFusesYN bit,
@PhyRisk100AMPPlusServiceYN bit,
@PhyRiskPlantoAddPortableHeatingYN bit,
@PhyRiskPBPipesInPropertyYN bit,
@PhyRiskWPDFactor varchar(10),
@PhyRiskFoundationType varchar(50),
@FloodHomeInFloodAreaYN bit,
@FloodInsurer nvarchar(30),
@FloodPolicyNo nvarchar(30),
@FloodZone nvarchar(10),
@FloodPolicyInEffectYN bit,
@FloodPolicyEffectiveDate datetime,
@FloodBuildingCoverageAmount money,
@FloodContentsCoverageAmount money,
@BillingSubTotalAmount money,
@BillingAddSurchargesAmount money,
@BillingTotalEstPremiumAmount money,
@BillingAtRenewalBillWhoChoice nvarchar(10),
@BillingPaymentAmountSubmitted money,
@BillingArrangementType nvarchar(50),
@BinderNumber varchar(30),
@BinderBound bit,
@BinderBoundNotReason nvarchar(10),
@BinderEffectiveDate datetime,
@BinderExpirationDate datetime,
@Status nvarchar(50),
@QueueStage nvarchar(50),
@ControlNum nvarchar(50),
@Retention smallint,
@VIPSPolicyNumber varchar(20),
@AppSubmitCode varchar(30)
) AS
–DECLARE @Tries tinyint
–SET @Tries = 1

SET @PolicyID = NEWID() –WHILE @Tries <= 10
–BEGIN
— BEGIN TRANSACTION
— BEGIN TRY SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION INSERT INTO PolicyAttributes (PolicyID,
PolicyEffectiveDate,
PolicyExpirationDate,
PolicyFormTypeTextChoice,
PolicyLocationStreet1,
PolicyLocationStreet2,
PolicyLocationCity,
PolicyLocationState,
PolicyLocationZip,
PolicyLocationTerritoryCode,
PolicyCounty,
CoverageAAmount,
CoverageBAmount,
CoverageCAmount,
CoverageDAmount,
CoverageEAmount,
CoverageFAmount,
CoverageLAmount,
CoverageMAmount,
PhyRiskProtectionClassTextChoice,
PhyRiskBCEGCode,
PremiumProtectiveDiscountsCentralBAlarmYN,
PremiumProtectiveDiscountsCentralFAlarmYN,
PremiumAutomaticSprinklersChoice,
PhyRiskConstructionTypeTextChoice,
PhyRiskPropertyTypeTextChoice,
PhyRiskNumberOfFamilies,
PhyRiskPropertyXWindYN,
DeductiblesAOPAmount,
DeductiblesHurricane,
OptionalCovPersonalPropRCYN,
OptionalCovIncidentalOccReqLiabilityYN,
OptionalCovIncidentalOccReqAmount,
OptionalCovIncidentalOccReqRentedOthersAmount,
OptionalDPOtherStructuresDesc,
OptionalDPImprovementsAmount,
OptionCovDPNamedInsuredItemPLB,
OptionalCovSectionILimitsTextChoiceAmount,
OptionalCovLossAssessCovIncTextChoice,
OptionalDPCondoAmount,
OptionalCovHO3LawOrdinanceYN,
OptionalCovHO8EndorsementsACV2RCLossYN,
OptionalCovHO6SpecialCovAYN,
OptionalCovHO6RentedToOthersYN,
OptionalCovDPExtendedYN,
OptionalCovDPVMMYN,
OptionalCovOnPremiseTheftAmount,
OptionalCovOffPremiseTheftYN,
PremiumMaintainedInsurancePastYearYN,
PremiumMaintainedInsuranceRequiredYN,
PhyRiskYearBuiltTextChoice,
PhyRiskDwellingUpdatedYN,
PhyRiskOccupancyLengthTextChoice,
PhyRiskPropertyInWindPoolYN,
OptionalCovIncidentalOccReqText,
OptionalCovIncidentalOccReqLoc,
OptionalCovIncidentalOccReqEmp,
OptionalCovIncidentalOccReqOthers,
PhyRiskElectricalRenDate,
PhyRiskElectricRenExtentTextChoice,
PhyRiskHeatingRenDate,
PhyRiskHeatingRenExtentTextChoice,
PhyRiskRoofRenDate,
PhyRiskRoofRenExtentTextChoice,
PhyRiskPlumbingRenDate,
PhyRiskPlumbingRenExtentTextChoice,
PhyRiskIsBuilderRiskYN,
PhyRiskBuildersRiskEstCompletionDate,
PhyRiskUsageTypeTextChoice,
PhyRiskOccupancyTypeTextChoice,
PhyRiskDistanceToHydrantFeetChoice,
PhyRiskDistanceToFireStationMilesChoice,
PhyRiskSecuredByTextChoice,
PhyRiskInsideCityLimitsYN,
PhyRiskRespondingFireDeptTextChoice,
PhyRiskNumUnitsInFirDiv,
PhyRiskMunicipalityCodeTextChoice,
PhyRiskMonthUnoccJanYN,
PhyRiskMonthUnoccFebYN,
PhyRiskMonthUnoccMarYN,
PhyRiskMonthUnoccAprYN,
PhyRiskMonthUnoccMayYN,
PhyRiskMonthUnoccJuneYN,
PhyRiskMonthUnoccJulyYN,
PhyRiskMonthUnoccAugYN,
PhyRiskMonthUnoccSeptYN,
PhyRiskMonthUnoccOctYN,
PhyRiskMonthUnoccNovYN,
PhyRiskMonthUnoccDecYN,
PhyRiskUnitFloorLocIntChoice,
PhyRiskNumOfApartments,
PhyRiskSquareFootage,
PhyRiskNumStoriesInBuilding,
PhyRiskYearCertOccIssued,
PhyRiskRoofShapeTextChoice,
PhyRiskRoofTypeTextChoice,
PhyRiskPrimaryHeatSourceTextChoice,
PhyRiskPrimaryHeatSourceProInstalledYN,
UWQSinkHoleYN,
UWQSinkHoleExplain,
UWQHomeCondemnedYN,
UWQHomeCondemnedExplain,
UWQHomeDamagedYN,
UWQHomeDamageCompletionDate,
UWQHomeDamageRepairedYN,
UWQHasSwimmingPoolYN,
UWQHasApprovedPoolFenceYN,
UWQFenceHeight,
UWQHasDiveBoardYN,
UWQHasTrampolineYN,
UWQPostHurricaneInspectionYN,
UWQPostHurricaneInspectionDate,
UWQPostHurricaneInspectionTime,
UWQPropOverWaterYN,
UWQPropOverWaterExplain,
UWQPropOverSandYN,
UWQPropOverSandExplain,
ApplicantQuestion1YN,
ApplicantQuestion2YN,
ApplicantQuestion3YN,
ApplicantQuestion4YN,
ApplicantQuestion5YN,
ApplicantQuestion6YN,
ApplicantQuestion7YN,
ApplicantQuestion8YN,
ApplicantQuestion9YN,
ApplicantQuestion10YN,
ApplicantQuestion10Explain,
ApplicantQuestion11YN,
ApplicantQuestion11Explain,
UWQIsHomeUnoccupiedAtAnyTimeYN,
UWQHomeCheckedText,
UWQNameHomeCheckPerson,
UWQNameHomeCheckPersonPhone,
UWQNeighborCanSeeHomeYN,
UWQBusinessOnPremisesYN,
PriorCoverageInsurerName,
PriorCoverageInsurerPolicy,
PriorCoveragePolicyExpires,
RVReplacementValue,
RVMarketValue,
RVDateOfPurchase,
RVPurchasePrice,
PhyRiskCopperPipesYN,
PhyRiskAgeOfHotWaterHeaterYears,
PhyRiskBinderWashingMachineHosesReplacedPast5YearsYN,
PhyRiskKnobTubeWiresYN,
PhyRiskElectricFusesYN,
PhyRisk100AMPPlusServiceYN,
PhyRiskPlantoAddPortableHeatingYN,
PhyRiskPBPipesInPropertyYN,
PhyRiskWPDFactor,
PhyRiskFoundationType,
FloodHomeInFloodAreaYN,
FloodInsurer,
FloodPolicyNo,
FloodZone,
FloodPolicyInEffectYN,
FloodPolicyEffectiveDate,
FloodBuildingCoverageAmount,
FloodContentsCoverageAmount,
BillingSubTotalAmount,
BillingAddSurchargesAmount,
BillingTotalEstPremiumAmount,
BillingAtRenewalBillWhoChoice,
BillingPaymentAmountSubmitted,
BillingArrangementType,
BinderNumber,
BinderBound,
BinderBoundNotReason,
BinderEffectiveDate,
BinderExpirationDate,
Status,
QueueStage,
ControlNum,
Retention,
VIPSPolicyNumber,
AppSubmitCode
)
VALUES
(
@PolicyID,
@PolicyEffectiveDate,
@PolicyExpirationDate,
@PolicyFormTypeTextChoice,
@PolicyLocationStreet1,
@PolicyLocationStreet2,
@PolicyLocationCity,
@PolicyLocationState,
@PolicyLocationZip,
@PolicyLocationTerritoryCode,
@PolicyCounty,
@CoverageAAmount,
@CoverageBAmount,
@CoverageCAmount,
@CoverageDAmount,
@CoverageEAmount,
@CoverageFAmount,
@CoverageLAmount,
@CoverageMAmount,
@PhyRiskProtectionClassTextChoice,
@PhyRiskBCEGCode,
@PremiumProtectiveDiscountsCentralBAlarmYN,
@PremiumProtectiveDiscountsCentralFAlarmYN,
@PremiumAutomaticSprinklersChoice,
@PhyRiskConstructionTypeTextChoice,
@PhyRiskPropertyTypeTextChoice,
@PhyRiskNumberOfFamilies,
@PhyRiskPropertyXWindYN,
@DeductiblesAOPAmount,
@DeductiblesHurricane,
@OptionalCovPersonalPropRCYN,
@OptionalCovIncidentalOccReqAmount,
@OptionalCovIncidentalOccReqLiabilityYN,
@OptionalCovIncidentalOccReqRentedOthersAmount,
@OptionalDPOtherStructuresDesc,
@OptionalDPImprovementsAmount,
@OptionCovDPNamedInsuredItemPLB,
@OptionalCovSectionILimitsTextChoiceAmount,
@OptionalCovLossAssessCovIncTextChoice,
@OptionalDPCondoAmount,
@OptionalCovHO3LawOrdinanceYN,
@OptionalCovHO8EndorsementsACV2RCLossYN,
@OptionalCovHO6SpecialCovAYN,
@OptionalCovHO6RentedToOthersYN,
@OptionalCovDPExtendedYN,
@OptionalCovDPVMMYN,
@OptionalCovOnPremiseTheftAmount,
@OptionalCovOffPremiseTheftYN,
@PremiumMaintainedInsurancePastYearYN,
@PremiumMaintainedInsuranceRequiredYN,
@PhyRiskYearBuiltTextChoice,
@PhyRiskDwellingUpdatedYN,
@PhyRiskOccupancyLengthTextChoice,
@PhyRiskPropertyInWindPoolYN,
@OptionalCovIncidentalOccReqText,
@OptionalCovIncidentalOccReqLoc,
@OptionalCovIncidentalOccReqEmp,
@OptionalCovIncidentalOccReqOthers,
@PhyRiskElectricalRenDate,
@PhyRiskElectricRenExtentTextChoice,
@PhyRiskHeatingRenDate,
@PhyRiskHeatingRenExtentTextChoice,
@PhyRiskRoofRenDate,
@PhyRiskRoofRenExtentTextChoice,
@PhyRiskPlumbingRenDate,
@PhyRiskPlumbingRenExtentTextChoice,
@PhyRiskIsBuilderRiskYN,
@PhyRiskBuildersRiskEstCompletionDate,
@PhyRiskUsageTypeTextChoice,
@PhyRiskOccupancyTypeTextChoice,
@PhyRiskDistanceToHydrantFeetChoice,
@PhyRiskDistanceToFireStationMilesChoice,
@PhyRiskSecuredByTextChoice,
@PhyRiskInsideCityLimitsYN,
@PhyRiskRespondingFireDeptTextChoice,
@PhyRiskNumUnitsInFirDiv,
@PhyRiskMunicipalityCodeTextChoice,
@PhyRiskMonthUnoccJanYN,
@PhyRiskMonthUnoccFebYN,
@PhyRiskMonthUnoccMarYN,
@PhyRiskMonthUnoccAprYN,
@PhyRiskMonthUnoccMayYN,
@PhyRiskMonthUnoccJuneYN,
@PhyRiskMonthUnoccJulyYN,
@PhyRiskMonthUnoccAugYN,
@PhyRiskMonthUnoccSeptYN,
@PhyRiskMonthUnoccOctYN,
@PhyRiskMonthUnoccNovYN,
@PhyRiskMonthUnoccDecYN,
@PhyRiskUnitFloorLocIntChoice,
@PhyRiskNumOfApartments,
@PhyRiskSquareFootage,
@PhyRiskNumStoriesInBuilding,
@PhyRiskYearCertOccIssued,
@PhyRiskRoofShapeTextChoice,
@PhyRiskRoofTypeTextChoice,
@PhyRiskPrimaryHeatSourceTextChoice,
@PhyRiskPrimaryHeatSourceProInstalledYN,
@UWQSinkHoleYN,
@UWQSinkHoleExplain,
@UWQHomeCondemnedYN,
@UWQHomeCondemnedExplain,
@UWQHomeDamagedYN,
@UWQHomeDamageCompletionDate,
@UWQHomeDamageRepairedYN,
@UWQHasSwimmingPoolYN,
@UWQHasApprovedPoolFenceYN,
@UWQFenceHeight,
@UWQHasDiveBoardYN,
@UWQHasTrampolineYN,
@UWQPostHurricaneInspectionYN,
@UWQPostHurricaneInspectionDate,
@UWQPostHurricaneInspectionTime,
@UWQPropOverWaterYN,
@UWQPropOverWaterExplain,
@UWQPropOverSandYN,
@UWQPropOverSandExplain,
@ApplicantQuestion1YN,
@ApplicantQuestion2YN,
@ApplicantQuestion3YN,
@ApplicantQuestion4YN,
@ApplicantQuestion5YN,
@ApplicantQuestion6YN,
@ApplicantQuestion7YN,
@ApplicantQuestion8YN,
@ApplicantQuestion9YN,
@ApplicantQuestion10YN,
@ApplicantQuestion10Explain,
@ApplicantQuestion11YN,
@ApplicantQuestion11Explain,
@UWQIsHomeUnoccupiedAtAnyTimeYN,
@UWQHomeCheckedText,
@UWQNameHomeCheckPerson,
@UWQNameHomeCheckPersonPhone,
@UWQNeighborCanSeeHomeYN,
@UWQBusinessOnPremisesYN,
@PriorCoverageInsurerName,
@PriorCoverageInsurerPolicy,
@PriorCoveragePolicyExpires,
@RVReplacementValue,
@RVMarketValue,
@RVDateOfPurchase,
@RVPurchasePrice,
@PhyRiskCopperPipesYN,
@PhyRiskAgeOfHotWaterHeaterYears,
@PhyRiskBinderWashingMachineHosesReplacedPast5YearsYN,
@PhyRiskKnobTubeWiresYN,
@PhyRiskElectricFusesYN,
@PhyRisk100AMPPlusServiceYN,
@PhyRiskPlantoAddPortableHeatingYN,
@PhyRiskPBPipesInPropertyYN,
@PhyRiskWPDFactor,
@PhyRiskFoundationType,
@FloodHomeInFloodAreaYN,
@FloodInsurer,
@FloodPolicyNo,
@FloodZone,
@FloodPolicyInEffectYN,
@FloodPolicyEffectiveDate,
@FloodBuildingCoverageAmount,
@FloodContentsCoverageAmount,
@BillingSubTotalAmount,
@BillingAddSurchargesAmount,
@BillingTotalEstPremiumAmount,
@BillingAtRenewalBillWhoChoice,
@BillingPaymentAmountSubmitted,
@BillingArrangementType,
@BinderNumber,
@BinderBound,
@BinderBoundNotReason,
@BinderEffectiveDate,
@BinderExpirationDate,
@Status,
@QueueStage,
@ControlNum,
@Retention,
@VIPSPolicyNumber,
@AppSubmitCode
) — COMMIT
— BREAK
— END TRY
— BEGIN CATCH
— SELECT ERROR_NUMBER() AS ErrorNumber
— ROLLBACK
— SET @Tries = @Tries + 1
—- WAITFOR DELAY ’00:00:01′
— CONTINUE
— END CATCH;
–END COMMIT SELECT @PolicyID
Don.
WOW thats a lot of Parameters [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />].<br /><br />Would you mind to tell, how many indexes are created for this table <b>PolicyAttributes</b>?<br /><br />Waqar.<br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
On unique clustered index on PolicyID. FYI, the issue happened again today so I ran PSSDIAG. PSSDIAG cloned the database but the cloned database was missing the one table listed above, PolicyAttibutes. The system starts to fail when I do the insert above. Thanks. Don.
there is no point to the SET read uncommitted because this is an insert
so it cannot do an uncommited read i see no reason for a single row insert to consume excessive cpu
are there any triggers on this table but the more likely reason your insert is failing is that there are other read queries doing a table scan, ie, getting a shared table lock, but preventing the insert

Thanks ‘jc’ for the input. I did find something in a trigger that would cause a problem. We were doing some ‘house cleaning’ that would be better suited as part of a maintenance plan. One other change that was the call to the SP from the web service was from inside a .NET 2.0 ‘Transaction Object’. I removed the object and call the SP directly and took out the begin/commit from the SP. The system is screaming now. I don’t know is there was some overhead or ‘concurrency’ issues with having the transaction within the transaction object or some settings on the transaction object that was creating conflicts. All is well for now. Thanks to all. Don. Don.
]]>