Database Tuning Advisor recommending index on column not r..

Hi,
I have used the DTA on this query...
SELECT TI.Unit, TI.BusDate, TI.Product, TI.PLU, SUM(TI.RetailPrice) AS
RetailPrice, SUM(TI.Quantity) AS Quantity, UTM.TillMap, MAX(TI.BusDate) AS
LastSold, CC.CostCenterDesc, TI.LineIsVoid
FROM Geography.dbo.CostCentre AS CC
INNER JOIN MS2.dbo.TransactionItems AS TI WITH (NOLOCK)
INNER JOIN MS2.dbo.Transactions AS T WITH (NOLOCK)
INNER JOIN Geography.dbo.UnitTillMaps AS UTM WITH (NOLOCK) ON T.Unit =
UTM.Unit AND T.BusDate >= UTM.StartBusDate AND T.BusDate <= UTM.EndBusDate ON
TI.Unit = T.Unit AND TI.UniqueRef = T.UniqueRef AND TI.POS = T.POS AND
TI.BusDate = T.BusDate ON CC.CostCentreCode = T.Unit
LEFT OUTER JOIN MS2.dbo.Voids AS V WITH (NOLOCK) ON T.Unit = V.Unit AND
T.UniqueRef = V.UniqueRef AND T.POS = V.POS AND T.BusDate = V.BusDate
LEFT OUTER JOIN MS2.dbo.PLUMappings AS PM WITH (NOLOCK) ON UTM.TillMap =
PM.TillMap AND TI.PLU = PM.POSPLU
WHERE (V.Unit IS NULL) AND (PM.TillMap IS NULL)
GROUP BY TI.Unit, TI.BusDate, TI.Product, TI.PLU, UTM.TillMap,
CC.CostCenterDesc, TI.LineIsVoid
HAVING (TI.PLU <> '0') AND (TI.BusDate = '20/12/2008') AND (TI.LineIsVoid
<> 1)

and it is recommending this index...

CREATE NONCLUSTERED INDEX
[_dta_index_TransactionItems_20_948198428__K4_K1_K3_K2_K10_K31_K6_K34_5_13]
ON [dbo].[TransactionItems]
( [BusDate] ASC,
[Unit] ASC,
[POS] ASC,
[UniqueRef] ASC,
[PLU] ASC,
[FuelGrade] ASC,
[Product] ASC,
[LineIsVoid] ASC)
INCLUDE ( [Quantity], [RetailPrice]) WITH (SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

What I don't understand is why column [FuelGrade] is part of the index as
this column is not used in the query?

Thanks
Paul

Paul (Paul@discussions.microsoft.com) writes:
> I have used the DTA on this query...
> SELECT TI.Unit, TI.BusDate, TI.Product, TI.PLU, SUM(TI.RetailPrice) AS
> RetailPrice, SUM(TI.Quantity) AS Quantity, UTM.TillMap, MAX(TI.BusDate) AS
> LastSold, CC.CostCenterDesc, TI.LineIsVoid

BusDate is both in a MAX unfunction and also aggregated on? That looks
funny.

> and it is recommending this index...
>
> CREATE NONCLUSTERED INDEX
> [_dta_index_TransactionItems_20_948198428__K4_K1_K3_K2_K10_K31_K6_K34_5_13]
> ON [dbo].[TransactionItems]
> ( [BusDate] ASC,
> [Unit] ASC,
> [POS] ASC,
> [UniqueRef] ASC,
> [PLU] ASC,
> [FuelGrade] ASC,
> [Product] ASC,
> [LineIsVoid] ASC)
> INCLUDE ( [Quantity], [RetailPrice]) WITH (SORT_IN_TEMPDB = OFF,
> IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
>
> What I don't understand is why column [FuelGrade] is part of the index as
> this column is not used in the query?

Maybe the query has been edited since you ran DTA? In any case, running
DTA for a single query may not always be that great. Unless, that is,
this is a core query of your system. DTA is apparently trying to build
a covering indexes, but if you later add another column to the query,
the index will no longer be covering again. And unless the table is
very wide the effect on the query may be limited, whereas on other hand
it may have an impact on inserts and updates.



--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx