2

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.

  • Yes. The vmWare server was set to use a 64 GB memory reservation and all the system and SQL Server memory counters described at solutioncenter.apexsql.com/… looked very healthy. We set SQL Max Memory according to formula described at sqlskills.com/blogs/jonathan/… and there was plenty of free memory and no memory pressure that I saw at all. Thanks for your help. – Mike Petrak Feb 15 '16 at 15:55
  • You state you did a side-by-side upgrade, when you do your test are both instances running, or just the one you are testing? The process that went to 42 minutes, what does SQL Server show it is waiting on? Was that the first time you ran that process under the new version? – Shawn Melton Feb 15 '16 at 16:29
  • Thanks, Shawn. The original was a default instance of SQL Server 2012 on a Windows Server 2008 R2 OS and the new one was SQL Server 2014 SP1 on a Windows Server 2012 R2 OS. SQL Server really didn't show a single query waiting on anything...it was hundreds of queries with very low costs each executing quickly but coming in very slowly from from the client application. The developer was testing on Windows Server 2008 R2 with SQL Server 2014 SP1 and SQL Server 2012...we should have had a test environment with Windows Server 2012 R2 for a more accurate test...we are building one now. – Mike Petrak Feb 15 '16 at 16:34
1

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_IO wait 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.