Are database query optimizers aware of storage performance differences?

As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has equal cost. Is that accurate, or is there some knowledge of storage performance that's taken into account?

In a totally contrived example, let's say that my table rows are stored on an SSD drive in my SAN with instantaneous access times, where my indexes are stored on SAS drives that are extremely overloaded, resulting in disk saturation and constant disk queues. When the RDBMS generates the execution plan, is it any more likely to favor a table scan than an index operation (or possibly a skinny index and associated table lookups, as opposed to a covering index, because it's less IO on the SAS disks)?

I suspect the answer is a solid "not a chance is the optimizer that smart or even aware of disk performance", but I just wanted to see if anybody out there knows for sure. I'm using SQL Server, but I'm interested in any database system.

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()
DBCC SETCPUWEIGHT()

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.)

StackExchange Database Administrators Q#40864, answer score: 8