Are database query optimizers aware of storage performance differences?

Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan. Paul White provides a great overview of Sql Server's cost based optimizer here:

https://sqlkiwi.blogspot.com/2010/09/inside-the-optimizer-plan-costing.html

Some key points are:

• The optimizer isn't trying to calculate the exact cost of a plan. It's trying to pick the plan with the relative lowest cost between several alternatives.

• It's a simplified view of reality. It assumes a server can perform 320 io/sec and that cpu performance hasn't increased in over a decade.

• Even though servers today have vastly different performance characteristics, the optimizer still does a really good job in the majority of cases.

So, why doesn't Microsoft add some additional intelligence to the optimizer? In the future they might, however, what is more likely are small tweaks to the costs of individual iterators. Currently the benefit isn't there to justify the effort.

You can use undocumented dbcc calls to change some of the query optimizers assumptions. DO NOT USE THESE ON A PRODUCTION SERVER

DBCC SETIOWEIGHT(<multiplier>)
DBCC SETCPUWEIGHT(<multiplier>)

Both have default values of 1. Play with them and see if you can come up with different values that consistently produce better plans in a majority of cases. You'll find that small changes won't change the majority of plans and large changes will generate really bizarre plans.

One additional point is that while SQL doesn't consider io performance when compiling a plan, it does respond to io performance during the execution of the plan (limiting read-ahead reads if io is saturated, etc.)