Why is it doing a Eager Spool followed by a Lazy Spool?
The lazy spool caches the result of the subtree below it, in case the correlated parameter of the nested loop join (SalesDate) does not change between nested loop iterations.
Reading a cached result from the lazy spool will be cheaper than reevaluating the Filter and re-seeking the index built by the eager spool.
DECLARE @StartDate DATE, @EndDate DATE; Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate) from dbo.PeriodCalendar_Weeks pcw where (pcw.Year = @Year and pcw.Period < @Period) or (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week) or (pcw.Year = @Year -1 and pcw.Period >= @Period); SELECT WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)), Store, DeliveryChargesTotal = dct FROM ( SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, SUM(DeliveryChargesTotal) FROM dbo.Daily_GC_Headers WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0 GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store ) AS x (SalesWeek, Store, dct) ORDER BY WeekEndDate, Store;
A filtered index may help, if many rows exist where isCanceled = 1 (these are just possible suggestions, depending on cardinality of Store, and may not be the most optimal):
CREATE INDEX x ON dbo.Daily_GC_Headers (SalesDate) INCLUDE (Store, DeliveryChargesTotal) WHERE isCanceled = 0;
If there are very few rows where isCanceled = 1, this may be better:
CREATE INDEX x ON dbo.Daily_GC_Headers (SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);
Both are worth trying on a test system, as well as moving Store into the key in either case, or moving IsCanceled to the INCLUDE list in the latter case. On my system, I found the best results with everything but the date in the INCLUDE list:
CREATE INDEX x ON dbo.Daily_GC_Headers (SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);
Again, you will need to test if any of these work out, or if the query above gives a different/better recommendation directly from SQL Server.