Cost

As soon as I saw the title of Jonathan Lewis' post, I had an inkling of what it might have to say and I wasn't too far off the mark. Although I don't disagree with a single statement of his post (I've read it a few times to make sure), I tend to take a different line at work because I keep finding myself in conversations with perfectly professional, bright and knowledgeable people that go something like this

Them: 'So it's picking this new plan and it's much slower than the old plan, but the new plan has a much lower cost.'

Me: 'Of course it has a lower cost. That's why it's picked it. That's what a cost-based optimiser does.'

Them: 'But it's slower'

Me: 'Well spotted'

Them: 'Well then why is the cost lower?'

Now at this point there are diverging paths you could take and it's utterly valid to find out *why* the calculations haven't delivered the best plan. The cost *is* the result of a calculation and not an arbritary number plucked from the air. The optimiser selected the 'bad' plan because it had the lowest cost.

To quote Jonathan

'Cost IS time - but only in theory.'

However, it's equally valid to say, I don't care why the cost is wrong, I *know* the plan that performs most efficiently and that's the plan I want the server to use. In fact, I'd suggest that *most* SQL performance problems I look at have at their very core a plan with a lower cost that doesn't deliver the best response time! My users couldn't care less about optimiser calculations. (I use the cliche carefully - they probably don't care at all.)

What isn't easy for me is to watch people spend a day aiming for the lowest cost, brandish it at me proudly and then be disappointed when it runs more slowly than the previous version!

Them: 'But look at the cost!'

Me: 'Who cares? It's slower.'

My take on it is this. Cost is the output of a model designed to deliver the lowest execution time but sometimes the model gets things wrong and, in the face of actual run times, I'll take reality over the model every time. When people arrive at my desk (perhaps electronically) with a SQL performance problem, one of the first things I say if the conversation begins like that one above is

"Ignore the cost!"

Jonathan's telling the truth and the true answer to a question is important, as long as you understand what the original question was. I know from experience that many people react to the cost without understanding how the optimiser arrives at that answer. Of course, it could be that I happen to see the problem queries, where the optimiser calculation isn't working out too well, but I come across plenty of them and it would save a lot of time if people didn't spend so long arguing about costs!

Cost is a fundamental metric if you care about the CBO, develop it, write about it (Jonathan) or are trying to work out what the hell it's doing (anyone who has ever looked at a 10053 trace in desperation). But as a performance metric? I'm not sure it's very important at all.

How about time?

P.S. No more blog posts for a while now. That Hotsos presentation looms large. Although, now I've said that, I'll probably contradict myself ....

P.P.S. I should say that I've missed the main thrust of Jonathan's post and picked up on the bit that suited me. Really, it was about this ...

"As I’ve pointed out in the past, “Cost is Time”. The cost of a query represents the optimizer’s estimate of how long it will take that query to run – so it is perfectly valid  to compare the cost of two queries to see which one the optimizer thinks will be faster but, thanks to limitations and defects in the optimizer it may not be entirely sensible to do so.

The point I want to address in this post though is the comment that 'it’s valid to compare the cost of different plans, but not to compare the cost of two different queries'."

'Valid' and 'sensible'. I liked that and I hope it's what people absorbed.

[Late update: I happened to look at AskTom today. I don't look as often now that the pace of interesting follow-ups has slowed down. That's no bad thing. Lots of things have been answered (for now) and I still search on old threads constantly. Anyway, I should have known that this subject would crop up there.]