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
Improve this question
11
  • 1
    Murphy states that newer software would require more memory and more hardware than older software. . ..
    – remudada
    Apr 3, 2014 at 20:09
  • 5
    First thing to do is to remove the Shrink DB step from the plan.
    – Joel Coehoorn
    Apr 3, 2014 at 20:24
  • 1
    Are SQL Server 2005 and 2012 the same editions (Enterprise, Standard etc.)?
    – JodyT
    Apr 3, 2014 at 20:36
  • 2
    Shrinking the database can totally screw up your indexes and can massive fragmentation. If you insist on keeping that step, do this before you reorganize & rebuild your indexes, so that those indexes can be defragmented after shrinking the database. Or leave out that step altogether.
    – marc_s
    Apr 3, 2014 at 20:43
  • 2
    As @KatherineVillyard suggested, look at the physical disks in use, whether they're shared, use something like Piriform's Defraggler to check fragmentation on your MDF and LDF files. Run DBCC UPDATEUSAGE and DBCC CHECKDB with DATA_PURITY and EXTENDED_LOGICAL_CHECKS. And scrap Step 4 and step 2; step 4's horrific, and step 3 gives superior results to step 2. Load up Perfmon on the OS and check Avg sec/read and Avg sec/write latency on both machines. Watch IO throughput on both machines.
    – Anti-weakpasswords
    Apr 4, 2014 at 3:18

3 Answers 3

Reset to default
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.

Improve this answer
4
  • 1
    Moving compatibility level to 110 should be on your list too (with the caveat that it will require a full regression test).
    – Aaron Bertrand
    Apr 3, 2014 at 23:30
  • @AaronBertrand yes.. it is on my list (please refer to the link at the bottom that has more detailed steps - step 7 in the linked answer). Thanks for pointing it out.
    – Kin Shah
    Apr 3, 2014 at 23:34
  • 1
    I mention it because it's one of the first things I do - personally I think it deserves a bullet here rather than being squirreled away in another answer.
    – Aaron Bertrand
    Apr 3, 2014 at 23:39
  • @AaronBertrand updated my answer to reflect your suggestion. Thanks !
    – Kin Shah
    Apr 4, 2014 at 0:35
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

Improve this answer
1
  • 3
    Its very vague if this will help to resolve any performance issue.
    – Kin Shah
    Apr 3, 2014 at 21:33
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.

Improve this answer
1
  • The server is a VM and is setup the same as the previous server. I found out that the server wasn't fully patched and I think the same applies to SQL Server 2012 but I hope to figure this out later this week. I hope we will get administrator rights to the server because it was not supplied and configured by us. Another company setup the server and said it was ready to rock 'n' roll. We reverted back to the old server for now and will investigate the new server later this week. Will post the answer here.
    – Guido Neele
    Apr 7, 2014 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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