7

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:

Plan 1

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:

Plan 2

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

  • Have you tried updating the statistics for the tables in question? – Max Vernon Sep 29 '17 at 17:32
  • @MaxVernon Yes, and it hasn't actually helped anything. :/ – Der Kommissar Sep 29 '17 at 17:32
5

"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

  • The thing is that these tables aren't that large (should have added that to the question), total row-count is only 200,000 and the query returns a final row count of about 1850 rows. – Der Kommissar Sep 29 '17 at 12:19
  • @202_accepted Even if the table isnt large, inaccurate estimations of rows returned can cause execcsive memory grants, potentially casuing contention on the server, and, if the server is under resource contention the larger the memory grant the lower the priority the requets gets. Look at your 1st execution plan (hover over the index seeks or scans) and compare the estimated vs actual rows returned. They're crazy off! Now compare that to the 2nd execution plan: not great, but much better estimate to actual ratios. Lastly, test calling the query from SSRS and locally: different? – thundercougarfalcon Sep 29 '17 at 15:46
4

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.

  • I appreciate this detailed answer Joe, and I only have one question: is there any way to hint to SQL Server that each @ReportId will have the same number of results? (They should be within 5-20 rows of each other.) I don't know if that would help my situation or not, or if it's even possible, but that's one thing that I could see having an impact. – Der Kommissar Oct 2 '17 at 17:51
  • 1
    @202_accepted Are you asking about just the column that ReportId filters against? If the histogram for the column represents your data well enough then that should not be necessary. Otherwise you could try to pick a representative value to use with OPTIMIZE FOR VALUE but your histogram could change over time. If you want to use density then you could use OPTIMIZE FOR UNKNOWN along with OPTIMIZE FOR VALUE for everything else, but that's arguably not what the hint is designed for. – Joe Obbish Oct 2 '17 at 22:21
  • I agree to Joe. Look for plan guide for mir Details and options. technet.microsoft.com/en-us/library/… – Magier Oct 31 '17 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.