SQL Server Home
SSMS : Execution plan sometimes exceeds 100%
as By Design
4/3/2007 2:13:12 PM
User(s) can reproduce this bug
Several elements in the execution plan are labeled as 100%, in this case the total is > 570%. I have seen this periodically with complex queries against indexed views, but I am not positive indexed views have anything to do with it.
SQL Server 2005 - Enterprise Edition
Win2003 Enterprise Server (SP1)
Operating System Language
Steps to Reproduce
It is difficult for me to provide a repro because it happens on a very large database and with a query that accesses several indexed views with a lot of data behind them.
I have attached an XML showplan that shows this symptom.
I can send a BAK of the database but it has some inter-database dependencies so even that may not be a worthwhile repro.
Several elements in the execution plan are labeled as 100%, so obviously the total adds to more than 100%.
I expect the nodes to be distributed among 100% correctly. I shouldn't have to figure out what percentage of the total is represented by this 100% node or that 85% node.
to post a comment.
Please enter a comment.
on 9/19/2008 at 1:34 AM
Please, document this behavior/anomaly in Books Online. The explanation makes sense, but it's far from what anyone would expect, so it needs explicit clarification.
on 5/28/2008 at 11:58 AM
Thanks again for bringing this to our attention. This anomaly arise in the presence of "row goals" and the concatenation operator. Row goals is the term for when a subtree (perhaps the entire query) is not required to return all possible rows. This most commonly happens when the query has a "top" clause although there are other causes. The anomaly arises because we do not expect to execute the second child of the Concatenation (because we expect the first child to provide all the required rows). Those additional children are costed to assume they return a single row. The only alternative would be to give them 0 cost which would fix the anomaly but probably create other confusions and would lead the optimizer to not care about the plans - we don't want to do that just in case they are executed.
Campbell Fraser, SQL Development.
on 4/10/2007 at 8:40 AM
Thank you for submitting your feedback regrading Showplan showing strange percentages. I've forwarded this on to the showplan team to figure out what is going on.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft