October 4, 2017 at 12:54 am
A database was upgraded to 2016 and certain queries (views) are exhibiting terrible performance. On the previous server (2008 R2), these queries would complete in under 2 minutes (not great)...on the new server, we haven't given them enough time to complete....they will run for over an hour. I've updated statistics, tried clearing the plans and rebuilding using different cardinality estimators and I've rebuilt indexes. I'm comparing query plans across the servers and the only thing that's really standing out are some convert_implicit warnings. Anyone have any thoughts? I know its not a lot to go on, but maybe there's something I haven't considered.
October 4, 2017 at 1:02 am
Jeff Shurak - Wednesday, October 4, 2017 12:54 AMA database was upgraded to 2016 and certain queries (views) are exhibiting terrible performance. On the previous server (2008 R2), these queries would complete in under 2 minutes (not great)...on the new server, we haven't given them enough time to complete....they will run for over an hour. I've updated statistics, tried clearing the plans and rebuilding using different cardinality estimators and I've rebuilt indexes. I'm comparing query plans across the servers and the only thing that's really standing out are some convert_implicit warnings. Anyone have any thoughts? I know its not a lot to go on, but maybe there's something I haven't considered.
Have you tried running the queries using the old cardinality estimator?
😎
Suggest you run them to a finish and gather the actual execution plans, hard to tell what is going on without more details.
October 4, 2017 at 1:11 am
Eirikur, I have tried both estimators. I've got nothing here. I will run it to completion the first opportunity I have.
October 4, 2017 at 8:27 am
You should report this to MS, or give enough DDL and plans for someone else to report. They are always looking for potential regression issues, if that is what this is.
October 4, 2017 at 10:20 am
Jeff Shurak - Wednesday, October 4, 2017 1:11 AMEirikur, I have tried both estimators. I've got nothing here. I will run it to completion the first opportunity I have.
Is this on the same hardware or if not, what are the old vs. new specs (detailed incl. storage)?
😎
I have seen this on an instance where the system DBs, including the tempdb, were installed on the (relatively slow) operating system drive and the previous instance had the tempdb on a fast separate drive. Turned out that a distinct order by multi column hash was spilling into tempdb. When I moved the tempdb to a separate drive, rewrote the query to eliminate the spill and the distinct, it went from 17+ hours to < 2 sec.
October 4, 2017 at 10:21 am
Steve Jones - SSC Editor - Wednesday, October 4, 2017 8:27 AMYou should report this to MS, or give enough DDL and plans for someone else to report. They are always looking for potential regression issues, if that is what this is.
I've got the feeling that we may not have the same type of apples on each side of the equation.
😎
October 4, 2017 at 10:29 am
If you set the compatibility level on the database to 2008, do queries still take a long time to run?
October 4, 2017 at 10:48 am
These are virtual machines, esx 5.1. Both on windows 2012 R2.
On the disk front, two vmdks, one for OS, one for everything else. Thick eager porovisioned. Average Disk Queue length have averaged 1.00002 but spiked at 203 briefly during index maintenance.
The older server has 4 cores, 16gb ram, new machine as 8 cores and 16 gb ram. New server has server settings for CTFP of 50 and maxdop 4....this was configured to allow a mixed query environment, some oltp and some large reporting queries. The previous server had the defaults for these values. Additionally, the new server is synchronous AG with automatic failover. Waits stats....CXPACKET is all the way up at 90% followed by SOS_SCHEDULER_YIELD at 5.6%.
I've tried different compatibility modes with no avail.
I know, I need to provide specific query details and execution plans.
October 4, 2017 at 10:52 am
If you could post the execution plans, actual from the old server and at least an estimated plan from the new, that might help us help you.
October 4, 2017 at 12:05 pm
You are probably past this point so apologises for stating the obvious but just in case it's relevant:
If you are running the queries from a client it might be worth checking the network card on the new VM. Some of the default cards have performance issues. I think we swapped ours to E1000.
October 4, 2017 at 2:18 pm
Eirikur Eiriksson - Wednesday, October 4, 2017 10:21 AMSteve Jones - SSC Editor - Wednesday, October 4, 2017 8:27 AMYou should report this to MS, or give enough DDL and plans for someone else to report. They are always looking for potential regression issues, if that is what this is.I've got the feeling that we may not have the same type of apples on each side of the equation.
😎
Ditto, but if there is an issue, they certainly want to know.
October 5, 2017 at 9:37 am
A few years ago we had a similar problem when upgrading from SQL Server 2000 to 2008 R2. There was some complex code which involved views joined to views that also called scalar functions, the code took a few minutes to run on the old servers, but potentially took hours on the new servers. I compared the plans (which were quite complex) and IIRC SQL 2000 was doing merge joins and 2008 R2 was doing nested loops. The fix was partly re-writing the query, also adding in join hints to use merge joins instead of nested loops.
Obviously each case is different, but if you can get the 'before' and 'after' execution plans, this should eventually show you what needs to be done.
October 5, 2017 at 1:28 pm
Hello,
I've just post the same kind of issue with view after migrating from SQL 2012 to SQL 2016, https://www.sqlservercentral.com/Forums/1901060/Performance-issue-after-migration-from-SQL-Server-2012
I've identify, in my case, the issue was due to imbricated views that is known as not well perform, but the performance are acceptable on SQL 2012.
When we migrate on SQL 2016 the response time of the views exploded !
October 6, 2017 at 6:55 am
Have you refreshed the views?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply