אוג18
Written by:
ronen ariely
18/08/2020 21:04 
Good day guys,
Hope you are having a great day.
If you speak Hebrew by the way, then you can join me to my lecture on Sunday. It should be very interesting and hopfully valuebale for practical cases as well.
Today I want to discuss an issue which was asked in the MSDN forums and in the Microsoft QnA forum and it is still open. It is not something that asked daily but from time to time I come across this question in a forums. Usually it start with someone who claim that SQL Server Execution Plan shows that the cost of a specific operator or the sum of operators is more than 100 percentage.
Obviously this make no sense and it raise the question: what is the source of the bug? The discussions usually get answers that this is related to the SQL Server Execution Plan, while the truth is that this has nothing to do with the server side, but the client tool which calculate the percentages of the cost.
In this post I will summarize a bit more information and explain how we can be sure that this issue is indeed in the client side and not a result of information which come from the server (at least the cases I am familiar with).
Let's go over this discussion in the MSDN forum for example:
https://social.msdn.microsoft.com/Forums/azure/en-US/7489cc49-3587-481e-adf9-3b52c9063e35/cost-in-execution-plan-summing-up-more-than-100-its-some-where-140?forum=sqldatabaseengine
I want to start with one of the responses given in the discussion, since this is the common answer people get in such issue.
>> SSMS only displays what the SQL Server returns.
This is actually the opposite (at least the issue which I am familiar with). The percentage Cost is calculated in the client side, so this is exactly an issue in the SSMS side usually.
There were several times in the past where issue like this happened in the past and it was fixed in the SSMS.
1. You can check the text of the Execution Plan which the server returns and notice that it includes values for TotalSubtreeCost and for StatementSubTreeCost. These values in the execution plan are used to calculate the percentages. Notice that you will not find the percentage values in the EP XML (This proof the issue is in the client side).
2. You can use other tools like the free version of "sentryone plan explorer". In many cases you can notice that this tool returns different values than these returned by the SSMS or ADS. In the past, when I had this issue then it did not happened in the sentryone App, which returned the right values (This proof the issue is in the client side).
3. If we are executing several batches using "GO" after each statement, then the SSMS send these batches separately and from the server "point of view" these are not related. The server will create and cache a separate execution plan for each batch.
If we execute several statements under the same batch (using "GO" after several queries for example in SSMS), then the statements in the batch are compiled into a single execution plan.
Let's examine "executing multiple queries in a single batch" vs "executing the same queries in separate batches"
Create a simple table named T
CREATE TABLE [dbo].[T](
[id] [int] NULL,
[txt] [nvarchar](10) NULL
) ON [PRIMARY]
GO
Clear the cache
and Execute the following two queries in the same batch :
select * from T WHERE id > 0
select * from T WHERE id > 1
GO
Check The Execution Plan which the server cache directly using dm_exec_cached_plans.
Note! DO NOT USE THE SSMS GUI -> "Show Execution Plan XML"
select CP.*, T.*, Q.*
from sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) Q
where CP.objtype = 'Adhoc'
GO
Notice that SQL Server compiles only one execution plan for all the queries inside the same batch.
You can repeat the same test but this time execute the queries separately
select * from T WHERE id > 0
GO
select * from T WHERE id > 1
GO
Notice that now the server cache two separate execution plan
SO why this is relevant?
In the case that we execute (send the server) multiple batches using the SSMS, you can notice that the SSMS compare the cost of the queries even so they were sent to the server separately. This must be done in the client side since the server got and execute each batch separately (This proof the issue is in the client side).
------------ More information -------------
- Notice Joe Sack respond, which confirm the issue is in the presentation on this bug report: "This is a known plan presentation issue"
- Notice Denny Cherry respond on the same issue claiming that "The visual cost estimator is crap", which I would not say crap but it is not something we should count on the numbers as accurate.
- Notice the respond of wBob in this thread, where he also explicitly recommend to use a different client tool which explicitly recognize this issue as related to the client tool and not the server. If I am not mistaken (not sure) then wBob is Bob Ward from Microsoft and in this case, he is the right person to respond.
- Nice post written by Randy Dyess where he explain a bit more on the TotalSubtreeCost parameter in the Execution Plan.
------------ Conclusion ------------
In most cases SSMS presents the percentage right but there might be cases where the values are not correct. When the values go above 100%, then it is simple to catch the issue bit this might happen in other cases as well.
Therefore, we should consider these number as estimated and not count on the presented percentage, if there is a dilemma!
The values which we should count on are (1) The TotalSubtreeCost and for StatementSubTreeCost values from the Execution Plan, or (2) The statistics IO and statistic TIME - SET one of these ON each time to get the value for each execution.
If you encounter an issue which you can guide us on how to reproduce it then you should open a bug report, since Microsoft developers might be able to fix it probably in future versions.
💥 I hope this was useful 🙄