Why is the merge join in Node 48 [lowest right Inner Join] estimating a loss of 20% of the rows?
Both tables JOINed in the merge [Object 2 and Object 5] are completely repopulated [TRUNCATE then INSERT] a few hours (within the same jobstream) prior to this SELECTion.
I worked to get most of the tables clustered by KeyToSourceFile, or have a non-clustered covering index of KeyToSourceFile. It would seem that a merge join would be most efficient and that is what is used in most of the cases.
But the JOINs to AccountsPayable and ClaimsDtlAuxFile did not produce a merge join like the others, instead producing Index Seek and Nested Loops. And those are two of the more expensive parts of the query that remain.
That's why I wondered if the 20% lower estimation of rows was causing the QO to chose Index Seek and Nested Loops. I hoped that correcting the estimation would push the QO to a merge join which I'm guessing would run more quickly.
The most likely causes are:
- Sampled or out-of-date statistics on the two large input tables.
- The effect on the KeyToSourceFile statistics histogram of the independent filters [ClaimType]='E' AND [PayStatus]<>'X' on the Claims Detail table. Filtered statistics could help there.
Join estimation is complex, but in outline: SQL Server takes the histograms for KeyToSourceFile (scaled for the independent filters, or from a filtered statistics object if available) from both tables, aligns each step (key boundary) using linear interpolation where necessary, then compares step-by-step to estimate how many rows will join.
This is a statistical process, subject to simplifying model assumptions and accumulating errors, so you should not in expect a perfect estimate, particularly for large tables, and/or where data changes have occurred since the statistics were collected.
FYI the SQL Server 2014 cardinality estimator sometimes produces less 'surprising' estimates in this sort of situation.
The post-execution plan will be interesting, though it is likely to show the cause of poor performance is not directly related to this particular join estimate.
Update based on post-execution plan
This query is executing the scalar UDFs [dbo].[IsStrNull] and [dbo].[ufn_years_old] hundreds of millions of times.
The current implementation of scalar UDFs in SQL Server means each execution of the function (per row!) runs within a new execution context (and T-SQL stack frame). This is approximately as bad as running a separate query for each row for each scalar function. These scalar functions should probably be directly in-lined or converted to in-line TVFs.
As another undesirable side-effect, using a scalar UDF also prevents parallelism, something one would expect to see in a query processing 50 million rows through 16 joins. The problems of scalar (and multi-statement) UDFs have been written about extensively over the years, here is one example from Hugo Kornelis:
There are also a couple of odd choices of physical join type (nested loops) where a hash or merge might be expected to be better-performing. The source query text is unavailable (truncated in the provided file) so it's hard to know exactly why that choice is being made. Perhaps an OUTER APPLY construction that the optimizer is unable to unnest.
Per your suggestion, I added filtered statistics with ClaimType and PayStatus predicated, and the estimated number of rows actually went down a little bit further.
Now I just have to verify that the inline code I wrote on the fly properly reproduced what the UDFs were doing in each instance.
Thanks for your help, Paul.
with UDFs: 6:12
w/o UDFs: 2:36
link text