Execution plan percentages don't add up to 100%

Question

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:

+

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 ?

Answer 1

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

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%

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.

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

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

Answer 2

I would not worry about the 100%
The big number are the big number
A lot repeats so start optimizing just one

This is just a subset of your 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
FROM [cal_reg].[dbo].[customer] AS main
WHERE cast(main.DATE AS SMALLDATETIME) = @date
AND upper(main.reffd) IN ( SELECT upper(shname)
                           FROM common.dbo.Password_table )
GROUP BY main.reffd

go with case insensitive collation

full DATETIME will get rid of some overhead
if that is a char field then that is a problem and you should fix the data

DECLARE @date DATETIME

SELECT Reffd AS NAME
     , ( SELECT (  ( SELECT count(*)
                     FROM [cal_reg].[dbo].[customer]
                     WHERE Reffd = main.reffd
                     AND DATE = @date
                   ) 
                   + 
                   ( SELECT count(*)
                     FROM [cal_reg].[dbo].[rep]
                     WHERE Reffd = main.Reffd
                     AND DATE = @date
                   )
                )
       ) AS Completed
FROM [cal_reg].[dbo].[customer] AS main 
JOIN common.dbo.Password_table pw
       on pw.shname = main.reffd
WHERE main.DATE = @date
GROUP BY main.reffd

this can be broken down to joins

DECLARE @date DATETIME

SELECT Reffd AS NAME
     , ( count(cusR.Reffd) + count(repR.Reffd) ) as AS Completed
     , ( count(cusA.[call Attnd]) + count(repA.[call Attnd]) ) as AS Attended
     , ( count(cusR.Reffd) + count(repR.Reffd) + count(cusA.[call Attnd]) + count(repA.[call Attnd]) ) as AS Total
FROM [cal_reg].[dbo].[customer] AS main 
JOIN common.dbo.Password_table pw
       on pw.shname = main.reffd
      and main.DATE = @date
left join [cal_reg].[dbo].[customer] [cusR]
       on cusR.Reffd = main.reffd
      and cusR.DATE = main.DATE
left join [cal_reg].[dbo].[rep]   as [repR]
       on repR.Reffd = main.Reffd
      and repR.DATE = main.DATE
left join [cal_reg].[dbo].[customer] [cusA]
       on cusA.[call Attnd] = main.reffd
      and cusA.DATE = main.DATE
left join [cal_reg].[dbo].[rep]   as [repA]
       on repA.[call Attnd] = main.Reffd
      and repA.DATE = main.DATE
GROUP BY main.reffd

if you have indexes on all the Reffd and Date then this should be smoking fast

those loops are killing it
hopefully this will get rid of some of those loops

this is like one of the knife commercials - but wait there is more

SELECT Reffd AS NAME
     , ( count(cus.Reffd) + count(rep.Reffd) ) as AS Completed
     , ( count(cus.[call Attnd]) + count(rep.[call Attnd]) ) as AS Attended
     , ( count(cus.Reffd) + count(rep.Reffd) + count(cus.[call Attnd]) + count(rep.[call Attnd]) ) as AS Total
FROM [cal_reg].[dbo].[customer] AS main 
JOIN common.dbo.Password_table pw
       on pw.shname = main.reffd
      and main.DATE = @date
left join [cal_reg].[dbo].[customer] [cus]
       on main.reffd in (cus.Reffd, cus.[call Attnd])
      and cus.DATE = main.DATE
left join [cal_reg].[dbo].[rep]   as [rep]
       on main.Reffd in (rep.Reffd, rep.[call Attnd])
      and rep.DATE = main.DATE
GROUP BY main.reffd