November 23, 2009 at 5:12 pm
I have a query that executes quickly given one set of search criteria (almost instantaneous); and is a dog when given another (30+ seconds). Instead of describing how I have been beating my head against my monitor I'll get down to business...
Let me know if I forgot to include any pertinent information.
We are accessing the database via a .net 08 c# application via a stored procedure hosted on a seperate server.
The database is on a virtual server installation:
• 2 Virtual processors
• 4096Mb RAM
• Windows 2003 Server Standard Edition with Sp2.
• Microsoft IIS 6.0
• Microsoft SQL 2005 Server Standard Edition with SP1
The Query:
/******************************************************************/
WITH PageResult AS
(
Select
ROW_NUMBER() OVER (ORDER BY Quote.DateQuoted)
AS
RowNumber,
Policy.RecordID as PolicyRecordID,
Policy.Locked,
Policy.LockedBy,
Policy.LockedByUserName,
Policy.LockedDateTime,
Policy.Deleted,
Policy.DeletedBy,
Policy.DeletedDateTime,
Insured.RecordID as InsuredRecordID,
Insured.AgencyLocationGUID,
Insured.FirstName,
Insured.MiddleName,
Insured.LastName,
Insured.Phone,
Insured.WorkPhone,
Quote.HasSecondaryData,
Quote.OriginalSaveDate,
Quote.QuoteDescription,
Quote.BinderNumber,
Quote.BinderDate,
Quote.CompanyID,
Quote.SecondaryCompanyID,
Quote.CompanyName,
Quote.CompanyRateRevision,
Quote.SecondaryCompanyRateRevision,
Quote.ProgramID,
Quote.SecondaryProgramID,
Quote.LastQuotedDate,
Quote.QuotedByFirstName,
Quote.QuotedByInitial,
Quote.QuotedByLastName,
Quote.DateQuoted,
Quote.LastQuotedByFirstName,
Quote.LastQuotedByInitial,
Quote.LastQuotedByLastName,
Quote.WrittenByFirstName,
Quote.WrittenByInitial,
Quote.WrittenByLastName,
Quote.CompanyEffectiveDate,
Quote.Bound,
Quote.ExportDate,
Quote.ExportTime,
Quote.WindowsRecordID,
Quote.TemplateDescription
From
tblDriver [Insured] (nolock)
Right Join tblAUPolicy [Policy] (nolock) ON (Policy.InsuredLinkID = Insured.RecordID)
Right Join tblQuote [Quote] (nolock) ON (Quote.PolicyLinkID = Policy.RecordID)
where
(
(Insured.PolicyLinkID = -1)
AND (Insured.AgencyGUID = 'B6649D01-94DF-4D45-8060-A944DA67A27C')
AND (Policy.Deleted = 0)
AND (Quote.QuoteTemplate = 0)
AND (Insured.ProductID = '2428'))
)
SELECT * FROM PageResult WHERE (RowNumber >= 1) AND (RowNumber < 61)
/******************************************************************/
The execution plan changes along with performance when I switch between the following search values for "Insured.AgencyGUID" & "Insured.ProductID" respectively:
'826CEB74-0047-4AE7-A3E3-EE9A5EB8CD1E'; '1940'
'B6649D01-94DF-4D45-8060-A944DA67A27C'; '2428'
/******************************************************************/
Here is some data statistics:
select count(*)
from tblDriver
--683916 rows returned
where
AgencyGUID = 'B6649D01-94DF-4D45-8060-A944DA67A27C'
--7996 rows returned
AND ProductID = '2428'
--31 rows returned
select count(*)
from tblDriver
--683916 rows returned
where
AgencyGUID = '826CEB74-0047-4AE7-A3E3-EE9A5EB8CD1E'
--13397 rows returned
AND ProductID = '1940'
--13397 rows returned
select count(*)
from tblAUPolicy
--282874 rows returned
select count(*)
from tblQuote
--282874 rows returned
/******************************************************************/
I have tried adding the following indexes without much help:
CREATE NONCLUSTERED INDEX [IX_tblQuote_QuoteTemplate] ON [dbo].[tblQuote] ([QuoteTemplate])
GO
CREATE NONCLUSTERED INDEX [IX_tblDriver_PolicyLinkID_AgencyGUID_ProductID] ON [dbo].[tblDriver] ([PolicyLinkID], [AgencyGUID], [ProductID])
GO
CREATE NONCLUSTERED INDEX [IX_tblDriver_ProductID] ON [dbo].[tblDriver] ([ProductID])
GO
CREATE NONCLUSTERED INDEX [IX_tblDriver_RecordID] ON [dbo].[tblDriver] ([RecordID])
GO
CREATE NONCLUSTERED INDEX [IX_tblAUPolicy_Deleted] ON [dbo].[tblAUPolicy] ([Deleted])
GO
CREATE NONCLUSTERED INDEX [IX_tblAUPolicy_RecordID] ON [dbo].[tblAUPolicy] ([RecordID])
GO
/******************************************************************/
Here are the structures of the 3 tables involved in the query (the above indexes did not change performance and were subsequently removed)...
/******************************************************************/
/****** Object: Table [dbo].[tblAUPolicy] Script Date: 11/23/2009 17:38:12 ******/
/******************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAUPolicy](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[InsuredLinkID] [int] NULL,
[UserLinkID] [int] NULL,
[GroupLinkID] [int] NULL,
[Term] [int] NULL,
[CoTerm] [int] NULL,
[TermDuration] [varchar](25) NULL,
[CoTermDuration] [varchar](25) NULL,
[Remarks] [varchar](1024) NULL,
[EffectiveDate] [datetime] NULL,
[BinderEffectiveDate] [datetime] NULL,
[BinderExpirationDate] [datetime] NULL,
[PolicyFee] [float] NULL,
[TotalPremium] [float] NULL,
[CreditScore] [varchar](20) NULL,
[CoCreditScore] [varchar](20) NULL,
[UDDStatus] [varchar](25) NULL,
[HOVStatus] [varchar](25) NULL,
[CreditVerification] [varchar](50) NULL,
[CoCreditVerification] [varchar](50) NULL,
[CreditScoreDateTime] [datetime] NULL,
[Tier] [varchar](15) NULL,
[DateCreated] [datetime] NULL,
[PriorCarrierName] [varchar](50) NULL,
[PriorEffDate] [datetime] NULL,
[PriorEffTime] [datetime] NULL,
[PriorExpDate] [datetime] NULL,
[PriorExpTime] [datetime] NULL,
[PriorInAgency] [bit] NULL,
[PriorPolicyNumber] [varchar](20) NULL,
[NumOfPayments] [int] NULL,
[PercentDown] [float] NULL,
[PayPlanDescription] [varchar](50) NULL,
[CoTierStr] [varchar](20) NULL,
[NonOwner] [bit] NULL,
[SR22Fee] [float] NULL,
[NumOfDrivers] [int] NULL,
[NumOfExclusions] [int] NULL,
[NumOfCars] [int] NULL,
[FullTort] [bit] NULL,
[ATPAFee] [float] NULL,
[CollectionFee] [float] NULL,
[LawEnforceFee] [float] NULL,
[MunicipalTax] [float] NULL,
[MunicipalTaxRate] [float] NULL,
[SecurityVerificationFee] [float] NULL,
[StampFee] [float] NULL,
[StateTax] [float] NULL,
[StateTaxRate] [float] NULL,
[Tax] [float] NULL,
[UninsMotorFee] [float] NULL,
[AutoRate] [bit] NULL,
[CoveragesByCar] [bit] NULL,
[DeletedDriverCount] [int] NULL,
[DeletedExclusionCount] [int] NULL,
[DeletedMiscPremiumCount] [int] NULL,
[DeletedUnitCount] [int] NULL,
[DOSCompanyID] [int] NULL,
[CommissionPercent] [float] NULL,
[CommissionPremium] [float] NULL,
[ExclusionCode] [int] NULL,
[CoBridgedCreditScore] [varchar](20) NULL,
[CoUDDStatus] [varchar](25) NULL,
[CoHOVStatus] [varchar](25) NULL,
[CoBridgedCreditVerification] [varchar](50) NULL,
[CreditScoreTransactionID] [int] NULL,
[InsuranceScoreEntryDoneDate] [datetime] NULL,
[AllowPayPlanChange] [bit] NULL,
[APR] [float] NULL,
[InsuredDeclinedCredit] [bit] NULL,
[BiMonthly] [bit] NULL,
[CreatedByUniversalUpload] [bit] NULL,
[CreditScoreServerID] [varchar](3) NULL,
[DownPayment] [float] NULL,
[DownPaymentOverride] [float] NULL,
[EndorsementOriginalTermPremium] [float] NULL,
[HOVOrderNum] [varchar](30) NULL,
[HOVRefNum] [varchar](30) NULL,
[InterfaceCompanyID] [int] NULL,
[MonthlyFinanced] [bit] NULL,
[PaymentTotal] [float] NULL,
[PrimaryPolicy] [bit] NULL,
[PriorCarrierAddress1] [varchar](50) NULL,
[PriorCarrierAddress2] [varchar](50) NULL,
[PriorCarrierCity] [varchar](50) NULL,
[PriorCarrierPolicyNumberString] [varchar](20) NULL,
[PriorCarrierState] [varchar](50) NULL,
[PriorCarrierZipCode] [varchar](10) NULL,
[RTRITCCompanyTransactionID] [varchar](40) NULL,
[RTRITCGroupTransactionID] [varchar](40) NULL,
[RTRThirdPartyQuoteURL] [varchar](300) NULL,
[RTRThirdPartyTransactionID] [varchar](40) NULL,
[SecondaryCoCreditScore] [varchar](20) NULL,
[SecondaryCoCreditVerification] [varchar](50) NULL,
[SecondaryCreditScore] [varchar](20) NULL,
[SecondaryCreditVerification] [varchar](50) NULL,
[SecondaryPolicyFee] [float] NULL,
[TaxablePremium] [float] NULL,
[UDDTransactionDate] [datetime] NULL,
[UDDTransactionID] [varchar](20) NULL,
[FinanceAmount] [float] NULL,
[FinanceCharge] [float] NULL,
[FinanceMessage] [varchar](50) NULL,
[FinanceQualified] [bit] NULL,
[FRBond] [bit] NULL,
[OriginalSaveDate] [datetime] NULL,
[HasSecondaryData] [bit] NULL,
[UseSecondaryData] [bit] NULL,
[RealTimeRatingData] [text] NULL,
[ThirdPartyCreditResponseData] [text] NULL,
[UDRNotes] [text] NULL,
[Locked] [bit] NULL,
[LockedBy] [varchar](50) NULL,
[LockedDateTime] [datetime] NULL,
[Deleted] [bit] NULL,
[DeletedBy] [varchar](50) NULL,
[DeletedDateTime] [datetime] NULL,
[LockedByUserName] [varchar](110) NULL,
[CompanyDataStorage] [text] NULL,
[NonStoredDataStorage] [text] NULL,
[CompanyModuleContentsDataStorage] [text] NULL,
[ApplicationBlob] [image] NULL,
[FR44Fee] [float] NULL,
[InsuranceScoreData] [text] NULL,
[WebAppStorage] [text] NULL,
[SourceProduct] [varchar](30) NULL,
[PaymentAmount1] [float] NULL,
CONSTRAINT [PK_tblAUPolicy] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/******************************************************************/
/****** Object: Table [dbo].[tblDriver] Script Date: 11/23/2009 17:39:09 ******/
/******************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblDriver](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[County] [varchar](35) NULL,
[State] [varchar](25) NULL,
[ZipCode] [varchar](10) NULL,
[BankLienJudgStat] [bit] NULL,
[CreditCard] [bit] NULL,
[DateLicensed] [datetime] NULL,
[DateLicensedState] [datetime] NULL,
[DefensiveDriving] [bit] NULL,
[DOB] [datetime] NULL,
[DriverClass] [varchar](20) NULL,
[DriversTraining] [bit] NULL,
[DrugAwareness] [bit] NULL,
[DrvLicenseNumber] [varchar](256) NULL,
[Employed] [varchar](1) NULL,
[EmployedDate] [datetime] NULL,
[Excluded] [bit] NULL,
[ForeignNatl] [bit] NULL,
[GoodCredit] [bit] NULL,
[GoodStudent] [bit] NULL,
[InternatDL] [bit] NULL,
[IsPrimaryOperator] [bit] NULL,
[LearnersPermit] [bit] NULL,
[Licensed] [bit] NULL,
[LicensedState] [bit] NULL,
[Marital] [varchar](1) NULL,
[MilesToWork] [int] NULL,
[MonthsLicensed] [int] NULL,
[MonthsLicensedState] [int] NULL,
[MonthsMVRExper] [int] NULL,
[MonthsNoBillCollector] [int] NULL,
[MonthsNoPastDue] [int] NULL,
[MonthsSuspended] [int] NULL,
[NonSmoker] [bit] NULL,
[OccasionalOperator] [bit] NULL,
[Occupation] [varchar](30) NULL,
[PrimaryCar] [int] NULL,
[PriorCompId] [int] NULL,
[PriorDaysLapse] [int] NULL,
[PriorInsurance] [bit] NULL,
[PriorLicenseNum] [varchar](16) NULL,
[PriorLicenseState] [varchar](2) NULL,
[PriorMonthsCovg] [int] NULL,
[PriorTransferLevel] [varchar](1) NULL,
[ProofOfOwnership] [bit] NULL,
[PropertyInsurance] [bit] NULL,
[RankE5OrHigher] [bit] NULL,
[Relation] [varchar](1) NULL,
[ResidencyStatus] [varchar](1) NULL,
[ResidencyType] [varchar](1) NULL,
[ResideTime] [int] NULL,
[SeniorDrvDisc] [bit] NULL,
[Sex] [varchar](1) NULL,
[SingleParent] [bit] NULL,
[SR22] [bit] NULL,
[SR22A] [bit] NULL,
[SR22CaseNum] [varchar](20) NULL,
[SR22Date] [datetime] NULL,
[SR22Reason] [varchar](1) NULL,
[SR22State] [varchar](2) NULL,
[StateLicensed] [varchar](2) NULL,
[Suffix] [varchar](12) NULL,
[SuspendedLic] [bit] NULL,
[Title] [varchar](12) NULL,
[ViolPoints] [int] NULL,
[PriorLiabLim1] [int] NULL,
[PriorLiabLim2] [int] NULL,
[PriorLiabLim3] [int] NULL,
[MultiPolicies] [bit] NULL,
[PolicyLinkID] [int] NULL,
[PersonType] [varchar](25) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone] [varchar](25) NULL,
[WorkPhone] [varchar](25) NULL,
[SSN] [varchar](256) NULL,
[FaxNumber] [varchar](25) NULL,
[EmailAddress] [varchar](75) NULL,
[PriorAddress1] [varchar](100) NULL,
[PriorAddress2] [varchar](100) NULL,
[PriorCity] [varchar](75) NULL,
[PriorState] [varchar](50) NULL,
[PriorZipCode] [varchar](10) NULL,
[EmployedTime] [int] NULL,
[EmployerName] [varchar](50) NULL,
[EmployerAddress1] [varchar](100) NULL,
[EmployerAddress2] [varchar](100) NULL,
[EmployerCity] [varchar](75) NULL,
[EmployerState] [varchar](50) NULL,
[EmployerZipCode] [varchar](10) NULL,
[MVRStatus] [varchar](25) NULL,
[CLUEStatus] [varchar](25) NULL,
[CurrentCarrierStatus] [varchar](25) NULL,
[PolicyType] [varchar](25) NULL,
[WorkPhoneExt] [varchar](15) NULL,
[CellPhone] [varchar](25) NULL,
[PriorStreetName] [varchar](100) NULL,
[PriorStreetNumber] [varchar](25) NULL,
[PriorApartmentNumber] [varchar](50) NULL,
[PriorStreetType] [varchar](50) NULL,
[EmployerPhone] [varchar](25) NULL,
[CoMVRStatus] [varchar](25) NULL,
[CoCLUEStatus] [varchar](25) NULL,
[CoCurrentCarrierStatus] [varchar](25) NULL,
[CurrentCarrierAmbestNum] [varchar](25) NULL,
[CurrentCarrierOrderNum] [varchar](25) NULL,
[CurrentCarrierRefNum] [varchar](25) NULL,
[MVRCPStatus] [varchar](1) NULL,
[PriorStateMVRCPStatus] [varchar](1) NULL,
[MVRDateOrdered] [datetime] NULL,
[MVRURL] [varchar](200) NULL,
[IsACompany] [bit] NULL,
[Referral] [varchar](25) NULL,
[OriginalSaveDate] [datetime] NULL,
[HasSecondaryData] [bit] NULL,
[UseSecondaryData] [bit] NULL,
[AgeRated] [int] NULL,
[CoDrvTierStr] [varchar](30) NULL,
[DriverID] [int] NULL,
[GroupCode] [int] NULL,
[InsPersonVersionRecord] [int] NULL,
[SecondaryDriverClass] [varchar](20) NULL,
[SecurityVerification] [bit] NULL,
[RestructureField] [bit] NULL,
[SecondaryPriorCompId] [int] NULL,
[AgencyGUID] [uniqueidentifier] NULL,
[AgencyLocationGUID] [uniqueidentifier] NULL,
[AgencyUserGUID] [uniqueidentifier] NULL,
[ProductID] [varchar](10) NULL,
[DefensiveDrivingCourseDate] [datetime] NULL,
[SeniorDriverCourseDate] [datetime] NULL,
[Disabled] [bit] NULL,
[CompanyDataStorage] [text] NULL,
[NonStoredDataStorage] [text] NULL,
[CompanyModuleContentsDataStorage] [text] NULL,
[FR44] [bit] NULL,
[CountryOfOrigin] [varchar](10) NULL,
[WebAppStorage] [text] NULL,
[SR50] [bit] NULL,
CONSTRAINT [PK_tblDriver] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/******************************************************************/
/****** Object: Table [dbo].[tblQuote] Script Date: 11/23/2009 17:40:17 ******/
/******************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblQuote](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[PolicyLinkID] [int] NULL,
[BinderNumber] [varchar](25) NULL,
[BatchGroup] [varchar](20) NULL,
[NumOfNotes] [int] NULL,
[InterfaceMajorRevision] [int] NULL,
[InterfaceMinorRevision] [int] NULL,
[InterfaceReleaseRevision] [int] NULL,
[InterfaceBuildRevision] [int] NULL,
[InterfaceRateRevision] [int] NULL,
[CompanyMajorRevision] [int] NULL,
[CompanyMinorRevision] [int] NULL,
[CompanyReleaseRevision] [int] NULL,
[CompanyBuildRevision] [int] NULL,
[WrittenByLastName] [varchar](50) NULL,
[WrittenByFirstName] [varchar](50) NULL,
[WrittenByInitial] [varchar](1) NULL,
[BinderDate] [datetime] NULL,
[BinderTime] [datetime] NULL,
[BinderDays] [int] NULL,
[Bound] [bit] NULL,
[CompanyCode] [varchar](20) NULL,
[CompanyEffectiveDate] [datetime] NULL,
[CompanyID] [int] NULL,
[PFPayPlanID] [int] NULL,
[PFProgramID] [int] NULL,
[ProgramID] [int] NULL,
[ProgramName] [varchar](40) NULL,
[PolicyNumber] [varchar](25) NULL,
[ProducerCode] [varchar](25) NULL,
[CompanyName] [varchar](50) NULL,
[CompanyPhone] [varchar](25) NULL,
[QuoteAgencyWebsiteURL] [varchar](200) NULL,
[QuoteDescription] [varchar](60) NULL,
[QuoteTemplate] [bit] NULL,
[SecondaryBinderDate] [datetime] NULL,
[SecondaryBinderDays] [int] NULL,
[SecondaryBinderNumber] [varchar](25) NULL,
[SecondaryBinderTime] [datetime] NULL,
[SecondaryBound] [bit] NULL,
[SecondaryCompanyEffectiveDate] [datetime] NULL,
[SecondaryCompanyID] [int] NULL,
[SecondaryExportTime] [datetime] NULL,
[TemplateDescription] [varchar](50) NULL,
[CompanyRateRevision] [int] NULL,
[ContractNumber] [varchar](20) NULL,
[DateQuoted] [datetime] NULL,
[EffectiveTime] [datetime] NULL,
[ExpirationDate] [datetime] NULL,
[ExpirationTime] [datetime] NULL,
[ExportDate] [datetime] NULL,
[ExportTime] [datetime] NULL,
[DeletedNoteCount] [int] NULL,
[FinanceCompanyAddress1] [varchar](100) NULL,
[FinanceCompanyAddress2] [varchar](100) NULL,
[FinanceCompanyCity] [varchar](50) NULL,
[FinanceCompanyName] [varchar](50) NULL,
[FinanceCompanyState] [varchar](50) NULL,
[FinanceCompanyZipCode] [varchar](10) NULL,
[QuotedByLastName] [varchar](50) NULL,
[LastQuotedByLastName] [varchar](50) NULL,
[LastQuotedByFirstName] [varchar](50) NULL,
[LastQuotedByInitial] [varchar](1) NULL,
[LastQuotedDate] [datetime] NULL,
[LeadSource] [varchar](255) NULL,
[NAICCode] [varchar](20) NULL,
[QuotedByFirstName] [varchar](50) NULL,
[QuotedByInitial] [varchar](1) NULL,
[QuoteAgencyTaxID] [varchar](50) NULL,
[QuoteAgencyName] [varchar](100) NULL,
[QuoteAgencyPhone] [varchar](25) NULL,
[QuoteAgencyFax] [varchar](25) NULL,
[QuoteAgencyAlternatePhone] [varchar](25) NULL,
[QuoteAgencyAddress1] [varchar](100) NULL,
[QuoteAgencyAddress2] [varchar](100) NULL,
[QuoteAgencyCity] [varchar](50) NULL,
[QuoteAgencyState] [varchar](50) NULL,
[QuoteAgencyZipCode] [varchar](10) NULL,
[OriginalSaveDate] [datetime] NULL,
[HasSecondaryData] [bit] NULL,
[UseSecondaryData] [bit] NULL,
[ExportedByFirstName] [varchar](16) NULL,
[ExportedByLastName] [varchar](16) NULL,
[ExportedByMiddleInitial] [varchar](16) NULL,
[SecondaryCompanyCode] [varchar](16) NULL,
[SecondaryCompanyName] [varchar](35) NULL,
[SecondaryCompanyRateRevision] [int] NULL,
[SecondaryContractNumber] [varchar](16) NULL,
[SecondaryExportDate] [datetime] NULL,
[SecondaryPFPayPlanID] [int] NULL,
[SecondaryPFProgramID] [int] NULL,
[SecondaryPolicyNumber] [varchar](25) NULL,
[SecondaryProducerCode] [varchar](20) NULL,
[SecondaryProgramID] [int] NULL,
[SecondaryProgramName] [varchar](35) NULL,
[EndorsementUnearnedFactor] [float] NULL,
[jcBumpLimits] [bit] NULL,
[jcEmbedFiles] [bit] NULL,
[jcErrorFile] [varchar](16) NULL,
[jcEstimateTerm] [int] NULL,
[jcLogFile] [varchar](16) NULL,
[jcOrderCreditScore] [int] NULL,
[jcReturnLowestCombo] [int] NULL,
[SecondaryThirdPartyCreditResponse] [varchar](255) NULL,
[ThirdPartyCreditResponse] [varchar](255) NULL,
[WindowsRecordID] [int] NULL,
[CompanyDataStorage] [text] NULL,
[NonStoredDataStorage] [text] NULL,
[CompanyModuleContentsDataStorage] [text] NULL,
[WebAppStorage] [text] NULL,
[LastTotalPremiumQuoted] [float] NULL,
[LastDownPaymentQuoted] [float] NULL,
[LastPayPlanQuoted] [varchar](255) NULL,
CONSTRAINT [PK_tblQuotes] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/******************************************************************/
/******************************************************************/
November 23, 2009 at 9:14 pm
GOOGLE for "Parameter Sniffing SQL Server". I believe that's your problem.
--Jeff Moden
November 24, 2009 at 12:26 am
Post execution plans?
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
November 24, 2009 at 10:13 am
Jeff, I'll google your suggestion now.
GilaMonster, attached are the execution plans for both queries. The main difference between the two is with index seeks changing to scans for tblPolicy & tblQuote when I execute with ProductID 2428. The seeks execute much faster than the scans in this instance. Only other item I noticed is if I remove the where clause "(Insured.PolicyLinkID = -1)" the execution plans match and things are just great. The problem with this however is we use this field to determine if the record is a driver on the policy or if the driver is the named insured on the policy. So, basically I can't get rid of that field from the query itself.
Thanks for looking guys.
Jack
November 24, 2009 at 10:29 am
Can you give me the definition of this index please
[tblAUPolicy].[_dta_index_tblAUPolicy_7_357576312__K2_K115_K1_112_113_114_116_117_118]
In fact, can you give the definitions of all the indexes on that table?
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
November 24, 2009 at 10:49 am
Here are the existing indexes. For some reason I thought I had already included them in my original post.
The index you asked about was actually suggested by the query analyzer when I analyzed the 1940 query. I have attached a list of all the indexes that were suggested then as well. These were added to the database a couple of weeks ago. It greatly helped the 1940 query, but had no affect on the remainder of the agent guids.
Another item to note is when I run the query analyzer with the 2848 data it suggests I delete ALL indexes within these three tables.
Jack
November 24, 2009 at 11:11 am
Can you also try a statistics update with full scan on the Drivers table? The row estimates are way off, probably contributing to the poor plan. Weird thing is, there are no parameters in the query, only constants, so it's not parameter sniffing.
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
November 24, 2009 at 11:13 am
jmartinez-847815 (11/24/2009)
Another item to note is when I run the query analyzer with the 2848 data it suggests I delete ALL indexes within these three tables.
I assume you mean Database Tuning Advisor, as Query Analyser is just a querying tool. DTA is less that ideal in a number of cases, I'd think very hard before implementing its recommendations, especially implementing without testing.
Will get to this tomorrow.
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
November 24, 2009 at 11:20 am
GilaMonster (11/24/2009)
jmartinez-847815 (11/24/2009)
Another item to note is when I run the query analyzer with the 2848 data it suggests I delete ALL indexes within these three tables.I assume you mean Database Tuning Advisor, as Query Analyser is just a querying tool. DTA is less that ideal in a number of cases, I'd think very hard before implementing its recommendations, especially implementing without testing.
Will get to this tomorrow.
Seconding what Gail has said about DTA. Any of your indexes prefixed with "_dta" are indexes implemented through the DTA. I have found that the indexes suggested by DTA are less than desirable, while others are legit. Any indexes that it suggests, I would test in a test environment and rename to something more meaningful (at the very least). I would also be cautious of dropping any indexes based solely on the recommendations of DTA.
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
November 24, 2009 at 12:00 pm
Yes, I meant tuning advisor. As soon as I posted that I realized my mistake.
I am doing my testing on my local machine on a restored backup of the live system as this is where the problem first manifested. This way I will not affect production transactions. We also have a test environment that I roll any changes to once I have looked things over that will then get hammered on for a while before rolling live. I'm doing it this way as I don't want to negatively impact our production or testing departments. Would hate to get the "Do you like your cushy job?" speech...
I have updated the statistics several times throughout my testing without any real results always after I add/remove any indexing. I have even gone so far as to restart my SQL service to clear out anything there as well. I'll do it again and then re-run the queries in my original post. Never know, in my frustration I may have missed something simple.
Like I stated in my first post, I have been beating my head on my monitor and finally reached a point where I knew this was just a little bit beyond my abilities to solve. Hence the posting here. 🙂
As for parameter sniffing as a possible cause, the stp on the production machine is actually accepting parameters. NOTE however that I am able to consistently replicate the performance hit by just running the original query in my first post with the values defined in the where clause. For grins however here is the original unadulterated stp definition for your review and pleasure. Again, please remember that I CAN consistently cause the extended execution (30+ seconds) by stripping out the final query created by this stp (the one I included in my first post) and simply inserting it into the query analyzer with the values hard coded that I originally provided.
/*********************************************************************************/
/****** Object: StoredProcedure [dbo].[stpGetQuoteHeaders] Script Date: 11/24/2009 12:43:40 ******/
/*********************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Returns the header data from the agencies policies for listing purposes.
If the procedure is called with NumberOfRecords = 0 then
the entire resultset from the table is returned.
*/
ALTER PROCEDURE [dbo].[stpGetQuoteHeaders]
(
@AgencyGUID uniqueidentifier,
@LocationGUID uniqueidentifier,
@UserGUID uniqueidentifier,
@ProductID varchar(10),
@Deleted char = '0', -- Returns the policies the are deleted status of: 0=Not deleted, 1=Deleted
@FilterType int = 0, -- 0=Agency, 1=Location, 2=User
@SearchType int = 0, -- 0=InsuredName,1=SSN,2=HomePhone,3=WorkPhone,4=DateQuoted,5=QuoteNumber,6=BinderDate,7=LastQuotedDate
@SearchValue VarChar(100) = '',
@RecordIndex int = 0, -- Bookmark position for paging (rownumber)
@NumberOfRecords int = 0, -- The number of pages we want returned
@SearchValue2 Varchar(100) = '' -- If you want a range of values, use this as the upper limit for the range
)
AS
BEGIN TRY
-- This is the main execution block. Any errors that occur are trapped anf logged to the tblSQL_Error_Log
Declare
@SQL varchar(8000),
@Top int,
@SQLSearchStr varchar(300),
@SQLOrderByStr varchar(100),
@SQLTemplateClause varchar(100)
-- Initialize the local variables
Set @SQL = '';
Set @SQLSearchStr = '';
Set @SQLOrderByStr = '';
SET @SQLTemplateClause = ''
-- Main execution
IF (@AgencyGUID Is NOT Null)
BEGIN
IF (@Deleted = '0') SET @SQLTemplateClause = '(Quote.QuoteTemplate = 0) And '
-- Set SQLOrderByStr value
IF @SearchType = 0 Set @SQLOrderByStr = 'Insured.LastName, Insured.FirstName, Insured.MiddleName, Insured.RecordID'
Else If @SearchType = 1 Set @SQLOrderByStr = '(SELECT CONVERT(VARCHAR(256), DecryptByKey(CONVERT(VARBINARY(256), Insured.SSN)))'
Else If @SearchType = 2 Set @SQLOrderByStr = 'Insured.Phone'
Else If @SearchType = 3 Set @SQLOrderByStr = 'Insured.WorkPhone'
Else If @SearchType = 4 Set @SQLOrderByStr = 'Quote.DateQuoted'
Else If @SearchType = 5 Set @SQLOrderByStr = 'Quote.WindowsRecordID' --Changed from Policy.RecordID
Else If @SearchType = 6 Set @SQLOrderByStr = 'Quote.BinderDate'
Else If @SearchType = 7 Set @SQLOrderByStr = 'Quote.LastQuotedDate'
Else
Set @SQLOrderByStr = 'Insured.LastName'
-- Set SQLSearchStr value
SET @SearchValue = Replace(@SearchValue, '''', '''''')
SET @SearchValue2 = Replace(@SearchValue2, '''', '''''')
If LTrim(RTrim(@SearchValue)) != ''
Begin
DECLARE @CheckSymbol varchar(5)
SET @CheckSymbol = '='
If LTrim(RTrim(@SearchValue2)) != '' SET @CheckSymbol = '>='
IF @SearchType = 0 Set @SQLSearchStr = ' AND (((Insured.LastName Like ''' + @SearchValue + '%'')) '
Else If @SearchType = 1 Set @SQLSearchStr = ' AND (((SELECT CONVERT(VARCHAR(256), DecryptByKey(CONVERT(VARBINARY(256), Insured.SSN))) Like ''%' + @SearchValue + '%'') '
Else If @SearchType = 2 Set @SQLSearchStr = ' AND ((Insured.Phone Like ''%' + @SearchValue + '%'') '
Else If @SearchType = 3 Set @SQLSearchStr = ' AND ((Insured.WorkPhone Like ''%' + @SearchValue + '%'') '
Else If @SearchType = 4 Set @SQLSearchStr = ' AND ((Quote.DateQuoted ' + @CheckSymbol + ' ''' + @SearchValue + ''') '
Else If @SearchType = 5 Set @SQLSearchStr = ' AND ((Quote.WindowsRecordID ' + @CheckSymbol + ' ' + @SearchValue + ') ' --Changed from Policy.RecordID
Else If @SearchType = 6 Set @SQLSearchStr = ' AND ((Quote.BinderDate ' + @CheckSymbol + ' ''' + @SearchValue + ''') '
Else If @SearchType = 7 Set @SQLSearchStr = ' AND ((Quote.LastQuotedDate ' + @CheckSymbol + ' ''' + @SearchValue + ''') '
Else
Set @SQLSearchStr = ''
If LTrim(RTrim(@SearchValue2)) != ''
Begin
DECLARE @CheckSymbol2 varchar(5)
SET @CheckSymbol2 = '<='
IF @SearchType = 0 Set @SQLSearchStr = @SQLSearchStr + ' OR ((Insured.LastName Like ''%' + @SearchValue2 + '%'') OR (Insured.FirstName Like ''%' + @SearchValue2 + '%'') OR (Insured.MiddleName Like ''%' + @SearchValue2 + '%'')) '
Else If @SearchType = 1 Set @SQLSearchStr = @SQLSearchStr + ' OR ((SELECT CONVERT(VARCHAR(256), DecryptByKey(CONVERT(VARBINARY(256), Insured.SSN))) Like ''%' + @SearchValue2 + '%'') '
Else If @SearchType = 2 Set @SQLSearchStr = @SQLSearchStr + ' OR (Insured.Phone Like ''%' + @SearchValue2 + '%'') '
Else If @SearchType = 3 Set @SQLSearchStr = @SQLSearchStr + ' OR (Insured.WorkPhone Like ''%' + @SearchValue2 + '%'') '
Else If @SearchType = 4 Set @SQLSearchStr = @SQLSearchStr + ' AND (Quote.DateQuoted ' + @CheckSymbol2 + ' ''' + @SearchValue2 + ''') '
Else If @SearchType = 5 Set @SQLSearchStr = @SQLSearchStr + ' AND (Quote.WindowsRecordID ' + @CheckSymbol2 + ' ' + @SearchValue2 + ') ' --Changed from Policy.RecordID
Else If @SearchType = 6 Set @SQLSearchStr = @SQLSearchStr + ' AND (Quote.BinderDate ' + @CheckSymbol2 + ' ''' + @SearchValue2 + ''') '
Else If @SearchType = 7 Set @SQLSearchStr = @SQLSearchStr + ' AND (Quote.LastQuotedDate ' + @CheckSymbol2 + ' ''' + @SearchValue2 + ''') '
End
Set @SQLSearchStr = @SQLSearchStr + ') '
End
/*Else -- no search value passed in
BEGIN
--we're looking for deleted quotes with a search type of 8
If @SearchType = 8 Set @SQLSearchStr = @SQLSearchStr + ' AND (Policy.Deleted) '
Set @SQLSearchStr = @SQLSearchStr + ') '
END
*/
-- Build the basic SQL Statement
IF (@NumberOfRecords > 0)
begin
-- This selects the top number of records necessary to fill our page order
Set @Top = (@RecordIndex + @NumberOfRecords)
--Set @SQL = 'Select Top ' + Cast(@Top as varchar(10)) + ' ROW_NUMBER() OVER (ORDER BY ' + @SQLOrderByStr + ') AS RowNumber,'
Set @SQL = 'Select ROW_NUMBER() OVER (ORDER BY ' + @SQLOrderByStr + ') AS RowNumber,'
end
ELSE
begin
-- We're selecting all the records.
Set @SQL = 'Select '
end
Set @SQL = @SQL + ' Policy.RecordID as PolicyRecordID,
Policy.Locked,
Policy.LockedBy,
Policy.LockedByUserName,
Policy.LockedDateTime,
Policy.Deleted,
Policy.DeletedBy,
Policy.DeletedDateTime,
Insured.RecordID as InsuredRecordID,
Insured.AgencyLocationGUID,
Insured.FirstName,
Insured.MiddleName,
Insured.LastName,
Insured.Phone,
Insured.WorkPhone,
(SELECT CONVERT(VARCHAR(256), DecryptByKey(CONVERT(VARBINARY(256), Insured.SSN)))) AS SSN,
Quote.HasSecondaryData,
Quote.OriginalSaveDate,
Quote.QuoteDescription,
Quote.BinderNumber,
Quote.BinderDate,
Quote.CompanyID,
Quote.SecondaryCompanyID,
Quote.CompanyName,
Quote.CompanyRateRevision,
Quote.SecondaryCompanyRateRevision,
Quote.ProgramID,
Quote.SecondaryProgramID,
Quote.LastQuotedDate,
Quote.QuotedByFirstName,
Quote.QuotedByInitial,
Quote.QuotedByLastName,
Quote.DateQuoted,
Quote.LastQuotedByFirstName,
Quote.LastQuotedByInitial,
Quote.LastQuotedByLastName,
Quote.WrittenByFirstName,
Quote.WrittenByInitial,
Quote.WrittenByLastName,
Quote.CompanyEffectiveDate,
Quote.Bound,
Quote.ExportDate,
Quote.ExportTime,
Quote.WindowsRecordID,
Quote.TemplateDescription
From tblDriver [Insured] (nolock)
Right Join tblAUPolicy [Policy] (nolock) ON (Policy.InsuredLinkID = Insured.RecordID)
Right Join tblQuote [Quote] (nolock) ON (Quote.PolicyLinkID = Policy.RecordID)
where ((Insured.PolicyLinkID = -1) AND
(Insured.AgencyGUID = ''' + Cast(@AgencyGUID as varchar(100)) + ''') AND
(Policy.Deleted = ' + @Deleted + ') And ' +
@SQLTemplateClause +
'(Insured.ProductID = ''' + @ProductID + ''')) '
-- Add filtration to it
IF (@FilterType = 1)
BEGIN
SET @SQL = @SQL + 'AND (Insured.AgencyLocationGUID = ''' + Cast(@LocationGUID as varchar(100)) + ''') '
END
IF (@FilterType = 2) -- User filtering
BEGIN
SET @SQL = @SQL + 'AND (Insured.AgencyUserGUID = ''' + Cast(@UserGUID as varchar(100)) + ''') '
END
-- Add SQLSearchStr
Set @SQL = @SQL + @SQLSearchStr
-- Further modify the SQL var if we are paging the results so only the correct number of rows are returned.
IF (@NumberOfRecords > 0)
Begin
-- This will wrap the selected recordset with a select statement to return the requested page of records.
-- Set @SQL = ' WITH PageResult AS ( ' + @SQL + ' ) SELECT TOP ' + Cast(@NumberOfRecords as varchar(10)) + ' * FROM PageResult WHERE (RowNumber >= ' + Cast(@RecordIndex as varchar(10)) + ') '
Set @SQL = ' WITH PageResult AS ( ' + @SQL + ' ) SELECT * FROM PageResult WHERE (RowNumber >= ' + Cast(@RecordIndex as varchar(10)) + ') AND ' + '(RowNumber < ' + Cast(@RecordIndex + @NumberOfRecords as varchar(10)) + ') '
End
Else
Begin
Set @SQL = @SQL + ' Order By ' + @SQLOrderByStr
End
-- Execute the dynamic SQL. Rem this and unrem the print statement to get the SQL on the console.
Set @SQL = 'OPEN SYMMETRIC KEY PasswordFieldSymmetricKey DECRYPTION BY CERTIFICATE PasswordFieldCertificate;' + @SQL +
';CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey;'
-- Added for debugging of the SQL. Unremarking this will insert the SQL into the error table.
-- insert dbo.tblSQL_Error_Log (UserName, tableName, errorNumber, errorSeverity, errorState, errorMessage, CustomData)
-- values (suser_sname(), 'GetQuoteHeaders', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE(), Left(@SQL, 4000))
Exec (@SQL)
print @SQL
End
END TRY
-- Exception Handling!!!!
BEGIN CATCH
-- Log the exception to the error table
insert dbo.tblSQL_Error_Log (UserName, tableName, errorNumber, errorSeverity, errorState, errorMessage, CustomData)
values (suser_sname(), 'GetQuoteHeaders', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE(), Left(@SQL, 4000))
END CATCH
/*********************************************************************************/
/*********************************************************************************/
At this time I am updating the stp following the suggestions from the "Parameter Sniffing SQL Server" article by declaring local variables and setting them to the passed in parameters. Not certain if it will improve execution but I would like to ensure I have clean code whenever possible.
Thanks again for everything so far!
Jack
November 24, 2009 at 1:40 pm
i've found that the param sniffing issue is usually specific to u setting a param that has a default vaule that it will typically not have. For example i had a start and end date as params to a proc and i was defaulting the them to null although they would never be null as i used to do for most of my params. Well there was a getdate() between @start and @end in my where. Therefore the estimated execution plan was way way off. You can either set them to something locally or give them a default that is more logical. i defaulted mine to 1/1/19000 and it ran much better. Not sure if this is your issue or not because i start to fall asleep about half way through your issue. need more coffee.
November 24, 2009 at 4:06 pm
jmartinez-847815 (11/24/2009)
As for parameter sniffing as a possible cause, the stp on the production machine is actually accepting parameters. NOTE however that I am able to consistently replicate the performance hit by just running the original query in my first post with the values defined in the where clause.
Then, it may be just inappropriate execution plan reuse. Have you tried DBCC FREEPROCCACHE (please, NOT on a production system) to see?
--Jeff Moden
November 24, 2009 at 4:13 pm
Jeff,
Thanks for your reply.
No, I have not tried using that between executions of the query. I will do so in a bit and let you know what I find. For now I need to import an agent list from a flat file into the active agents table. Just want to make certain I have a clean backup before checking (again) that I don't introduce any duplicates... 🙂
Again, thanks for your help.
Jack
November 25, 2009 at 4:56 am
If you don't have any luck with the suggestions so far then you may wish to consider trying one of the following options:
1.Add the LOOP join hint to each of the RIGHT JOINs, e.g. RIGHT LOOP JOIN. This may force SQL Server to perform seeks against your two indexes, but could make performance worse if indexe scans are still used.
2.Consider using a plan guide or the USE PLAN query hint.
Chris
November 25, 2009 at 7:41 am
1) first and most important recommendation: get a performance consultant in to help you analyze and improve the system in general and this query in particular. Especially work to get rid of most of those DTA indexes. I had a client where I was able to remove over 60% of their indexes created by DTA with a very small decrease in aggregate read performance but a HUGE increase in concurrency and DML throughput. DTA is often bad, bad, bad.
2) for paging scenarios like this, especially if they have variable where clauses and conditional joins, I have found a dynamic sql solution to be vastly superior. another trick is to just gather the relevant key columns into temp table first and then make a second pass to get the actual data. WAY faster in many implementations where you are carrying lots of data and/or have complex join issues. Yes, you pay a bit of price for it but get it back in spades over the long haul by avoiding some horrid monster query plans.
3) another option is OPTION (RECOMPILE)
4) yet another option if you have simple and fixed where clause is either index hints or forcing the entire query plan. Both of those will lead to some bad performance if your data values are not VERY evenly distributed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply