Month: May 2019

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 B

Hey, 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.