SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance issue after migration from SQL Server 2012


Performance issue after migration from SQL Server 2012

Author
Message
Julien Maillard
Julien Maillard
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 313
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]
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.



Attachments
Test_ImbricatedView_2012.sqlplan (34 views, 156.00 KB)
Test_ImbricatedView_2016.sqlplan (23 views, 160.00 KB)
Henrico Bekker
Henrico Bekker
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25352 Visits: 6050
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
Julien Maillard
Julien Maillard
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 313
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.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166612 Visits: 21750
Julien Maillard - Thursday, October 5, 2017 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 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]
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.




The compile time of the SQL2016 plan was 84s. What happens if you run the query multiple times?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Julien Maillard
Julien Maillard
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 313
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 Smile

Julien Maillard
Julien Maillard
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 313
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search