Same query, fast and slow

One of my clients had an issue where the same query was running fast or slow depending on the database connected to. This was in spite of both databases residing on the same server and the query was predicated with a database name, so both executions were executing exactly the same code on exactly the same database with the same data. How can this be?
 
Actually, there are a few reasons, with the most obvious being the COMPATIBILITY_LEVEL. Even though you are executing a query on Database dbA, if you are executing it from Database dbB, SQL will honour the compatibility level of dbB. In the case of my client, both databases had the same compatibility level of 130 (SQL Server 2016).

So, what happened?
Microsoft introduced LEGACY_CARDINALITY_ESTIMATION in SQL Server 2016 specifically so that you could configure a database with compatibility level 130, while keeping the old cardinality estimator (CE). A database upgraded to 2016 will keep the old CE by default, even though you run with compatibility level of 130 (2016). However, a new database on the same 2016 server will have compatibility level of 130 and the new CE.

So, two databases with compatibility level, 130, on the same server can have different CEs, leading to different plans (potentially one fast and one slow) for the same query pointing to the same database. In my client's situation, this led to one query executing in 7 seconds and the other executing in 10 minutes.

Essentially this was the code to reproduce the issue

Use dbA;

go

--This query executes in 7 seconds with an efficient plan

select *

      from dbA.dbo.myView

      where myKey= 23;   

Use dbB;

go 

--This query (on the same connection) executes in 10 minutes with an inefficient plan

select *

      from dbA.dbo.myView

      where myKey= 23;

    

I imagine the new cardinality estimator is generally better than the old one and its use should be encouraged. However, it could be that in your environment, the old CE is better. If you are having performance woes, it might be worth benchmarking the two CEs. It could even be that you create a second empty database, just to be used for those queries that prefer the old CE. (They would be executed from the second, dummy, database).

For instructions on altering the CE see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql.