question

mjharper avatar image
mjharper asked

Estimated Rows too low.

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][2] [xml file][1] 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 [1]: /storage/temp/1806-execplan.xml [2]: /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.
execution-planrow-counts
execplan.png (30.1 KiB)
execplan.xml (47.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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?
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'd start by isolating just that select on ProcessedCompetitiveRegistrationData with those parameters, even ignoring the aggregate - make the query as simple as possible. And then see what estimates you get. The plan is using values of -how do these compare to typical values in the data? In other words is parameter sniffing skewing the results?
1 Like 1 ·
Many thanks for the reply. I've added those items to the original post.
0 Likes 0 ·
Are those local variables or stored procedure parameters?
0 Likes 0 ·
And how many rows in total does ProcessedCompetitiveRegistrationData have?
0 Likes 0 ·
Hi - many thanks for the replies. I've added some more details to the original post after looking into the things you've mentioned. I think the estimated rows might be based on the average number of rows a GeographyFeatureId would return - is it possible that could be the case?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.