asked
Why does my query plan have a top operator?
Hi I have a query plan that I'm trying to understand (see attachment). I'm confused as to why I see a top operator. There is no TOP in the SELECT. There is no ORDER BY. Also it seems to be selecting TOP (0) rows. I'm also confused why the estimated rows before this operator is 153,000 and after is 552,000 (the actual rows before and after is 68,000). Thanks, Matt ![alt text][1] INSERT INTO #LimitedData SELECT n.GeogId , a.SegId , a.TimeId , a.CatId , SUM(a.Val1 * n.Perc) AS Val1 , SUM(a.Val2 * n.Perc) AS Val2 FROM dbo.Data AS a INNER JOIN #Product AS p ON a.prodId = p.prodId INNER JOIN #Time AS t ON a.TimeId = t.TimeId INNER JOIN #Category c ON a.CatId = c.CatId INNER JOIN #Network n ON a.GeogId = n.GeogId WHERE a.criteria1 = @criteria1 AND a.criteria2 = @criteria12 GROUP BY n.GeogId , a.SegId , a.TimeId , a.CatId; [queryplan][2] [1]: /storage/temp/2318-qryplan.png [2]: /storage/temp/2324-top0query-anon.sqlplan
It has to be related to the aggregation being run. Not being able to see the plan itself and all the properties, that's about all I can tell. I'd also check to see if the optimizer timed out or completed its optimizations. And, based on the complexity of that query, I'd also check your cost threshold for parallelism. I suspect that doesn't need to be a parallel execution.
Hi. Thanks for your reply. When you say the complexity of the query do you mean just this statement – or the entire stored procedure? This is one statement of a relatively simple stored procedure – but that store procedure is nested within another (which is again nested in another). So over all I guess the code is complex – but I wouldn’t say this statement is complex. To look for optimiser time out am I correct in looking for StatementOptmEarlyAbortReason="TimeOut" in the plan xml? Looking at the plan there are some batches that have this– but this particular statement doesn’t have the StatementOptmEarlyAbortReason attribute at all. It has these attributes: StmtSimple StatementCompId="264" StatementEstRows="552231" StatementId="27" StatementOptmLevel="FULL" StatementSubTreeCost="37.6264" StatementText="INSERT INTO #LimitedData The cost threshold for parallelism is currently set to the default value of 5 – I guess I could increase that? Cheers.
Just that statement. It doesn't look complex enough to justify parallel operations. Yeah, the XML or in the properties of the first operator. I would absolutely increase the cost threshold of parallelism. But, it sounds like the query is actually more complex than it seems at 37.6. Feels excessive for that query. I'd know more if I saw the full plan.
Hi - thanks for your reply. I'll try increasing that threshold. I've uploaded an anonymized version of the plan to the original post. Sorry I can't upload the original plan - it's the kind of thing my employer takes a dim view of! (I didn't realise SQL Sentry had an anonymize option until today, otherwise I'd have uploaded it when I first posted). Cheers
Your actuals and your estimates are a bit out of wack. I think that's part of the problem. I would absolutely get the statistics updated. And, because it's doing four different scans, the optimizer is doing a lot of late filtering, probably including the TOP operator, to get down to the 68000 you're returning. Although the optimizer thinks it's return 500,000, hence the extreme high cost. What are you doing with 68,000 rows. People don't read that much data. If you could filter on indexes on these tables it would probably improve. Also, two of the tables are heaps, which is generally an indication of problems in the design. Most tables should have clustered indexes.
17 People are following this question.
