How can I get the estimated rows closer to the actual?

ssharpxzyv87 2017-10-11 18:37:50

bizkit 2017-10-11 20:53:27
In order to give the SQL Server Optimizer a better chance at estimation you can try enabling the histogram and making the parameter values known at compile time. Try example below from SSMS:

DECLARE @SQLString nvarchar(500);
DECLARE @P1 int;
DECLARE @P2 int;
DECLARE @P3 int;
DECLARE @P4 int;

SET @P1 = [some value];
SET @P2 = [some value];
SET @P3 = [some value];
SET @P4 = [some value];

SET @SQLString = N'SELECT CASE
WHEN EXISTS (SELECT 1 FROM zumero.dbfile$z$dd$1563152614 dd INNER JOIN zumero.dbfile$t$tx tx ON tx.txid=dd.txid WHERE tx.generation BETWEEN @P1 AND @P2)
OR EXISTS (SELECT 1 FROM zumero.dbfile$z$rv$1563152614 rv INNER JOIN zumero.dbfile$t$tx tx ON tx.txid=rv.txid WHERE tx.generation BETWEEN @P3 AND @P4)
THEN 1 ELSE 0 END
OPTION (RECOMPILE)';

EXECUTE sp_executesql @SQLString, @P1, @P2, @P3, @P4;