Column order.

Hi,
I have an index on a table:
Unit, BusDate, Quantity, RetailPrice, UniqueRef, POS, Product

When I analyze a query the DTE recommends index
Product, BusDate, Quantity, RetailPrice, Unit, POS, UniqueRef

So the same fields but in a different order?

When I look at the actual execution plan the existing index is used in an
index scan.

1) Why is the DTE recommending this new index?
2) Is it possible to make it do an index seek on the existing index?

The query is below.
Any help greatly appreciated.
Thanks
Paul


SELECT TOP 1000 T.Unit, T.BusDate, ISNULL(FPT.Translation, TI.Product) AS
Product, TI.Quantity, TI.RetailPrice, CC.CostCenterDesc,
CAST(Cal.FiscalPeriodNo AS int) AS PeriodDesc, Cal.weekNo AS WeekNoOfYear,
Cal.year AS FinYear, TI.RetailPrice / TI.Quantity AS PPL, CC.BrandCode
FROM MS2.dbo.Transactions AS T WITH (NOLOCK)
INNER JOIN MS2.dbo.TransactionItems AS TI WITH (NOLOCK) ON T.Unit = TI.Unit
AND T.BusDate = TI.BusDate AND T.POS = TI.POS AND T.UniqueRef = TI.UniqueRef
INNER JOIN Geography.dbo.CostCentre AS CC WITH (NOLOCK) ON T.Unit =
CC.CostCentreCode
INNER JOIN Calendar.dbo.AXCal AS Cal WITH (NOLOCK) ON T.BusDate = Cal.dayDate
INNER JOIN MS2.dbo.FuelProductTranslation AS FPT WITH (NOLOCK) ON TI.Product
= FPT.FuelType
WHERE (CC.BrandCode IN (1154, 1155)) AND (TI.Quantity <> 0) AND
(TI.RetailPrice <> 0) AND (T.Unit <> 7166001)

Paul,

It is impossible to second guess the decisions without a lot more
information. However, here are some things to note:

Unit - Your first column in the existing index is only give a "not equal"
compare, which means that everything except that 1 value will be included.
It is also used for the join between Transactions and TransactionItems.
However, this has a low selectivity with this query.

Product - The recommended first column is involved in the join between
FuelProductTranslation and TransactionItems. If the selectivity of this
join is such that the optimier would prefer to first join from
FuelProductTranslation to TransactionItems, then it would like a Product
first index.

If these things are true, then another index would be more useful to your
query. The first column in the index should normally be the most specific.
For other queries that you run, the existing index may indeed be the very
best index, but apparently not this time.

You can use query and index hints to try to force a behavior, but I
recommend against it. It usually leads to worse performance.

FWIW,
RLF




"Paul" <Paul (AT) discussions (DOT) microsoft.com> wrote