Percentages? Junk. Yes, this right here:
It’s junk. It’s not the amount of time spent, it’s not the proportion of resources used, it’s not the fraction of rows, it’s junk. There’s a more nuanced explanation, but this is my pet peeve dangit, and I’m going on a repro rant.
Behold, two tables with a single row each.
DROP TABLE IF EXISTS dbo.A
DROP TABLE IF EXISTS dbo.B
CREATE TABLE dbo.A (ID int)
CREATE TABLE dbo.B (ID int)
INSERT dbo.A VALUES (1)
INSERT dbo.B VALUES (1)Mess with statistics, saying there are 10000 rows in B, and compare the SELECT *s.
UPDATE STATISTICS dbo.B WITH ROWCOUNT = 10000
SELECT * FROM A
SELECT * FROM BHey, that actual plan has different percentages even though the table scans take the same time with the same rows…
Recreate the tables to clear the bad stats. This query reads 1 row from A, and 0 from B. But they have the same percents!
SELECT TOP (1) *
FROM (
SELECT * FROM A
UNION ALL
SELECT * FROM B
) u
Estimated plans can have problems too. Check this out.
IF (SELECT 1) = 1
SELECT * FROM A
Those cost percentages, despite whatever myth you’ve heard, do not correspond to actual resource usage. What are they? Leftover guesses. SQL Server uses a cost-based optimizer, and those cost numbers come from the optimizer’s guesswork. Get an estimated plan, note the percents, then run with the actual plan, and you’ll see the exact same costs (with usual “It Depends” edge-case disclaimer). They’re leftovers, not even reheated.
And pretty often, you’re looking at a plan because SQL Server guessed wrong. Which means those cost percentages are close to worthless when tuning.
So please, stop it. Percentage misunderstanding is too common, even among Senior DBAs. When we introduce people to execution plans, the best thing to say is that these costs are junk.
OK, I get it. But…what we should look at in the Estimate Plan then? The costs are junk, the percentages are junk, the estimated rows are just estimated…So?
That’s a completely valid question! I think the answer is that we have to be aware that cost percents are the Drunkard’s Search. The first goal should improving our understanding of actual executions. Thankfully, there are details in actual plans that are much more effective, like time elapsed, and SSMS 18 makes this more apparent. Also, I admit this post is a polemic. Costs can help you quickly spot problems like huge scans and missing indexes, you just have to be know what you’re actually working with.
The % cost is “relative to the overall size of the query”, not anm absolute. So in your first case, the 1 row read is 20% of the query. In the second case its clearly confused by the stat you forced (1 of 100000 rows), so it is estimating that the actual data read will be bigger, so the cost of the read (rather than other parts of the execution plan) will be relatively higher.
% costs only a guide to whereabouts in the query to look & a high % is no help for plans that have a low cost.
While I agree that % cost & absolute costs a bit of guesswork, I think your example slightly flawed.