October 5, 2017 at 1:00 pm
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 caseIF OBJECT_ID('[dbo].[Test]') IS NOT NULL
DROP VIEW [dbo].[Test]
IF OBJECT_ID('[dbo].[Test2]') IS NOT NULL
DROP VIEW [dbo].[Test2]
GO
CREATE 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.
October 5, 2017 at 1:27 pm
If the 2 instances are on the same machine, did you allow for the 2016 instance to have sufficient memory, assuming you allocated everything to your 2012 instance previously?
set the memory low on the 2012 instance, and higher on 2016 maybe.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
October 5, 2017 at 2:21 pm
The memory setting are the same on the 2 instance, and only one instance is running at a time during testing, so an instance cannot take resource to other.
October 6, 2017 at 2:52 am
Julien Maillard - Thursday, October 5, 2017 1:00 PMHello,
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 caseIF 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]
GOCREATE VIEW [dbo].[Test2]
AS
SELECT (SELECT [Test] AS [text()]
FROM [dbo].[Test]
FOR XML PATH, TYPE).value('.', 'nvarchar(max)') AS [Test]
GOSELECT (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 optionSET LEGACY_CARDINALITY_ESTIMATION = ON;on my database but no change for the performance.
I also try to set theSET 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.
The compile time of the SQL2016 plan was 84s. What happens if you run the query multiple times?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2017 at 9:32 am
Executing query multiple time doesn't change the perf.
But after trying different optimization, I found one which doesn't change the code a lot and works well.
To retrive the concatenated value in the xml string, I replace the .value('.', 'nvarchar(max)') by .value('.[1]', 'nvarchar(max)') for all queries (in the views and in the final select)
With this new code, the execution time is as instantaneous for SQL 2012 as for SQL 2016 🙂
February 8, 2018 at 7:33 am
Hello,
A quick news to say the issue is solved with SQL 2016 SP1 CU7, https://support.microsoft.com/en-us/help/4056955/long-time-to-compile-query-that-casts-string-or-binary-data-to-xml.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply