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.