Sql-server – TSQL Execution Plan – Estimated Number of Rows = 1 – Poor Performing Query

To answer your first question:

Why is there a Stream Aggregate in the query when I dont have a group by. I am guess it has something to do with the join being merge and its doing a sort?

SQL Server is effectively rewriting your query. This query:

select Object1.Column1
FROM Object2 Object3 WITH (ROWLOCK)
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)
or Object3.Column3 in (SELECT Column2 FROM Object5);

Can also be written like this:

select Object1.Column1
FROM
(
select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)

UNION

select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column3 in (SELECT Column2 FROM Object5)
) Object3 
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1;

That's why you have two different access methods on Object2 in the query plan. The Merge Join (Concatenation) operation isn't actually a merge join. It's just implementing a UNION ALL and combining the results. The stream aggregate that you mentioned groups by Object3.Column2. This both removes duplicates from the Merge Join (Concatenation) and sorts the data so that it can be used in the following MERGE JOIN to Object4.

To answer your second question:

Secondly and more importantly why does an estimate of 13 going into the stream aggregate comes out as an estimate of 24,595,900? This is causing the secondary problem of Object4 getting Clustered index scanned instead of a nested loop. I had to split the query into two queries instead of using a OR and the join turns into a nested loop and seeks into Object4.

It looks like a bug. After some searching I found an article by Paul White explaining the issue. The bug is reported to Connect if you want to vote for it or add yourself as affected.

In short, the cardinality estimate is 24595900 because 27328800 (table cardinality of Object2) * 0.9 = 24595900 after rounding. You're on SQL Server 2008 so the legacy CE calculation is used:

A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

I recommend reading through the entire article. The issue is a little difficult to summarize, but I will attempt to do so by quoting part of the text near the end:

The cardinality estimator uses the Fixed Join calculator with 100% selectivity. As a consequence, the estimated output cardinality of the semi join is the same as its input, meaning all 113443 rows from the history table are expected to qualify.

The exact nature of the bug is that the semi join selectivity computation misses any predicates positioned beyond a union all in the input tree. In the illustration below, the lack of predicates on the semi join itself is taken to mean every row will qualify; it ignores the effect of predicates below the concatenation (union all).

A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

Splitting up the query as you did should prevent the issue. You also might have some luck by rewriting the query using UNION (see Example 4 in the article).

First let's start where the problem likely isn't to be. Cardinality estimates look fine. You aren't doing any filtering and all of the joins are left joins on the primary keys of the tables. Table access methods look fine. You've defined the best possible indexes for the tables and SQL Server is doing index scans on those tables. Seems perfectly reasonable because you need all of the rows from the tables. Join order seems ok. None of the tables will decrease or increase the size of the result set (if ZZ_PlayerIDList is the starting table). A poor join order could lead to some unnecessary repartitioning but I don't see that here.

That means the join type. There are four parallel merge joins and the rest are parallel hash joins. There are some edge cases with parallel merge join that perform very poorly. I'm not a fan of it for this type of query in which you data is already sorted. The parallel aspect of it means you read the sorted data in parallel (which breaks the sorting) only to have to sort it again. There's a lot of repartitioning rows and sorting which would be avoided with MAXDOP 1 merge joins. There are also hints in the query plan that this query isn't getting as much memory as it wants.

I would try adding a MAXDOP 1 hint to the query and making changes so that you get all merge joins. For testing purposes it may be helpful to add OPTION (MERGE JOIN) as a hint. I would not expect that query to take more than 9 hours. If it does that sounds like some kind of hardware, configuration, or blocking issue.

From a data model point of view, you could improve performance of the query by combining together some of your tables. All of them have the same primary keys. For example, UX_ZZGamingTrips has 18260100 rows and UX_ZZTrackedSlot has 18259900 rows. Would combing those tables into one table really be a bad thing? What do you gain by separating them?