Modify the IO cost computation formula in MS SQL Server to reflect negligible SSD seek-times?

Question

The query optimizer of MS SQL Server seems to use a fixed formula for estimating IO costs. This formula seems to be based on a fixed value, reflecting the seeking time of a magnetic hard disk, plus a variable amount of costs depending on the amount of consecutive blocks being read. Given a read of only one or two blocks, the computed fixed costs for seeking are vastly higher then the computed costs for reading the blocks.

Joe Chang did an examination of the cost structure in 2002 in this article: http://www.qdpma.com/CBO/s2kCBO_2a.html

Paul White examined the topic in 2010 here: https://www.sql.kiwi/2010/09/inside-the-optimizer-plan-costing.html

This cost computation results in the query optimizer switching from index seek to index scan for certain queries in cases where sufficiently much random access would occur. If cardinalities are appropriate, the optimizer computes that there would be so much time spend for disk seeking that sequentially reading the whole table (or huge ranges of the table or index) would be faster.

When running SQL Server on a SSD, the IO cost computation is getting unrealistic due to SSDs having an almost negligible seeking time (microseconds instead of milliseconds). I have created a simple environment to show this. In creates three tables and a query resulting in a sufficiently sparse access to the key values. You can run the first SQL (below) once in a database named "reprod", and the second SQL multiple times with varying upper bound on the where clause (... and T1.id1<X). Remark to not modify the query by using a parameter, else because of parameter sniffing one cannot reproduce the behavior - change in execution plan - as described below.

On my machine it results in the following statistics:

execution time (ms)	limit	total subtree costs	table hint
32	id1<89	15,08132	none
382	id1<90	15,1062	none
35	id1<90	15,2106	forceseek

The numbers show a significant increase in execution time moving from 89 to 90. Examining the profiler output shows that for id1<90, the plan changed to using an index scan instead of an index seek. The total subtree costs however changed only very slightly. Using forceseek shows the optimizer now thinks that using an index seek produces more IO costs than an index scan.

My explanation is that the query optimizer is assuming disk seeking times based on a magnetic hard disk as mentioned in the introduction of this question. Doing random reads of one block would result in the reading time for SSD being vastly overestimated (red - query optimizer) in comparison to reality (green). The jump is visible in the table above.

In our environment there are lots of bad execution plans involving full table or index scans. We know for sure (by testing) that using an index seek would result in vastly less execution time (up to a factor 1/100, meaning 100ms instead of 10s).

The question now is: how to have SQL server properly reflect the negligible seeking time of an SSD and what other workarounds (a few listed below) do exist?

Possible workarounds:

• using plan guides (however would create the more work, the more huge tables exists an the more different types of queries are run against each table)
• use forceseek (in our working environment this would need to be injected when coming from Entity Framework, propably using an DbCommandInterceptor - maybe there are other methods?)
• "dbcc setioweight" could be played with, but had no effect on my side since, I guess, it only scales the whole IO cost estimation and would result in a trade off to queries including more CPU time, not different ways of accessing the indexes.

I guess others are also affected by this behavior - maybe even without noticing it, because SSDs usually also come with a higher sequential read rate. If there is sufficient awareness of the problem, maybe a configurable option will get introduced so we can set another value for the fixed cost component in the IO cost computation/estimation.

    -- reprod-1-create.sql

    use reprod;
    drop table T3;
    drop table T2;
    drop table T1;
    go
    create table T1(id1 int identity(1,1) primary key, crc int);
    create table T2(id2 int identity(1,1) primary key, id1 int not null, crc int, foreign key(id1) references T1(id1) )
    create table T3(id3 int identity(1,1) primary key, id2 int not null, crc int, foreign key(id2) references T2(id2) )
    create index IX_id1 on T2(id1) include(crc); alter index IX_id1 on T2 DISABLE;
    create index IX_id2 on T3(id2) include(crc); alter index IX_id2 on T3 DISABLE;
    go
    insert into T1(crc) values(1);
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    insert into T1(crc) select crc from T1
    go
    update T1 set crc = checksum(str(id1)) where id1 > 0;
    go
    
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(crc)) from T1 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    insert into T2(id1,crc) ( select id1, checksum(str(id1)+str(id2)+str(crc)) from T2 )
    
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(crc)) from T2 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    insert into T3(id2,crc) ( select id2, checksum(str(id2)+str(id3)+str(crc)) from T3 )
    
    alter index IX_id1 on T2 rebuild;
    alter index IX_id2 on T3 rebuild;
    select count(*) as NrRowsT1 from T1;
    select count(*) as NrRowsT2 from T2;
    select count(*) as NrRowsT3 from T3;

    -- reprod-2-execute.sql
    use reprod; set statistics time on; set statistics profile on;
    select  count(T3.id3) from T1
    inner join T2 on T2.id1 = T1.id1
    
    inner join T3 --with(forceseek)--with(index(IX_id2))
    
    on T3.id2 = T2.id2
    where abs(T3.crc) < 2147483--6--4--7
    
    and T1.id1<90 -- 89 = index seek, 90=index scan
    
    set statistics profile off; set statistics time off;

Answer 1

No, you can't modify the cost formulas or components. Well, ok, yes you can if you know where the numbers are stored in memory, and are prepared to edit them, but no one is going to do that on every restart and they'd be in violation of their licence agreement anyway. Undocumented things like IOWEIGHT aren't the way either.

The SQL Server model never really attempted to model each customer's specific hardware. The model used some numbers that were loosely based on basic realities at the time. Importantly, the chosen model as a whole tended to generate reasonable plans for most workloads most of the time. Given the vast difference in hardware, SQL statements, schema 'designs' and whatnot, this isn't a bad outcome, and a reasonable compromise.

Now, other vendors have gone down the route of providing more and more tuning knobs, parameters, and options in an attempt to allow customers to adapt the database engine to their particular workload. That is a reasonable approach, and I'm sure there have been successes with that.

One of the nice things about fixed numbers and a (relatively) simple model is it makes it easier to reproduce an issue on a different system. Supportability is perhaps boring, but it is a real consideration. Equally, making changes to optimizer behaviour is easy -- there are never any shortages of suggestions or ideas -- but for every query that benefits, there might be another serious regression. Predictability is very important to some customers, hence compatibility levels and the like.

Now the 'simple' model isn't really as simple as all that. There have been undocumented tweaks and additions over the years that I would say are pretty firmly not 'simple' at all. One could go on forever looking to account for processor speeds, hardware developments, the relative benefits of large I/O, chances of a previously-fetched page remaining in memory for the lifetime of the query, read-ahead and prefetch effectiveness...and so on.

SQL Server seems to be moving in a different direction over recent releases. In particular, the Intelligent Query Processing features are more and more leaning toward runtime feedback rather than providing new tuning levers. Feedback means learning from the current and previous executions to adjust things like memory grants. You can expect future work in this general area. The seek/scan tipping point might well be one of those things.

In the meantime, what to do? Well, the majority of queries and systems manage to get by with the default behaviour. If you find a class of query that routinely needs hints to perform adequately, then perhaps you need tooling that allows you to apply those hints more easily. That might mean using stored procedures, query store, or advocating for changes to your data abstraction tool of choice.

It might equally mean providing feedback to the Microsoft SQL Server team through your usual support avenue to advance the case for future product features. It might also mean changing something about your own designs and practices to avoid the issue in as many cases as possible.

There are certain trace flags that can provide relief in particular edge cases, where no other solution is feasible. Those are normally only provided by Microsoft after a detailed investigation as part of paid support.