Has anyone seen much slower SQL Server application performance when upgrading from Windows Server 2008 R2 to Windows Server 2012 R2?
We recently did a side-by-side upgrade of an important server with this newer operating system, SQL Server 2012 to SQL Server 2014 SP1, Citrix XenServer to vmWare, and an older Nimble SAN to a newer Nimble SAN with a lot more SSD, but one Delphi XE application went from running a process in 2 minutes to about 42 minutes!
We saw similar performance issues with our PowerBuilder application on Windows 8 and Windows Server 2012 / 2012 R2 and changing from the SQLOLEDB driver to the SQL Server Native Client driver SQLNCLI helped a lot. In this case, we tested the Delphi application with the latest SQL Server Native Client 2012 SQLNCLI11.1 and it didn't help much at all. I read at https://msdn.microsoft.com/en-us/library/cc280510.aspx that Microsoft recommends using the new ODBC Driver 11 for SQL Server – Windows, so that's something else we can try. This really seems like a driver-related issue.
I really didn't find much on the web about similar problems except the Balanced Power savings option as Glenn Berry describes at https://sqlserverperformance.wordpress.com/2010/09/28/windows-power-plans-and-cpu-performance/. I verified that we are using the High Performance setting and confirmed this with the CPU-Z utility.
I have heard of some people experiencing regression queries with the new SQL Server 2014 Cardinality Estimator (CE), but when I changed the database compatibility mode from 2014 (120) back to 2012 (110), it didn't help. Our testing also found that performance was fine on Windows Server 2008 R2 with SQL Server 2014 SP1 and the 2014 (120) compatibility level.
This application is unfortunately using a lot of client-side cursors to query the data one row at a time with many round trips to the server, and the developer has rewritten much of it in a set-based manner with stored procedures which performs much better, but the new approach still has to go through additional testing and QA before we roll it out. I am also concerned about other applications that may suffer from similar problems.
In the meantime we rolled back to the old Windows Server 2008 R2 server with an in-place upgrade to SQL Server 2014 SP1. We will do more comparison testing in a test environment.
Since this may affect other applications, I may have to open a Microsoft ticket to find out what the root cause is, but I wanted to check here first.
Best Answer
Originally self-answered in comments:
After doing a lot of testing, this issue appears to occur when crossing the temporary network bridge from our new environment over to our old environment. Although we haven't figured out the root cause at the network layer, we have decided to leave the centralized server on our old environment and move it last during our infrastructure migration.
We kept seeing a lot of
ASYNC_NETWORK_IOwait types with the queries. After doing a lot of testing with different configurations and opening tickets with Cisco and vmWare, vmWare asked us to try to disable Receive Side Coalescing (RSC) on hosts.After making that change, performance was back to normal (actually better on the new equipment, which is what we expected). See the VMware Knowledge Base entry:
After upgrading a virtual machine to hardware version 11, network dependent workloads experience performance degradation (2129176).