Q:

How to speed up this TSQL query?

I have a TSQL select query that is running "slow"
SELECT 
    CustomerKey
    ,ProductKey
    ,RepresentativeKey
    ,ReportingDateKey   
    ,SUM(i.InvoiceQuantity) AS InvoiceQuantity
    ,SUM(i.InvoiceQuantityKg) AS InvoiceQuantityKg
    ,SUM(i.BrutoInvoiceLineAmount) AS BrutoInvoiceLineAmount
    ,SUM(i.EndOfYearDiscount) AS EndOfYearDiscount
    ,SUM(i.NettoInvoiceLineAmount) AS NettoInvoiceLineAmount
    ,SUM(i.TotalLineCostPrice) AS CostPrice
    ,SUM(i.MarginAmount) AS MarginAmount

FROM FactInvoices i

WHERE 
    i.DossierKey =2
    AND i.ReportingDate BETWEEN '2016-01-01' AND '2017-12-31'
GROUP BY
    CustomerKey
    ,ProductKey
    ,RepresentativeKey
    ,ReportingDateKey

I'm running the query in SSMS 32bit.
Execution time is 17-21s, I have tested adding non clustered indexs on DossierKey and ReportingDate, but this is only slowing down the query.
The table has about 6.04M record and this result set is giving back 1M records.
It's running on SQL 2016 Developers edition.
Server specs: 8core 16gb ram and HDD => Virual server.
Looking at the execution plan, I can't find any improvements.
How do I speed up? More hardware? But I don't think that will help because the server is not fully used when running this query.
Edit:
Execution Plan:

Index:
CREATE NONCLUSTERED INDEX [_dx1]
ON [dbo].[FactInvoices] ([DossierKey],[ReportingDate])
INCLUDE ([CustomerKey],[ProductKey],[ReportingDateKey],[RepresentativeKey],[InvoiceQuantity],[InvoiceQuantityKg],[BrutoInvoiceLineAmount],[NettoInvoiceLineAmount],[MarginAmount],[EndOfYearDiscount],[TotalLineCostPrice])

Thanks.

A:

For this query:
SELECT CustomerKey, ProductKey, RepresentativeKey, ReportingDateKey,
       . . .
FROM FactInvoices i
WHERE i.DossierKey = 2 AND
      i.ReportingDate BETWEEN '2016-01-01' AND '2017-12-31'
GROUP BY CustomerKey, ProductKey, RepresentativeKey, ReportingDateKey;

I would recommend an index on FactInvoices(DossierKey, ReportingDate, CustomerKey, ProductKey, RepresentativeKey).  The first two are the primary elements of the index used for the WHERE clause.  The remaining three columns may be useful for the aggregation.  You could also include all the additional columns used in the query.

