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

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.

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: