Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds?

Question

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.)

Answer 1

"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 & Density Vector

Answer 2

The slow plan has a poor cardinality estimate coming out of the index seek at node 4. The estimated number of rows is 1 but the actual number of rows is 3261. Here's the seek predicate:

Seek Keys[1]: Prefix: Database1.Schema1.Object1.Column2, Database1.Schema1.Object1.Column3 = Scalar Operator(ScalarString7), Scalar Operator(ScalarString2)

You're filtering on two different columns from the same table. Often SQL Server doesn't have enough information to give a precise estimate for that scenario, so it makes modelling assumptions that depend on your CE version, patches, trace flags, and so on. For example, it might assume that the columns have no correlation and multiply the selectivities together. That can result in a low estimate if there is some correlation between the filters.

In general I would say that if you get good performance with a bad estimate then you probably got lucky and your luck may run out at some point. I would try to fix that estimate. I can't give you precise instructions because there's too much information missing (you won't be able to share some of the missing information due to IP concerns), but I can say that a multi-column statistic or index could help. Storing the primary keys of the table after filtering into a temp table is a method that should always work. With a more accurate estimate I would expect to see a query plan that's similar to the fast plan.

You didn't do anything wrong by adding the OPTION (RECOMPILE) hint. You may have gotten poor performance simply by bad luck. The parameter embedding optimization usually helps instead of causing problems. OPTIMIZE FOR UNKNOWN causes SQL Server to use the statistics objects differently and it just so happens that you got a estimate closer to reality when using it.

I would not use OPTIMIZE FOR UNKNOWN as a long term solution. The query plan won't change depending on the value of @ReportId which could cause problems as you change the variable's value. It's also a bit of an indirect fix and you admitted that you don't understand how it works. It would be better to attack the problem more directly by fixing the cardinality estimate or by strategically materializing intermediate results into temp tables. As a general rule you should avoid using table variables because they do not have statistics. Table variables have very limited use cases and my recommendation for you is to use them only when you have no other choice.