sql-server - 在 SQL Server 上查询 View 时出现性能问题

Question

我目前在 SQL Server 2016 Enterprise 上遇到一些奇怪的性能问题。

我在数据库中创建了一个新模式，然后在该模式中创建了一个 View 。

现在，当我直接连接到数据库时，它包含这个模式和 View ，并编写一个简单的查询，如

SELECT * FROM SCHEMA.VIEW

大约需要 30 分钟 (!) 才能完成。像

这样的完全限定查询也会发生同样的情况

SELECT * FROM DB_NAME.SCHEMA.VIEW

但是现在，如果我先将数据库更改为 master 或另一个用户数据库，然后跨数据库再次运行查询，它会在大约 10 秒内完成 (!)。两个数据库的数据库属性相同，数据库文件和日志文件使用的驱动器也是相同的。

有没有人知道什么可能会导致这种巨大的性能问题？

我为 View 使用了以下代码:

CREATE VIEW [controlling].[UnitLoginHistory]
AS

    WITH    cte 
            ( 
                    Jahr, UnityId, Unit_UnityId, Analytical_Code, Unit_Code, Unit_Name, Active
            ,       Show_in_org_chart, Begin_Date, End_Date, Unit_Owner_First_Name, Unit_Owner_Last_Name
            ,       Unit_Owner_Login, Unit_Owner_CorporateID, UnityParentId, UnityTypeId, Unit_Level
            ,       Magnitude_Code, ImportDate, ReplicationLevel 
            ) 
    AS
    (
            SELECT
                    dt.Jahr
            ,       a.UnityId 
            ,       a.UnityId 
            ,       a.Analytical_Code 
            ,       a.Unit_Code 
            ,       a.Unit_Name 
            ,       cast(case when a.Active = 'True' then 1 else 0 end as bit)
            ,       a.Show_in_org_chart 
            ,       a.Begin_Date 
            ,       a.End_Date 
            ,       a.Unit_Owner_First_Name 
            ,       a.Unit_Owner_Last_Name 
            ,       a.Unit_Owner_Login 
            ,       a.Unit_Owner_CorporateID 
            ,       a.UnityParentId 
            ,       a.UnityTypeId 
            ,       a.Unit_Level 
            ,       a.Magnitude_Code
            ,       a.ImportDate
            ,       1
            FROM    [Staging_INPUT].[DBO].[OBS_Workunit]    a
            JOIN    (
                            SELECT
                                    YEAR(IMPORTDATE)        Jahr
                            ,       MAX(IMPORTDATE)         Datum
                            FROM    [Staging_INPUT].[DBO].[OBS_Workunit]
                            GROUP   BY
                                    YEAR(IMPORTDATE) 
                    )                                       dt
            ON      a.ImportDate                    =       dt.datum
            WHERE   a.unitytypeid                   =       12
            UNION   ALL
            SELECT
                    b.Jahr
            ,       b.UnityId 
            ,       a.UnityId 
            ,       b.Analytical_Code 
            ,       a.Unit_Code 
            ,       a.Unit_Name 
            ,       cast(case when a.Active = 'True' then 1 else 0 end as bit)
            ,       a.Show_in_org_chart 
            ,       a.Begin_Date 
            ,       a.End_Date 
            ,       a.Unit_Owner_First_Name 
            ,       a.Unit_Owner_Last_Name 
            ,       a.Unit_Owner_Login      
            ,       a.Unit_Owner_CorporateID 
            ,       a.UnityParentId 
            ,       a.UnityTypeId   
            ,       a.Unit_Level 
            ,       a.Magnitude_Code
            ,       a.ImportDate 
            ,       b.ReplicationLevel + 1
            FROM    [Staging_INPUT].[DBO].[OBS_Workunit]    a
            JOIN    cte                                     b
            ON      a.UnityId                       =       b.UnityParentId
            AND     a.ImportDate                    =       b.ImportDate
            AND     a.UnityTypeId                   >=      6 
    )
    ,       Company
    AS
    (
            SELECT  DISTINCT 
                    Jahr
            ,       UnityId
            ,       LEFT(REPLACE(Magnitude_Code,'XE','U'),4) CompanyUID
            FROM    cte
            WHERE   UnityTypeId     =       7
            AND     Active          =       1

    )
    ,       BUs 
    AS
    (
            SELECT  DISTINCT 
                    a.Jahr                          JAHR
            ,       a.UnityId                       UnityId
            ,       c.Analytical_Code               BU_CODE
            ,       c.Unit_Name                     BU_NAME
            ,       b.CompanyUID
            FROM    cte                     a

            JOIN    Company                 b
            ON      a.Jahr          =       b.Jahr
            AND     a.UnityId       =       b.UnityId
            AND     a.Active        =       1

            JOIN    cte                     c
            ON      a.Jahr          =       c.Jahr
            AND     a.UnityId       =       c.Unit_UnityId
            AND     c.Active        =       1

            WHERE   ISNULL(c.Analytical_Code,'') != ''
    )
    SELECT  DISTINCT
            a.JAHR                          JAHR
    ,       a.BU_CODE                       BU_CODE
    ,       a.BU_NAME                       BU_NAME
    ,       'EUROPE\'
    +       b.Unit_Owner_Login              BU_LOGIN
    ,       b.Unit_Owner_Last_Name 
    +       ', '
    +       b.Unit_Owner_First_Name         BU_LOGIN_NAME
    ,       a.CompanyUID                    COMPANY_UID
    FROM    BUs                             a

    JOIN    cte                             b
    ON      a.Jahr                  =       b.Jahr
    AND     a.UnityId               =       b.UnityId
    AND     b.Active                =       1

    UNION   

    SELECT  DISTINCT
            a.Jahr                          JAHR
    ,       a.BU_CODE                       BU_CODE
    ,       a.BU_NAME                       BU_NAME
    ,       c.BU_LOGIN                      BU_LOGIN
    ,       c.BU_LOGIN_NAME                 BU_LOGIN_NAME
    ,       a.CompanyUID                    COMPANY_UID
    FROM    BUs                             a

    CROSS   JOIN (
                    SELECT  DISTINCT
                            [Last Name]     COLLATE Latin1_General_100_CI_AS
                    +       ', '
                    +       [First Name]    COLLATE Latin1_General_100_CI_AS BU_LOGIN_NAME   
                    ,       'EUROPE\'
                    +       [User Id]       COLLATE Latin1_General_100_CI_AS BU_LOGIN  
                    FROM    NAVISION.dbo.Employee
                    WHERE   [Global Dimension 2 Code]       IN      (
                                                                            10061
                                                                    ,       10062
                                                                    )
                    AND     ISNULL([User Id],'')            !=      ''
                 )                          c

