Covering index & Aggregate
I'm doing some perfomance analysis to my application and SQL Server 2005.
I'm inserting 400k rows, doing 20k selects (one for each 20 inserts). The Edition 1 has a non-unique clustered index (UserId, ProductId, BatchId, LocationId), the Editon 2 has a non-clustered index (UserId, ProductId, BatchId, LocationId, Quantity) in addition to the clustered one (same as the edition 1 got).
I'm testing this because of the articlehttp://www.sql-server-performance.com/covering_indexes.asp says this could help aggregate functions alot. I'm doing a SUM().
The SQL I'm executing 20k times is:
SELECT ProductId, BatchId, LocationId, SUM(Quantity) AS Quantity, COUNT(*) AS Lines
FROM Logistics
WHERE UserId = [client id] AND ProductId IN ([product list])
GROUP BY ProductId, BatchId, LocationId
The problem is that Edition 2 is about 1-2 ms slower, even when the inserts has no differences (they are about equal). The Execution Plan says Editon 2 should perform better (less IO and CPU operations) since it has a Covering Index.
Why is it like this? I can't find any logical explenations to this problem. Can the reason be that the Edition 2 has bigger defragmentation problems than Edition 1 since it's a non-clustered index?
