5

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
marc_s
  • 9,052
  • 6
  • 46
  • 52
Guido Neele
  • 181
  • 1
  • 1
  • 5

3 Answers3

6

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.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

Came across this issue as well. We are keeping our 2008R2 edition for this very reason.

Starting with SQL Server 2012, each edition of SQL Server has two compute capacity limits: A maximum number of Sockets (Same as Physical processor or Socket or Processor package). A maximum number of cores as reported by the operating system.

http://technet.microsoft.com/en-US/library/ms143432(v=sql.110).aspx#Engine

user36643
  • 1
0

"We made a full backup and restored it on the new server. The server specification are the same for both:

X5650 @ 2.67GHz (4procs) 16 GB RAM"

You bought a new server with the exact same specs as the old one? Or is this a VM?

Including all the points Kin made above, you need to install SP1 up through the latest Cumulative Update. There are many performance fixes including:

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012. IX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012.

FIX: "Non-yielding Resource Monitor" when you run a workload that executes concurrent queries in SQL Server 2012.

FIX: The threads are not scheduled evenly in SQL Server 2012 Standard Edition.

FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2. Memory dump may be created when you run a query in SQL Server 2012.

Community
  • 1
stacylaray
  • 2,585
  • 16
  • 22