Home Dashboard Directory Help
Search

Add config setting to adjust the cost of random and sequential IO by xor88


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Suggestion
ID: 652362
Opened: 3/19/2011 3:52:03 PM
Access Restriction: Public
0
Workaround(s)
view

Description

The cost of a disk seek cannot be assumed to be relatively constant across servers. SSDs, SANs and fully in-memory databases offer much higher io/s rates than servers with few drives offer. Therefore we need a per-instance or per-database setting to tell the query optimizer how many disk seeks and how many MB this server can read per second. This setting is currently available as an undocumented per-session setting (not usable in production).

This setting could surface as the following sql code:

alter database xyz set sequential_io_rate = 120 --250mb/s
alter database xyz set random_io_rate = 10000 --ssd can read 10k small blocks per second

This feature could also be used to tell the query optimizer that the database is expected to be fully in memory at all times:

alter database xyz set sequential_io_rate = 1000 --1gb/s
alter database xyz set random_io_rate = 100000 --ram can probably provide at least 100k "seeks" per second including overhead

The value of this feature lies in the fact that the optimizer can now choose the optimal plan considering the actual hardware and not some kind of "canonical server hardware". With SSDs plans with many seeks become more viable.

Details
Sign in to post a comment.
Posted by Microsoft on 1/25/2012 at 4:31 PM
Thank you for submitting this suggestion, but given its priority relative to the many other enhancements we are considering, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look. For this kind of thing we prefer automatic solutions rather than low-level hints.

Thank you,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by xor88 on 3/23/2011 at 4:18 PM
Great idea! There could be a calibration tool that stresses the io system for a few minutes (and it could be run during installation for a few seconds).
Posted by Microsoft on 3/23/2011 at 3:50 PM
Thanks for the feedback. We'll consider addressing this for a future release. I can tell you that we'd rather make it fully automatic than to give a knob to allow developers and DBAs to control it. But I agree its a good idea for us to evaluate our I/O cost calibration in the next major release.

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by xor88 on 3/19/2011 at 3:54 PM
There could also be a per-table setting that behaves just like the proposed per-database setting but just for a particular table. That way DBAs could set the io rate on small tables to be very high because they are likely to be fully cached anyway.
Sign in to post a workaround.