Performance Issues When Querying a View on SQL Server
I am currently facing some poor performance issues with strange behavior on SQL Server 2016 Enterprise.
I created a new schema in the database and then created a view inside that schema.
Now when I connect directly to the database containing this schema and view and write a simple query like
SELECT * FROM SCHEMA.VIEW
it takes about 30 minutes (!). The same happens with a full query like
SELECT * FROM DB_NAME.SCHEMA.VIEW
But now if I first change the database to master or another custom database and then run the query across the databases again, it completes in about 10 seconds (!). The database properties of both databases are the same, as are the drives used for the database files and log files.
Does anyone have any idea what could be causing such huge performance problems?
I used the following code for the 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
Runtime and statistics:
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 compile time and time: CPU time = 0 ms, elapsed time = 0 ms.
(34,119 rows affected) "Desktop" table. Number of scans 1607, logical read 253696, physical read 0, read forward 1238, logical read lob 0, physical read lob 0, read / write lob 0. Table "U415 Altran Engineering GmbH $ Employee". Scan count 1, boolean read 128, physical read 0, read forward 0, boolean read 0, physical read lob 0, read / write lob 0. Table "U388 Altran Aviation GmbH $ Employee". Number of scans 1, logical read 42, physical read 0, read forward 0, logical read lob 0, physical read lob 0, read / read lob 0. Table "U354 Altran Service GmbH $ Employee". Scans 1, Logical Read 210, Physical Read 0, Read Forward - 0, Logical Read Logic 0,physical reading lob 0, reading with reading lob 0. Table "U353 AIH Holding GmbH Co KG $ Employee". Number of scans 1, logical read 934, physical read 0, read forward 0, logical read logical 0, physical read lob 0, read / write lob 0. Table "OBS_Workunit". Scan count 46286, boolean read 10430933, physical read 0, read forward 0, boolean read 0, physical lob read 0, read / read lob 0.boolean read 10430933, physical read 0, read forward 0, boolean read 0, physical read lob 0, read-read lob 0.boolean read 10430933, physical read 0, read forward 0, boolean read 0, physical lob 0, lob 0 read-read.
SQL Server Runtime: CPU Time = 1363546 ms, Elapsed Time = 1455980 ms.
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 compile time and time: CPU time = 0 ms, elapsed time = 0 ms.
(34,119 rows affected) "Desktop" table. Number of scans 582, boolean read 576096, physical read 0, read forward 146, boolean read 0, physical read lob 0, read / write lob 0. Working file table. Number of scan 0, logical read 0, physical read 0, read forward 0, logical read lob 0, physical read lob 0, read / write lob 0. Table "OBS_Workunit". Scan count 53573, boolean read 485656, physical read 0, read forward 0, boolean read 0, physical read lob 0, read / write lob 0. Table "U415 Altran Engineering GmbH $ Employee". Scan count 1, logical read 128, physical read 0, read forward 0, boolean read 0, lob physical read 0,reading with reading lob 0. Table "U388 Altran Aviation GmbH $ Employee". Number of scans 1, logical read 42, physical read 0, read forward 0, logical read lob 0, physical read lob 0, read / read lob 0. Table "U354 Altran Service GmbH $ Employee". Scans 1, Logical Read 210, Physical Read 0, Read Forward - 0, Logical Read Logic 0, Physical Read Lob 0, Read / Read lob 0. Table "U353 AIH Holding GmbH Co KG $ Employee". Scan count 1, boolean read 934, physical read 0, read forward 0, boolean read 0, physical lob read 0, read-read lob 0.logical read lob 0, physical read lob 0, read with read lob 0. Table "U354 Altran Service GmbH $ Employee". Scans 1, Logical Read 210, Physical Read 0, Read Forward - 0, Logical Read Logic 0, Physical Read Lob 0, Read / Read lob 0. Table "U353 AIH Holding GmbH Co KG $ Employee". Scan count 1, boolean read 934, physical read 0, read forward 0, boolean read 0, physical lob read 0, read-read lob 0.logical read lob 0, physical read lob 0, read with read lob 0. Table "U354 Altran Service GmbH $ Employee". Scans 1, Logical Read 210, Physical Read 0, Read Forward - 0, Logical Read Logic 0, Physical Read Lob 0, Read / Read lob 0. Table "U353 AIH Holding GmbH Co KG $ Employee". Scan count 1, boolean read 934, physical read 0, read forward 0, boolean read 0, physical lob read 0, read-read lob 0.U353 AIH Holding GmbH Co KG $ Employee ". Scan Count 1, Logical Read 934, Physical Read 0, Read Forward 0, Logical Forehead Read 0, Physical Read Lob 0, Read Read Lob 0.U353 AIH Holding GmbH Co KG $ Employee ". Scan Count 1, Logical Read 934, Physical Read 0, Read Forward 0, Logical Forehead Read 0, Physical Read Lob 0, Read Read Lob 0.
SQL Server Runtime: CPU Time = 15047 ms, Elapsed Time = 28007 ms.
source to share
Different performance should be due to different execution plans. Since plans differ depending on the context of the database, this implies different settings that affect the plan. There are so many default SET options and properties that can vary by database, you missed one or two.
I suggest creating scripts CREATE DATABASE
for 2 databases and comparing scripts.
EDIT:
Differences in database compatibility level can affect execution plans. SQL Server will use the legacy database cardinality estimate of 110 (SQL Server 2012), while the new CE will use the 120 and later databases if LEGACY_CARDINALITY_ESTIMATION is enabled.
source to share