GO 

执行时间和统计数据:

use NAVISION

GO

set statistics io on 
set statistics time on

select * 
from NAVISION.dbo.UnitLoginHistory 
where Jahr = 2017 
order by 1,2

SQL Server 解析和编译时间: CPU 时间 = 0 毫秒，运行时间 = 0 毫秒。

(34119 行受影响)表“工作台”。扫描计数 1607，逻辑读取 253696，物理读取 0，预读读取 1238，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U415 Altran Engineering GmbH$员工”。扫描计数 1，逻辑读取 128，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U388 Altran Aviation GmbH$Employee”。扫描计数 1，逻辑读取 42，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U354 Altran Service GmbH$Employee”。扫描计数 1，逻辑读取 210，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U353 AIH Holding GmbH Co KG$Employee”。扫描计数 1，逻辑读取 934，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“OBS_Workunit”。扫描计数 46286，逻辑读取 10430933，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间: CPU 时间 = 1363546 毫秒，运行时间 = 1455980 毫秒。

use Staging_INPUT
GO

set statistics io on 
set statistics time on

select * 
from NAVISION.dbo.UnitLoginHistory 
where Jahr = 2017 
order by 1,2

SQL Server 解析和编译时间: CPU 时间 = 0 毫秒，运行时间 = 0 毫秒。

(34119 行受影响)表“工作台”。扫描计数 582，逻辑读取 576096，物理读取 0，预读读取 146，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“工作文件”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“OBS_Workunit”。扫描计数 53573，逻辑读取 485656，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U415 Altran Engineering GmbH$员工”。扫描计数 1，逻辑读取 128，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U388 Altran Aviation GmbH$Employee”。扫描计数 1，逻辑读取 42，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U354 Altran Service GmbH$Employee”。扫描计数 1，逻辑读取 210，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“U353 AIH Holding GmbH Co KG$Employee”。扫描计数 1，逻辑读取 934，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间: CPU 时间 = 15047 毫秒，运行时间 = 28007 毫秒。

Answer 1

不同的性能肯定是由于不同的执行计划。由于计划因数据库上下文而异，这表明影响计划的设置不同。有太多默认的 SET 选项和属性可能因数据库而异，您可能错过了一两个。

我建议为 2 个数据库生成 CREATE DATABASE 脚本并比较这些脚本。

编辑:

database compatibility level 的区别设置会影响执行计划。 SQL Server 将对 110 (SQL Server 2012) 级别的数据库使用遗留基数估计器，而较新的 CE 将用于 120 及更高版本的数据库，除非 LEGACY_CARDINALITY_ESTIMATION数据库范围设置已打开。