Don't Panic if you see Crazy Cost Percentage in SQL Execution Plan.

I've had situations when very complicated execution plans showed billions of percents for single operations within a query plan. Unfortunately I did not capture these extreme situations and have only pretty modest percentage example. Just up to several million percents:

That is documented bug and very well described by Aaron Bertrand on StackExchange:

http://dba.stackexchange.com/questions/40274/how-does-sql-server-generate-a-query-execution-plan-that-adds-up-to-6-000

In the theory, SUM of all tasks in Execution plan must not exceed 100%, but as you see it is not always the truth.
In this post I try to reproduce that bug in test environment, learn it's dependencies and workaround.

At first will create a test table and fill it wit 4K dummy records.

USE Tempdb
GO
CREATE TABLE tbl_Test
(
Demo_ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text NVARCHAR(450) NOT NULL
);
GO
;WITH SampleData_1 AS (SELECT CAST(0 AS TINYINT) AS SampleData UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
    , SampleData_2 AS (SELECT sd1.SampleData FROM SampleData_1 AS sd1, SampleData_1 AS sd2)
    , SampleData_3 AS (SELECT sd1.SampleData FROM SampleData_2 AS sd1, SampleData_2 AS sd2, SampleData_2)
INSERT INTO tbl_Test(Demo_Number, Demo_Type, Demo_Text)
SELECT ROW_NUMBER() OVER(ORDER BY SampleData) rn, 'T', REPLICATE(N'A',447) FROM SampleData_3;

Then copy-paste following code in SSMS, highlight it, but do not execute. Just click "Display Estimated Execution Plan" or press "Ctrl+L"

IF EXISTS (SELECT * FROM tbl_Test WHERE Demo_Number % 33 = 0)
DELETE FROM tbl_Test WHERE Demo_Number % 33 = 0;

You will get thousands of percents in the bottom line.

Will look why did it happened.
First look at Estimated Cost:
Estimated Subtree Cost for the whole query is 0.0063008.
Estimated Cost for "Clustered Index Delete" is 0.108937, which is exactly 1728.939%  from the cost of whole query.

That means that percentage was calculated correctly, but Estimated Subtree Cost for the whole query was not.

It is obvious why that was happened. As you can see, the query contains two sub queries, but total Subtree Cost is calculated only for the first sub-query, without including costs for the second sub-query.

Now, when we know WHY and HOW the problem happens, will take a look at what is contributing to the additional percentage.

First, will double amount of records by using second part of the first query:

;WITH SampleData_1 AS (SELECT CAST(0 AS TINYINT) AS SampleData UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
    , SampleData_2 AS (SELECT sd1.SampleData FROM SampleData_1 AS sd1, SampleData_1 AS sd2)
    , SampleData_3 AS (SELECT sd1.SampleData FROM SampleData_2 AS sd1, SampleData_2 AS sd2, SampleData_2)
INSERT INTO tbl_Test(Demo_Number, Demo_Type, Demo_Text)
SELECT ROW_NUMBER() OVER(ORDER BY SampleData) rn, 'T', REPLICATE(N'A',447) FROM SampleData_3;

Look at estimated execution plan again:

Percentages are also almost doubled.

That is easy to explain. In order to delete, SQL Server has to deal with higher volume of data, that results in higher Estimated Costs.

Now will try to add bunch of indexes to our table:

CREATE INDEX IX_tbl_Test1 ON tbl_Test(Demo_Number);
CREATE INDEX IX_tbl_Test2 ON tbl_Test(Demo_Type);
CREATE INDEX IX_tbl_Test3 ON tbl_Test(Demo_Text);
CREATE INDEX IX_tbl_Test4 ON tbl_Test(Demo_Text, Demo_Type);
CREATE INDEX IX_tbl_Test5 ON tbl_Test(Demo_Type, Demo_Text);
CREATE INDEX IX_tbl_Test6 ON tbl_Test(Demo_Type, Demo_Text, Demo_Number);
CREATE INDEX IX_tbl_Test7 ON tbl_Test(Demo_Number, Demo_Type, Demo_Text);
CREATE INDEX IX_tbl_Test8 ON tbl_Test(Demo_Type, Demo_Number, Demo_Text);

I'd expect percentages also to rise:

And they increased dramatically!


As the conclusion I'd say:
In case you see crazy percentage values in your Execution query plan:

Everything is OK, that is just SSMS does not show you correct percentages.