Hi. I have a query that shows the warning **Operator used tempdb to spill data during execution with spill level 1**. From what I understand (but correct me if I'm wrong) this is caused by the memory grant being too low, which in turn is caused by the estimated row count being low (see execution plan below). What should I be looking at to try and find out why this is too low? The index has been rebuilt, statistics have been updated. It seems to think it is only going to find one row in the top table for each row in the bottom table. Any help greatly appreciated. Cheers. ![alt text] [xml file] query: SELECT PCRD.GeographyFeatureId, PCRD.TimePeriodId, PCRD.BrandId, PCRD.PerformanceSourceId, PCRD.CategoryId, PCRD.StandardFeatureId, SUM(PCRD.RegistrationVolume), NULL FROM dbo.ProcessedCompetitiveRegistrationData AS PCRD INNER JOIN #CompSegmentationFeatureIds s ON s.SegmentFeatureid = pcrd.SegmentFeatureId WHERE PCRD.GeographyFeatureId = @geographyfeatureid AND (PCRD.TimePeriodId BETWEEN @timePeriodId -12 AND @timePeriodId) AND PCRD.PerformanceSourceId = @performanceSourceId AND PCRD.CategoryId = @CategoryId AND PCRD.StandardFeatureId = @standardId GROUP BY PCRD.GeographyFeatureId, PCRD.TimePeriodId, PCRD.BrandId, PCRD.PerformanceSourceId, PCRD.CategoryId, PCRD.StandardFeatureId : /storage/temp/1806-execplan.xml : /storage/temp/1805-execplan.png **Additional details:** This code is part of a larger stored procedure - the variables are a set in a variety of ways: - @standardId is actually hardcoded in the stored procedure to 1 - @CategoryId is passed into the stored procedure The other variables are set in the stored procedure based on values that are passed in. - @geographyFeatureId is set depending on the value of @var1 which is passed in to the stored procedure - @TimePeriodId and @PerformanceSourceId are set depending on the value of @var2 which is passed in to the stored procedure There are 200 million rows in the table. When I isolate the SELECT the estimate improves, but is still out by around a factor of 4 - Estimated Rows: 11,712 Actual Rows: 43,714. As far as these values vs typical values GeographyFeatureId returns a lot of rows compared to the average rows returned (it returns 3.1 million vs average of 700,000). This PerformanceSourceId value returns fewer rows than average (15 million vs average of 41 million). The other variables return approximately the average number of rows.
Yes the memory spill is caused by the cardinality estimate being too low, hence the memory grant not being large enough. As well as stats being outdated, there are certain query patterns that will also produce this behaviour. Can you share the query with us? Also the actual .sqlplan xml too?