Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds? Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not done), in an effort to improve it I rewrote it from scratch, and I came up with the following:

Now here's the boring word-problem portion:

We want to pull a list of the TOP 5 clients per sales rep, but exclude the TOP 10 total clients from that list. (So if John Doe has clients A, B, C, D, and E, and client C is one of the top 10, then only pull A, B, D, and E.)

To do this, the first query used a IN (... NOT IN ( ) ), so I thought that the nesting of IN was the issue, to rewrite it I did an OUTER APPLY that really broke everything.

Anyway, I fixed all that and I ran the query, and it still took 10-15 seconds which I assumed was parameter sniffing. To investigate I ran the query in SSMS, added OPTION (RECOMPILE) (to see what query plan would generate), and got the following:



It can be viewed here on Brent Ozar's 'Paste The Plan'. The query that generated this was:

DECLARE @Top10Temp TABLE (Id INT)
INSERT INTO @Top10Temp
SELECT TOP 10 Id
FROM Object1
WHERE Column2  = @ReportId
  AND Column3  = 0
GROUP BY Id
ORDER BY SUM(Column4 + Column5) DESC

SELECT Object2.*
FROM Object1 AS Object2
OUTER APPLY (
    SELECT TOP 5
        Object3.Id,
        SUM(Object3.Column4 + Object3.Column5) AS Column6
    FROM Object1 AS Object3
    WHERE Object3.Column3 = 0
      AND Object3.Column7 = Object2.Column7
      AND Object3.Column2 = @ReportId
    GROUP BY
        Object3.Id
    ORDER BY
        SUM(Object3.Column4 + Object3.Column5) DESC
) AS Object4
WHERE Object2.Column2 =      @ReportId
  AND Object2.Column3 =      0
  AND Object2.Id      =      Object4.Id
  AND Object2.Id      NOT IN (SELECT Id FROM @Top10Temp)
ORDER BY Object2.Column7
OPTION (RECOMPILE)


Now the same query but with OPTION (OPTIMIZE FOR UNKNOWN) generated the following plan:



Which can also be viewed at 'Paste the Plan'. This plan executed in less than 1 second.

If I add OPTION (OPTIMIZE FOR (@ReportId = #)), where # is the same as the @ReportId variable, I get the same query plan as the second.

Did I do something wrong? I'm having trouble understanding what happened, so any information is much appreciated. (I also really don't like trying to influence the optimizer via hints, but if it's necessary I'll keep it.)
"To investigate I ran the query in SSMS..."
That's the issue.  Local variables use the density vector of statistics yielding a much better row estimate, and consequently already OPTIMIZING for UNKNOWN.  Parameterized dynamic SQL uses the histogram, which pulls the entire row count for a given section.

Look at the estimated number vs actual number of rows for each of your Paste the Plan links.
The second link has waaaayyyy better estimations than the first.

I'd deploy your SSRS query to a dev instance and run some tests as I suspect you might have performance issues.

BTW, update stats or rebuild indexes on those beast tables, if you can.

Links:
Inside the Statistics Histogram &amp; Density Vector