Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
MySQL's optimizer is unaware as well. The storage could be disk, ssd, network-over-33.6kbps-connection, whetever. Optimizer has no clue. –  ypercube Apr 25 '13 at 6:22
2  
Oracle generates "system statistics" which measure (amongst other things) the latency (and performance) of the disk access and includes those values into the plan. For Postgres you can manually set a scale on how "expensive" certain IO operations which is also used by the planner. –  a_horse_with_no_name Apr 25 '13 at 6:35
add comment

1 Answer 1

up vote 2 down vote accepted

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: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx

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

share|improve this answer
    
This is great information - thanks! It confirms the suspicions I had, and those two DBCC commands have been fun to play with on a sandbox machine I have :) –  SqlRyan Apr 26 '13 at 18:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.