What's the "cost"?

SQL Server uses a cost-based based optimizer which means that it will assign a "cost" to each execution plan and then choose the plan with the lowest cost.  You can see evidence of this when you look inside an execution plan and see things like "CPU cost" and "subtree cost". 

If you've been around SQL Server for a while you'll know that this cost doesn't translate into seconds or anything meaningful and is just an arbitrary number used to assign a value representing the resource cost.  You might even know that the "cost" value was benchmarked on a machine at Microsoft early in SQL Server's life (probably 7.0) but have you ever wondered what this historic supercomputer looked like?  Wonder no longer, I've attached a picture of it.  Have a look, it'll make you smile :)

I was on SQL Ranger training (now MCA: Database) in Redmond back in May and this question of cost came up at one of the sessions that Lubor Kollar was running.  For those of you that don't know Lubor he owned the development of the SQL Server optimizer for many years and now works in the Customer Advisory Team.  He explained that many years ago a guy worked for him called Nick and it was Nick's job to benchmark query times for the optimizer team.  Those benchmarks became the basis for the cost-based optimizer.

Lubor said that they looked into changing it but it had so many dependancies within SQL Server that it was prohibitively expensive to change.  Nothing would really be gained from changing it anyway as they'd be replacing what is now an arbitrary value for another arbitrary value.  So that is why we still have it today and why you'll see cost values with lots of decimal places; what took 1 sec on the benchmark machine runs a lot faster on your more modern machine.

Next time your looking at a cost in an execution plan spare thought for Nick and his historic machine, it might even make it all seem less complicated now you know its provenance!

Regards,

Christian

http://coeo.com