What index should I use for a read-only database?
You said, that TimeElapsed is not really filtering much, so there is no point in indexing it.

Just looking at the query structure, I'd add an index to Parameters table on
(ExogDexCurve, ControlRange, SensorError). The order of columns is important.

I would also make sure that all indexes have Fill Factor=100, because the data is static.

But, overall, the main bottleneck is reading all Xa values for the needed ControlRange and SensorError.

The server has to read all this data. Storing it in columnstore index should help.



I would also consider denormalizing the data and making just one table (persisting the join). In this case I'd create a covering index. The order of columns is important.

(ExogDexCurve, ControlRange, SensorError, Xa) INCLUDE (TimeElapsed)


Xa is part of an index rather than included, because you order by Xa.

With one joined table and this covering index the query should scan the index without extra sorts.

Also, to "hide" the persisted joined table it may be possible to create an indexed view of the join instead of explicit table, but I'm not sure.