How to improve performance of multi-database queries in SQL Server where one database is synchronize-CodePudding

I have two databases. One I'll call a is a read-only synchronized database that is part of an availability group and the other is a plain ol database I'll call b on the same server as the synchronized database.

I need to write views in b that read from a, but they perform very poorly in this environment.

For example, I might have:

create view dbo.v1    --> in b
as
    select
        t1.col_a,
        t2.col_b
    from
        a.dbo.Table1 t1
    left outer join
        a.dbo.Table2 t2 on t1.t2_id = t2.Id

We can't add artifacts to database a...such as views, functions, and stuff...but we're free to read from it. OpenQuery wouldn't be available, since we're not talking about a linked server... a and b are on the same server.

Given the above, I can execute the select statement within the view from either databases a or b.

For example:

use a;

select
    t1.col_a,
    t2.col_b
from
    a.dbo.Table1 t1
left outer join
    a.dbo.Table2 t2 on t1.t2_id = t2.Id

...vs...

use b;

select
    t1.col_a,
    t2.col_b
from
    a.dbo.Table1 t1
left outer join
    a.dbo.Table2 t2 on t1.t2_id = t2.Id

On super simple ones like this example, the difference in speed is not too bad...but as the queries gain complexity, The first one runs in nearly 0 time, while the second one waits for-seeming-ever and finally runs...in minutes or worse.

Is there something misconfigured that I might look at? Is there some magic query hit that might help? The server is Windows Server 2012 R2 and the database version is SQL Server 2016 Enterprise.

Dan kindly posted my a and b .sqlplan files of a genuine query that was too ugly to include here to PasteThePlan (a.sqlplan and b.sqlplan). Thanks Dan!

Something I noticed in the b plan is that it suggested a missing index (with 91 impact) in one of the tables...and the a plan (which runs fast) found no recommendations

CodePudding user response：

I uploaded your query Plans to PasteThePlan and added the links to your question.

The first glaring difference between the 2 plans is the cardinality estimation version:

Plan a:

<StmtSimple ... CardinalityEstimationModelVersion="70"

Plan b:

<StmtSimple ... CardinalityEstimationModelVersion="130"

SQL Server optimizes queries based on the cardinality estimator version of the context database. Because the CE version of the context database is different, you get different plans for the same query. The CE version is tied to the database compatibility level unless overridden by a database scoped configuration or query hint.

The legacy cardinality estimator (used in SQL 2012 and earler versions) can provide better plans for some queries. Depending on your overall workload, you can choose to use the legacy CE with targeted query hints or for all database queries.

Query hint example:

use b;

select
    t1.col_a,
    t2.col_b
from
    a.dbo.Table1 t1
left outer join
    a.dbo.Table2 t2 on t1.t2_id = t2.Id
OPTION(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Database scoped configuration example:

use b;

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

The query hint may be the better choice if this is the only problematic query. However, I generally suggest due diligence with query and index tuning first (note the missing index suggestions in the plan with the newer CE).

I'll add it's not uncommon for newer CE versions to introduce performance regression for some queries while benefitting others. Queries already in the need of query/index tuning are the most susceptible to regression in my experience. It's a good practice to include workload performance testing and CE analysis as part of SQL upgrade plans.