SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server.

Problem now is that the database on SQL Server 2012 is much slower than it was on the Windows 2003 server with SQL Server 2005. What's even more worrisome is that the exact same maintenance plan (that took 37 minutes to run on SQL Server 2005) is now taking more than 2 hours and uses 16GB of memory and the tempdb is filling up the empty space on the C: drive. What's going on?

The server specification are the same for both:

• X5650 @ 2.67GHz (4procs)

• 16 GB RAM

The maintenance plan consists of the following steps:

• Step 1: Check DB integrity

• Step 2: Reorganize Index

• Step 3: Rebuild Index

• Step 4: Shrink Database

There can be many things to address. Below is an outline of things to check before jumping on any conclusions :

First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance.

Read up - Why you should not shrink your data files by Paul Randal.

Below are my common post restore steps for any lower version to higher version migration. In your case SQL Server 2005 to 2012.

• change compatibility level on the restored databases to 110 (with the caveat that it will require a full regression test)

• run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS

• Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN

• Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's Index and Stats maintenance scripts.

• Recompile all SP's using sp_recompile 'procedureName'

• Refresh your views SP_REFRESHVIEW view_name

----------

As a side note:

• turn on Instant File Initialization on the new server.

• Have multiple tempdb data files with equal size.

• Enable Trace Flag 1118

• Configure max and min memory correctly. Especially Max memory away from default.

• Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.

• Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.

• You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.

• Enable Optimize for ad-hoc workloads and Default backup compression options.

Refer to UPGRADE and POST UPGRADE steps for more details.

StackExchange Database Administrators Q#62498, answer score: 6