Execution plan percentages don't add up to 100% - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Execution plan percentages don't add up to 100%

sql-server sql-server-2008-r2 add tag

it researcher (imported from SE)

I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. 

But the total of individual query costs adds up to more than 100%. 

This is my query:

    DECLARE @date SMALLDATETIME
    
    SELECT Reffd AS NAME
    	,(
    		SELECT (
    				(
    					SELECT count(*)
    					FROM [cal_reg].[dbo].[customer]
    					WHERE upper(Reffd) = upper(main.reffd)
    						AND cast(DATE AS SMALLDATETIME) = @date
    					) + (
    					SELECT count(*)
    					FROM [cal_reg].[dbo].[rep]
    					WHERE upper(Reffd) = upper(main.Reffd)
    						AND cast(DATE AS SMALLDATETIME) = @date
    					)
    				)
    		) AS Completed
    	,(
    		SELECT (
    				(
    					SELECT count(*)
    					FROM [cal_reg].[dbo].[customer]
    					WHERE upper([call Attnd]) = upper(main.[Reffd])
    						AND cast(DATE AS SMALLDATETIME) = @date
    					) + (
    					SELECT count(*)
    					FROM [cal_reg].[dbo].[rep]
    					WHERE upper([Call Attnd]) = upper(main.reffd)
    						AND cast(DATE AS SMALLDATETIME) = @date
    					)
    				)
    		) AS Attended
    	,(
    		SELECT (
    				(
    					SELECT (
    							(
    								SELECT count(*)
    								FROM [cal_reg].[dbo].[customer]
    								WHERE upper(Reffd) = upper(main.reffd)
    									AND cast(DATE AS SMALLDATETIME) = @date
    								) + (
    								SELECT count(*)
    								FROM [cal_reg].[dbo].[rep]
    								WHERE upper(Reffd) = upper(main.Reffd)
    									AND cast(DATE AS SMALLDATETIME) = @date
    								)
    							)
    					)
    				) + (
    				SELECT (
    						(
    							SELECT count(*)
    							FROM [cal_reg].[dbo].[customer]
    							WHERE upper([call Attnd]) = upper(main.[Reffd])
    								AND cast(DATE AS SMALLDATETIME) = @date
    							) + (
    							SELECT count(*)
    							FROM [cal_reg].[dbo].[rep]
    							WHERE upper([Call Attnd]) = upper(main.reffd)
    								AND cast(DATE AS SMALLDATETIME) = @date
    							)
    						)
    				)
    		) AS Total
    	,'' AS f6row
    	,'' AS f8row
    FROM [cal_reg].[dbo].[customer] AS main
    WHERE cast(DATE AS SMALLDATETIME) = @date
    	AND upper(reffd) IN (
    		SELECT upper(shname)
    		FROM common.dbo.Password_table
    		)
    GROUP BY reffd

And for that I am getting total of 104% as shown below:
 
[![enter image description here][1]][1]
+
[![enter image description here][2]][2]

My question is it possible to solve that more than 100% cost error or are there any other ways from which I can tell my query is running efficiently ?


  [1]: https://i.stack.imgur.com/zrWwb.png
  [2]: https://i.stack.imgur.com/Rr7oM.png

Top Answer

Kin Shah (imported from SE)

The sum of operator costs is more than 100% in execution plan is a [known bug and is closed as by design][1]!

>Posted by Microsoft on 11/17/2010 at 5:28 PM
>
>Thanks for taking the time to file this observation and simple repro. The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.
>
>The concatenation operator has two children - the table scans. However the server expects that it will not be necessary to execute the second table scan. This is because it expects to find a row from the first table scan which would satisfy the "exists" part of the query. Therefore it does not include the cost of the second table scan when computing the total cost of the subtree rooted at the concatenation. However it still estimates and reports the cost of the second table scan, which the management tool the computes as a percentage of the total query cost as if it will be executed.
>
>In this case of course, it would be necessary to run the second table scan since the first is empty. However the engine operates on the conservative assumption that there will always be at least one row (which may only have been added after the query was compiled).
>
>Campbell  
>SQL Development

Aaron Bertrand filed a similar bug - [SSMS : Execution plan sometimes exceeds 100%][2]

>Posted by Microsoft 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.

If you want to understand [how plan costing works, Paul White explains it at his best here.][3]

From the query processor team - [What’s this cost?][4]

General guidelines for better performance -

 - Have proper indexes & up-to-date stats.
 - Fetch data that is required ONLY along with relevant columns.
 - Have proper schema.table with proper indexes & datatypes 
 - Use statistics time, IO before and after you make changes to measure the gain.
 - Get help / guidance from experts at this site :-)

That's why I use [SQL Sentry's FREE Plan Explorer][5]

  [1]: https://web.archive.org/web/20131118011039/http://connect.microsoft.com/SQLServer/feedback/details/621330/sum-of-operator-costs-is-more-than-100-in-the-execution-plans
  [2]: https://web.archive.org/web/20131119063847/https://connect.microsoft.com/SQLServer/feedback/details/267530/ssms-execution-plan-sometimes-exceeds-100
  [3]: https://sqlkiwi.blogspot.com/2010/09/inside-the-optimizer-plan-costing.html
  [4]: https://blogs.msdn.microsoft.com/sqlqueryprocessing/2006/10/11/whats-this-cost/
  [5]: https://www.sentryone.com/plan-explorer

1 Answer