Prevent SQL Server 2017 from executing non correlated subquery in loop

Question

We have a multi-tenant database, data separation is achieved with a key attached to all tables. The number of rows in each table may be very different for each tenant (0 to 500000+). Since migrating to SQL Server 2017 sometimes very bad execution plans are used. We didn't have any problems with SQL Server 2008 previously used.

The biggest problem is, that sometimes non-correlated sub queries are executed in a loop for each row of the outer query. The subquery would take 0 seconds, and the outer query using an IN(list of ids) also takes 0 seconds, but together they run for several minutes.

I have tried different solutions:

• OPTION(RECOMPILE)
• Changing to a JOIN and OPTION(FORCE ORDER)
• UPDATE STATISTICS for the tables
• Parameter Sniffing is enabled but doesn't make a difference

none of these solved the problem.

Example Query:

SELECT address.adrId, address.adrCity FROM address 
WHERE address.orgId=1 AND
address.adrId IN (SELECT instAddress.adrId 
                    FROM instAddress
                    WHERE instAddress.instId = 12345 
                    AND instAddress.orgId = 1) 

UPDATE: actual execution plan, number of executions and estimated number of executions highlighted.

Is it somehow possible to tell SQL Server to always execute the sub query first and only once?

UPDATE:

This one query changed to the correct execution plan after latest update of statistics. This one and similar queries are generated by our application dynamically. We would like to prevent us from rewriting the complete database tier.

I chose this query for the example because it was the most basic one where the issue occurred. Another example (fetching translations for some names) where the independent sub query is executed in loops for the outer query actual execution plan available: https://pastebin.com/tbB0vPUZ

select institutions.inst_id , institutions.inst_nr , inst_name , 
translations1.trans_to as trans_to , translations2.trans_to as trans_to2 , 
translations3.trans_to as trans_to3 , translations4.trans_to as trans_to4 
from institutions 
left join translations translations1 on institutions.inst_nr = translations1.trans_from and translations1.lang_locale = @0 and translations1.org_id = @1 
left join translations translations2 on institutions.inst_nr = translations2.trans_from and translations2.lang_locale = @2 and translations2.org_id = @3 
left join translations translations3 on institutions.inst_name = translations3.trans_from and translations3.lang_locale = @4 and translations3.org_id = @5 
left join translations translations4 on institutions.inst_name = translations4.trans_from and translations4.lang_locale = @6 and translations4.org_id = @7 
where ( institutions.org_id = @8 and lcd_id = @12 
and exists ( 
    select inst_id_partner 
    from agreements 
    where agreements.inst_id_partner = institutions.inst_id and org_id = @13 and agree_id in ( 
        select agree_id 
        from agreements 
        where org_id = @14 and pers in ( @15 , @16 ) and art in ( @17 , @18 ) and prog_id not in ( 
            select prog_id 
            from programs 
            where org_id = @19 and is_not_part_of_all_prog = @20 ) 
        and agree_id in ( 
            select agree_id 
            from year_agree where year_id = @21 and sem_id = @22 ) 
        and ( agree_id not in ( 
            select agree_id 
            from agree_stat 
            where org_id = @23 ) 
        or agree_id in ( 
            select agree_id 
            from agree_stat 
            where org_id = @24 and agree_stat.year_id = @25 and agree_stat.sem_id = @26 and agree_stat.count1 < agreements.total ) ) 
        and inst_id in ( @27 ) ) 
) 
order by translations1.trans_to

Answer 1

I think that rewrite of the original query to use EXISTS can lead to more robust query plans:

SELECT a.adrId, a.adrCity FROM address  a
WHERE EXISTS ( SELECT *
                    FROM instAddress i
                    WHERE i.instId = 12345 
                    and i.orgId=1
                    AND i.orgId = d.orgId 
                    AND i.adrId = a.adrId 

             )

Another observation is that such index will increase a chance that query optimizer will set a correlated query as an initial one to execute and then fetch rows from [address]

CREATE INDEX IX_1 ON instAddress (instId) INCLUDE (orgid, adrId )

Another possible reason - outdated statistics.

According to OP:

The query in the initial example was one of about 10 similar queries for which this issue occurred, some of them appear and disappear when the statistics or the parameters change, but eventually pop up gain after a while

If compatibility level is lower than 130, default threshold of statistics to be auto updated is 20% changed rows. This is especially not nice for large tables and can be fixed by:

DBCC TRACEON (2371, -1)

Another possible reason: New cardinality estimator and correlated queries.

Since migrating to SQL Server 2017 sometimes very bad execution plans are used. We didn't have any problems with SQL Server 2008 previously used.

The old (legacy) cardinality estimation (that was available in SQL 2008) can be enabled as a query hint, on a database level or on instance level, via trace flag

SELECT .. FROM .. WHERE ..
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

or on a database level:

USE DB
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

---

Related:

• SQL Server IN vs. EXISTS Performance

• Default auto statistics update threshold change for SQL Server 2016

Answer 2

You can also achieve this with a standard join:

SELECT a.adrId, a.adrCity 
FROM address  a
inner join instAddress i on i.orgId = a.ordId and i.adrId = a.adrId
where i.instId = 12345 and a.orgId = 1