Re: DTE recommends an index that already exists but with a different .
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Thu, 15 Jan 2009 16:42:24 -0500
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5144C6C9-6A35-489E-A474-6CDA5F5E361F@xxxxxxxxxxxxxxxx
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)
.
- References:
- Prev by Date: Re: SQL Maintenance Plans and Atomic Backups of a Set of Databases
- Next by Date: Re: Management Studio: Omit "Set" when modifying a proc
- Previous by thread: DTE recommends an index that already exists but with a different .
- Next by thread: SQL Maintenance Plans and Atomic Backups of a Set of Databases
- Index(es):
Relevant Pages
|
