Microsoft KB Archive/321093

= FIX: SEEK Uses Only First Column of Compound Index Which Results in a Sub-Optimal Plan =

Article ID: 321093

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q321093



BUG #: 357032 (SHILOH_BUGS)



SYMPTOMS
The optimizer may choose a plan that performs an index seek on only the leading column of a multicolumn index even though it can generate a plan that also seeks by using the remaining index columns. This behavior can result in sub-optimal query performance.



RESOLUTION
The English version of this fix should have the following file attributes or later:   Date         Time         Version      Size              File name -

04-10-2002  6:33:00 PM   8.00.0608    7,454,801 bytes   Sqlservr.exe NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.



MORE INFORMATION
Here is an example of the problem and what you may observe in the query. The MyTab2Idx index is defined on SessionID, ObjectGUID and ObjectType. However, in the following plan, the SEEK on the MyTab2Idx index only uses SessionID. Note that the criteria in the query includes other columns in the SEEK predicate: FROM MyTab1 MT1 INNER JOIN MyTab2 MT2 ON                ( MT1.RightsGUID = MT2.ObjectGUID          AND MT2.SessionID = 1022802101736715                  AND MT2.ObjectType = 501

This query plan shows that the SEEK is only on SessionID: |--Clustered Index Seek(OBJECT:([repro].[dbo].[MyTab2].[MyTab2Idx] AS        [MT2]), SEEK:([MT2].[SessionID]=1022802101736715),          WHERE:([MT2].[ObjectType]=500) ORDERED FORWARD) Here is how the plan will look if the SEEK uses all the columns in the predicate: |--Clustered Index Seek(OBJECT:([repro].[dbo].[MyTab2].[MyTab2Idx] AS [MT2]), SEEK:([MT2].[SessionID]=1022802101736715 AND [MT2].[ObjectGUID]=[MT1].[EntryRightsGUID] AND [MT2].[ObjectType]=501) ORDERED FORWARD)

Keywords: kbbug kbfix kbsqlserv2000presp3fix kbsqlserv2000sp3fix KB321093

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.