I am currently running into some bad performance issues with a strange behaviour on SQL Server 2016 Enterprise.
I created a new schema in a database and then I created a view inside this schema.
Now, when I connect directly to the database, that contains this schema and the view and write a simple query like
SELECT * FROM SCHEMA.VIEW
it takes around 30 minutes (!) to complete. The same happens with a full qualified query like
SELECT * FROM DB_NAME.SCHEMA.VIEW
But now, if I first change the database to master or another user database and then run the query again across the databases, it completes within around 10 seconds (!). The database properties of both databases are the same, as well as the used drives for the database files and the log files.
Does anyone have an idea on what might cause this enormous 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
Execution times and stats:
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 parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(34119 row(s) affected) Table 'Worktable'. Scan count 1607, logical reads 253696, physical reads 0, read-ahead reads 1238, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U415 Altran Engineering GmbH$Employee'. Scan count 1, logical reads 128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U388 Altran Aviation GmbH$Employee'. Scan count 1, logical reads 42, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U354 Altran Service GmbH$Employee'. Scan count 1, logical reads 210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U353 AIH Holding GmbH Co KG$Employee'. Scan count 1, logical reads 934, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'OBS_Workunit'. Scan count 46286, logical reads 10430933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: 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 parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(34119 row(s) affected) Table 'Worktable'. Scan count 582, logical reads 576096, physical reads 0, read-ahead reads 146, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'OBS_Workunit'. Scan count 53573, logical reads 485656, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U415 Altran Engineering GmbH$Employee'. Scan count 1, logical reads 128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U388 Altran Aviation GmbH$Employee'. Scan count 1, logical reads 42, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U354 Altran Service GmbH$Employee'. Scan count 1, logical reads 210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'U353 AIH Holding GmbH Co KG$Employee'. Scan count 1, logical reads 934, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 15047 ms, elapsed time = 28007 ms.
