Explain plan - KEY LOOKUP explanation

[fredericofonseca]

SQL Server 2008 R2 64 bit - running a windows 2008 24 CPU's 50GB Ram
Issues below happen also on a smaller spec pc

table with 144 fields
17 million rows
7.5GB data size file

index IX_COMP_movements_PolNum - non clustered
columns
policynumber asc
compoundkey asc
average index row size - 406 bytes

pk clustered
columns
policynumber asc
minorstatperiodnumber asc
movementnumber
average index row size - 50 bytes

tests done when both indexes had near 0% fragmentation

DDL as per below.

Just wondering if I am reading this wrong or if indeed what SQL Server is doing is "incorrect".

By looking at the explain plan it seems that
1- goes to index IX_COMP_movements and then to the datafile to get columns (PolicyNumber,MinorStatPeriodNumber and MovementNumber)
2- using the above output columns goes to index pk_movements and again to datapage to get all other output columns.


I was rather expecting to see just a single index access to IX_COMP_movements as that index contains both columns required to satisfy the where and the order by conditions.


I tried a variation of this table and created the field compoundkey as a normal varchar instead of a computed column, created same index on it, and populated it manually. Resulting explain plan was the same as above so it seems that the fact that the original index was on a computed column made no difference.

Any comments, sugestions or explanation of why it behaves like this appreciated.

CREATE TABLE [dbo].[movements](
 [RecordStatus] [varchar](2) NULL,
 [PolicyNumber] [varchar](8) NOT NULL,
 [MinorStatPeriodNumber] [varchar](6) NOT NULL,
 [MovementNumber] [decimal](5, 0) NOT NULL,
 [RunDateLastTrans] [date] NULL,
 [RunNumberLastTrans] [varchar](1) NULL,
 [NextActivityDate] [date] NULL,
 [NextActivityPriority] [varchar](1) NULL,
 [TypeOfRecord] [varchar](1) NULL,
 [ElementNumber] [decimal](3, 0) NULL,
 [ReassuranceNumber] [decimal](1, 0) NULL,
 [XTable] [varchar](3) NULL,
 [Fund] [varchar](1) NULL,
 [BranchOfRegister] [varchar](1) NULL,
 [MajorSourceProdn] [varchar](7) NULL,
 [PremiumSeries] [varchar](1) NULL,
 [Sex] [varchar](1) NULL,
 [Frequency] [varchar](1) NULL,
 [MethodOfPayment] [varchar](1) NULL,
 [TypeOfLoading] [decimal](3, 0) NULL,
 [TypeOfSuperScheme] [varchar](1) NULL,
 [CurrentSumAssured] [decimal](7, 0) NULL,
 [TabularAnnualPremium] [decimal](9, 2) NULL,
 [AdjustedAnnualPremium] [decimal](9, 2) NULL,
 [BonusAllotted] [decimal](9, 2) NULL,
 [DateBonusCoversTo] [date] NULL,
 [PolicyCount] [decimal](5, 0) NULL,
 [DateRiskCommenced] [date] NULL,
 [DateMatyOrExpiry] [date] NULL,
 [DatePremiumsCease] [date] NULL,
 [BonusMaturityDate] [date] NULL,
 [DateOfBirth] [date] NULL,
 [MovementDate] [date] NULL,
 [MovementCause] [decimal](3, 0) NULL,
 [IfLoadingRefundable] [varchar](1) NULL,
 [TypeOfScheme] [varchar](5) NULL,
 [ReassuranceCompany] [varchar](2) NULL,
 [PremiumInstalment] [decimal](9, 2) NULL,
 [StampDuty] [decimal](9, 2) NULL,
 [StampDutyRegister] [varchar](1) NULL,
 [StampDutyStatus] [varchar](1) NULL,
 [AllocationComnBasis] [varchar](2) NULL,
 [SupnSchemeNumber] [varchar](6) NULL,
 [GroupNumber] [varchar](6) NULL,
 [LifeAssuredClientRef] [decimal](7, 0) NULL,
 [EndOfForfeitureDate] [date] NULL,
 [ElementRelatesTo] [decimal](3, 0) NULL,
 [TableRelatesTo] [varchar](3) NULL,
 [PremiumSeriesRelatesTo] [varchar](1) NULL,
 [InclusionIndicator] [varchar](1) NULL,
 [TabularAnnualExpense] [decimal](9, 2) NULL,
 [ExpenseInstalment] [decimal](9, 2) NULL,
 [CoverExclusionIndicator] [varchar](2) NULL,
 [SystemMovementCause] [decimal](3, 0) NULL,
 [LowestSegmentNumber] [decimal](5, 0) NULL,
 [NumberOfSegments] [decimal](5, 0) NULL,
 [GovtLevyInst] [decimal](9, 2) NULL,
 [SourceOfBusiness] [varchar](4) NULL,
 [BranchCode] [varchar](7) NULL,
 [ReassurancePercent] [decimal](5, 2) NULL,
 [CommissionAmount] [decimal](9, 2) NULL,
 [StampDutyRecoverable] [decimal](9, 2) NULL,
 [IfSmoker] [varchar](1) NULL,
 [ValuationTerm] [decimal](3, 0) NULL,
 [OriginalSumAssured] [decimal](7, 0) NULL,
 [OccupationClass] [varchar](1) NULL,
 [QualifyingPeriod] [decimal](3, 0) NULL,
 [AccidentBenefitFactor] [varchar](1) NULL,
 [AccidentBenefitTerm] [decimal](3, 0) NULL,
 [SicknessBenefitFactor] [varchar](1) NULL,
 [SicknessBenefitTerm] [decimal](3, 0) NULL,
 [GridScore] [decimal](3, 0) NULL,
 [ProductCode] [varchar](4) NULL,
 [IfMulticoverDiscount] [varchar](1) NULL,
 [SourceOfPremium] [varchar](2) NULL,
 [ContributionType] [varchar](1) NULL,
 [FmProcessingDate] [date] NULL,
 [MarginAmount] [decimal](11, 2) NULL,
 [Filler1] [varchar](36) NULL,
 [Filler2] [varchar](15) NULL,
 [CompoundKey]  AS ((CONVERT([char](8),[PolicyNumber],(0))+CONVERT([char](6),[MinorStatPeriodNumber],(0)))+right('00000'+CONVERT([varchar](5),[MovementNumber],(0)),(5))) PERSISTED,
 CONSTRAINT [pk_movements] PRIMARY KEY CLUSTERED 
(
 [PolicyNumber] ASC,
 [MinorStatPeriodNumber] ASC,
 [MovementNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_COMP_movements_PolNum] ON [dbo].[movements] 
(
 [CompoundKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886