Performance problem after migration from SQL Server 2005 to 2012

Question

Last Friday, I migrated a SQL Server instance from 2005 Enterprise Edition to a brand new Windows Server 2012 server with SQL Server 2012 Enterprise Edition installed. Since then, users have started to complain about the application performance.

Here is some information about the server:

• Virtual server on VMWare 5.5
• 4 vCPU and 24 Gb RAM. On the previous configuration, 10 Gb was necessary but the tempdb database was smaller than what I set (almost 6Gb)
• I now have set the maximum memory target to 22 Gb and tempdb is totally in Buffer Pool
• After migration (performed using Database Mirroring), I ran update statistics, index rebuild, and other maintenance commands

I don't know where to find answers. If everything except the SQL Server instance is the same (VM, Disks, etc) then, to me, the problem is in SQL Server configuration, but where? I have tried some changes but none of them seem to have been useful.

Do you have any ideas?

Additional information from comments

• I ran sp_Blitz which gave me some information, but nothing new from SQL 2005 (slow storage for instance)
• The memory consumption (20Gb + ) is mostly due to tempdb (6Gb) and the remainings are mostly taken by an application database
• PLE flows and sometimes goes down to 150. At the beginning of the day, most of the data is out of the cache
• The max memory setting was 10Gb on the previous server
• We use Idera diagnostic manager
• The max server memory is set to twice the value it was on the previous server.
• The page life expectancy drops below 150 at least twice a day

sp_BlitzIndex analysis

I started an analysis with sp_BlitzIndex and in addition to poorly written code, it showed this:

• Aggressive Indexes: Total lock wait time > 5 minutes (row + page)

  dbo.TABLE1.PK_TABLE1 (1): 
  Row lock waits: 3,591; total duration: 10 minutes; avg duration: 0 seconds; 
  Page lock waits: 23; total duration: 15 seconds; avg duration: 0 seconds; 
  Lock escalation attempts: 510,489; Actual Escalations: 1.
  
  dbo.TABLE1.I_TABLE1_CNTTYPE_CATEGORY_IS_CURRENT (85): 
  Row lock waits: 155; total duration: 48 seconds; avg duration: 0 seconds;
  Page lock waits: 129; total duration: 8 minutes; avg duration: 4 seconds;
  Lock escalation attempts: 29,423; Actual Escalations: 4,951.
  

Would just an additional index creation do some changes in that phenomenon?

sp_configure

I've compared the output of sp_configure as requested. Here are the differences:

Config                          Old     New
Blocked process threshold       0       120
Maximum Degree of parallelism   0       4
Maximum Memory (MB)             10240   22000

The power option is already at High Performance. I set the memory back to 10 Gb with this command:

CHECKPOINT ;
DBCC DROPCLEANBUFFERS ;
EXEC sys.sp_configure N'max server memory (MB)', N'10240'
GO
RECONFIGURE WITH OVERRIDE
GO

After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

Analysis of the Diagnostic Manager CPU statistics Report

The CPU statistics reports :

• an average SQL compilation of 500
• an average SQL Recompilation of 120
• Up to 10 lock waits per minute, 5 on average
• And mostly, a Table Lock Escalation which is on average of 40.

I've already set the Optimize for adhoc workload server setting and even "forced parametrization" for the most used user database.

So far, nobody told me there have been performance improvements. As it's a database I got back and was not managed by the DBA team, we don't have the background to check if it came back to a normal situation or not...

I will wait for some time and see whether it's ok for now or not. Thank you all for your help !

Answer 1

The most likely explanation is that your queries are being executed using different execution plans on the new server, due to changes in statistics, index size and density, and configuration settings.

The configuration settings that have the biggest impact on query optimizer execution plan choices are:

• max server memory
• max degree of parallelism
• cost threshold for parallelism

The question notes that memory has doubled on the new installation, so this is very likely to be the major factor. Intuitively, we might expect more memory to always improve performance, but this is not always the case because the optimizer may start to favour plans with more hashing, sorting, and scans instead of nested loops and index seeks.

For more information, see my previous answer:

• difference in execution plans on UAT and PROD server

A setting of 20GB would not normally be large enough to justify setting startup trace flag 2335 (as referenced in that answer), but only testing would prove that one way or the other.

As a potential quick fix, you might consider setting max server memory back to the previous value, while you work on identifying query plans that regressed on the new server, and correcting the underlying causes using normal tuning methodology. I apologise if that sounds generic and hand-wavy, but the reality is that poor performance has very many possible causes, and there are established ways to identify and correct most common ones. The goal of my answer here is to get your system back somewhere close to where you were on 2005.

---

After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

The tempdb database may have grown previously to accommodate sorts and hashes that ended up spilling from memory; it may not need to be so large now. The tempdb database does not use memory any differently from any other database. PLE "hardly at 490" makes no sense as an English sentence.

The important point of setting max server memory back to 10GB was to encourage execution plans similar to the 2005 installation, thereby returning performance to acceptable levels. You don't say if it has helped or not. It is likely you need local professional support at this stage; I think we have reached the limit of what can reasonably be done in Q & A format.