I have two databases/catalogs on the same SQL server and an SQL-user with access to both of them.

Collation on Database01 is Danish_Norwegian_CI_AS

Collation on Database02 is SQL_Latin1_General_CP1_CI_AS

I need to query two views on "Database02". The queries are basically

SELECT * FROM View01 -- (resulting in 43 rows)
SELECT * FROM View02 -- (resulting in 2000 rows)

The two views are rather complex (to say the least) and I do not have access to change them.

If I log onto the SQL server and specify "Database02" as "default catalog", the queries respond in "0.001" and 30 seconds, respectively.

However if I log onto the SQL Server and specify "Database01" as "default catalog" the first query executes in 30 seconds whereas the second query times out after 600 seconds.

The queries executed on Database01 will be something like

SELECT * FROM Database02.View01
SELECT * FROM Database02.View02

Can anyone please explain this behavior? I am not trying to do any joins between the databases, so I would not expect the diffent collations to be an issue.

Slow query: https://www.brentozar.com/pastetheplan/?id=BJgyIUhrG

Fast query: https://www.brentozar.com/pastetheplan/?id=S1-BUIhSG

PARAMETERIZATION is "Simple" on both databases.


I looked at your plans and found the cause of this difference:

you get a slow plan in the database with compatibility level 100 (see the plan: CardinalityEstimationModelVersion="70" i.e. the old cardinality estimator is used).

Your good plan is generated using new cardinality estimator of SQL Server 2014: CardinalityEstimationModelVersion="120"

You should update your question with the view text to find out the exact predicate where estimation goes wrong, but in your slow plan cardinality there is underestimation of the join with OMInternalOrganization so NL was chosen while in the fast plan you see Hash Join for this table, starting with this join slow plan shows many other Nested Loops while Hash Join is better and it's used in fast plan.

I don't know for what reason that database was left in compatibility level 100, maybe other queries perform better with old cardinality estimator, so instead of global changing compatibility level to 120 for that database I suggest you to use local query option: OPTION (QUERYTRACEON 2312) to forse your query to use new cardinality estimator even in that "old-style" database.

More on cardinality estimator here: Cardinality Estimation (SQL Server)

And here: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Your Answer

