Can I prevent SQL Server from consolidating histogram steps in statistics?

Since I am running SQL Server 2008, I cannot restore your database.

However, I was able to reproduce your "problem". See script below.

In your original post, you mention that execution plan for the date range >= 2013011500 AND < 2013011600 is "abysmal", but I don't see why that should be the case for your example.

The optimizer will choose a Loop Join if the estimate for the dimension table is 1 row. If that estimate is accurate, then the Merge Join cannot outperform the Loop Join; the Loop Join will always be faster or about as fast as the Merge Join.

If the estimate is not accurate, then it really depends on the indexes on the FactTable. If there is a clustered index (or any other covering index) that starts with DatumID, then you shouldn't see any large performance differences.

Please verify if the script below accurately portrays your "problem". When I run it, I get (about) the same amount of logical reads and the same order of magnitude for the CPU time.

create table justnumbers(number int primary key)
insert into justnumbers values (0)
declare @i int
set @i=1
while @i<500000 begin
  insert into justnumbers select @i+number from justnumbers
  set @i=@i*2
end
create table DimDatum (DatumID int constraint pk_dimdatum primary key, LastVersion tinyint)
insert into dimdatum select top 555 cast(convert(char(8),dateadd(day,number,cast('20120101' as datetime)),112) as int)*100+case when number=382 then 4 else 2 end,1 from justnumbers
create table FactTable (DatumID int, Risikofaktor1ID int, Risikofaktor2ID int, SomeFacts char(50),constraint pk_facttable primary key(datumid,Risikofaktor1ID, Risikofaktor2ID))
insert into facttable select top 352728 2013010702,number / 100,number%100,'bla' from justnumbers
insert into facttable select top 270085 2013011402,number / 100,number%100,'bla' from justnumbers
insert into facttable select top 349154 2013011502,number / 100,number%100,'bla' from justnumbers
insert into facttable select top 431171 2013011602,number / 100,number%100,'bla' from justnumbers
insert into facttable select top 268745 2013011704,number / 100,number%100,'bla' from justnumbers
go
set statistics io on
set statistics time on
go
-- merge join
SELECT f.DatumID,f.SomeFacts,dd.LastVersion
INTO #t FROM dbo.FactTable f
INNER JOIN dbo.DimDatum AS dd
ON  f.DatumID = dd.DatumID
    AND dd.LastVersion = 1
WHERE
 dd.DatumID >= 2013011600 AND dd.DatumID < 2013011700
OPTION(RECOMPILE)
go
drop table #t
go
-- loop join
SELECT f.DatumID,f.SomeFacts,dd.LastVersion
INTO #t FROM dbo.FactTable f
INNER JOIN dbo.DimDatum AS dd
ON  f.DatumID = dd.DatumID
    AND dd.LastVersion = 1
WHERE
 dd.DatumID >= 2013011500 AND dd.DatumID < 2013011600
OPTION(RECOMPILE)
go
drop table #t
go
-- force a merge join
SELECT f.DatumID,f.SomeFacts,dd.LastVersion
INTO #t FROM dbo.FactTable f
INNER JOIN dbo.DimDatum AS dd
ON  f.DatumID = dd.DatumID
    AND dd.LastVersion = 1
WHERE
 dd.DatumID >= 2013011500 AND dd.DatumID < 2013011600
OPTION(merge join)
go
drop table #t
go
set statistics time off
set statistics io off
go
drop table FactTable
drop table DimDatum
drop table justnumbers

---

Gert-Jan