Are database query optimizers aware of storage performance differences?

Question

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.

Answer 1

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

Answer 2

The Db2 for LUW query optimizer is aware of the hardware performance characteristics of the machine it's running on and takes them into consideration.

Specifically, each tablespace has two numeric parameters that reflect the underlying storage performance: overhead, which reflects the I/O controller overhead and disk seek and latency time in milliseconds, and transferrate, which indicates the time required to transfer one tablespace page from disk to memory.

These parameters can be specified at the tablespace creation time to override heuristically derived default values.

The I/O performance parameters, along with the cpu_speed database manager-level parameter, are used by the optimizer to calculate I/O and CPU cost of each query plan operator and will therefore affect which plan is ultimately chosen. Subsequently, your scenario would be completely plausible in Db2. Similarly, on a system with very high CPU speed and so-so disk performance the optimizer might prefer CPU intensive operators (e.g. table scan plus sort) to more I/O intensive ones (e.g. index-based table access).

I believe Db2 for z/OS similarly accounts for the underlying hardware performance characteristics, obtaining them from the storage management layer, not as part of the database configuration.