multi view query

…but the main problem is something that would require a design change. The issue is only really easy to see on the nested loops join to the subtree containing a Filter, some Spools, Segments and a Sequence Project:

Looking at the nested loops join, we see CourtCaseId as the outer reference. The Filter properties show that filtering on that correlated value is performed late:

This means that the expensive subtree, with it's ROW_NUMBER and COUNT(*) OVER (…) is performed for all case ids, before being filtered for the one we are interested in. The same essential issue occurs with the Merge Joins as well; it's just that the optimizer chose to filter (join) on the case id in the merge join instead of in a filter on the inner side of a loop join.

If you look at the logic, there is no reason the Case ID filter could not be pushed down beyond the ROW_NUMBER and COUNT(*) OVER (…) from a logical perspective. Indeed, with proper indexing (that may already be present) the scans of Person and Company could be replaced with efficient seeks on the case ID value.

If the query contained a literal value for the case id, the optimizer could do this for you. It could even do this if the case id were provided in a variable, so long as a recompile query hint were used to allow it to sniff the runtime value and construct a plan for it.

To demonstrate the principle using an AdventureWorks query:

DECLARE @x integer = 1;
 
SELECT * FROM 
(
    SELECT 
        TH.ProductID, 
        cbs = COUNT_BIG(*) OVER (PARTITION BY TH.ProductID),
        rn = ROW_NUMBER() OVER (PARTITION BY TH.ProductID ORDER BY TH.TransactionID)
    FROM Production.TransactionHistory AS TH
) AS TH2
WHERE TH2.ProductID = @x;

That will produce a scan unless OPTION (RECOMPILE) is added (and even then, only in the actual plan, not the estimated one). If you use the literal '1' in place of @x, the plan features a seek, as you would hope.

Unfortunately, the current optimizer cannot push column/expression references down the plan in this circumstance, even where it is logically safe to do so.

Workarounds

Either rewrite the query so SQL Server can see a literal value for case ID at runtime, or replace/extend the view with an inline table-valued function that takes case ID as a parameter, and which contains an explicit predicate on case ID as part of the definition.