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
  • 8,932
  • 6
  • 45
  • 51
Guido Neele
  • 181
  • 1
  • 1
  • 5
  • 1
    Murphy states that newer software would require more memory and more hardware than older software. . .. –  Apr 03 '14 at 20:09
  • 5
    First thing to do is to remove the Shrink DB step from the plan. – Joel Coehoorn Apr 03 '14 at 20:24
  • The next thing to look at: How often is your transaction log backed up? – Joel Coehoorn Apr 03 '14 at 20:25
  • 1
    Are SQL Server 2005 and 2012 the same editions (Enterprise, Standard etc.)? – JodyT Apr 03 '14 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 03 '14 at 20:43
  • Are the disks the same on both servers? – Katherine Villyard Apr 03 '14 at 21:51
  • 1
    Have you checked the server's power settings? In some cases Windows Server 2012 comes in Balanced by default. Switch to High Performance. – NothingsImpossible Apr 04 '14 at 00:42
  • 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 04 '14 at 03:18
  • Did you upgrade the os as well? If you upgraded the operating system AND SQL Server version, why didn't you upgrade the hardware? What else is running on this box/vm? I have seen antivirus software severely impact performance even with the recommended exclusions. Completely uninstall any antivirus software and see if performance improves. Simply stopping the service will not be sufficient because most corporate antivirus software use a filter driver. To repeat Kin...DO NOT have SHRINK in any maintenance plan. Either reorg the index & update stats, or rebuild it, not both. – stacylaray Apr 06 '14 at 05:49
  • Out of curiosity, are any of your table partitioned? – Solomon Rutzky Apr 06 '14 at 15:52
  • Wow guys and girls thank you for all your reactions. We decided to revert the database back to SQL Server 2005. That way the pressure is off and we can investigate the problem. This week our system administrators will check the server because I found out it wasn't fully patched and probably the same is true for SQL Server 2012. So we are investigating the hardware and OS first. When we have a definite answer later this week I will post it here or except one of the given answers. Thanks, learned a lot! – Guido Neele Apr 07 '14 at 17:54

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,225
  • 6
  • 120
  • 236
  • 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 03 '14 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 03 '14 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 03 '14 at 23:39
  • @AaronBertrand updated my answer to reflect your suggestion. Thanks ! – Kin Shah Apr 04 '14 at 00: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

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,565
  • 15
  • 22
  • 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 07 '14 at 17:59