Hello,
I'm facing with a strange performance issue on SQL Server 2016 Service Pack 1 CU5 instance after migrating from a SQL Server 2012 Service Pack 2 CU2 instance.
The hardware is strictly the same as the two instance are on the same machine.
The configuration in sys.configurations is the same for the 2 instance.
To reproduce the issue I create a first view which concatenate a field from a table using xml method, then I create a second view which concatenate the field from the first view, then I launch a query which concatenate the field from the second view.
Here a code sample to reproduce the case
IF OBJECT_ID('[dbo].[Test]') IS NOT NULL
DROP VIEW [dbo].[Test]
IF OBJECT_ID('[dbo].[Test2]') IS NOT NULL
DROP VIEW [dbo].[Test2]
GOCREATE VIEW [dbo].[Test]
AS
SELECT (SELECT TOP 0 [name] AS [text()]
FROM [sys].[columns]
FOR XML PATH, TYPE).value('.', 'nvarchar(max)') AS [Test]
GO
CREATE VIEW [dbo].[Test2]
AS
SELECT (SELECT [Test] AS [text()]
FROM [dbo].[Test]
FOR XML PATH, TYPE).value('.', 'nvarchar(max)') AS [Test]
GO
SELECT (SELECT [Test] AS [text()]
FROM [dbo].[Test2]
FOR XML PATH, TYPE).value('.', 'nvarchar(max)') AS [Test]
As you can notice the first view return no data to concatenate for the test, on SQL 2012 the last select is instantaneous, on SQL 2016 the last select take 84s !
I have think that the new Cardinality Estimator could be in cause so I try to force the old one with the option SET LEGACY_CARDINALITY_ESTIMATION = ON; on my database but no change for the performance.
I also try to set the SET COMPATIBILITY_LEVEL = 110; on the 2016 database but result is the same.
I have the possibility to rewrite the code differently in order to optimize the performance on SQL 2016, but there is a lot of code to rewrite that imply a lot of testing load and a risk of regression.
Before implementing this solution I would like to know if there is a configuration setting on instance or on database that I could try to retrieve the same performance as SQL 2012 ?
I also join the 2 executions plans obtained for SQL 2012 and SQL 2016 if it could help analysis.
Any help is appreciated, thanks in advance.