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

Plan1.pesession (174.1 kB)
avatar image By ssharpxzyv87 0 asked Oct 11 at 06:37 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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;

avatar image By bizkit 16 answered Oct 11 at 08:53 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x445
x1
x1

asked: Oct 11 at 06:37 PM

Seen: 19 times

Last Updated: Oct 12 at 03:03 PM