Solved

Migration from SQL v2005 Enterprise to v2008 R2 Standard -- terrible performance

Posted on 2014-03-17
28
534 Views
Last Modified: 2014-03-23
A customer of mine is trying to migrate from v2005 Enterprise to v2008 R2 Standard Edition.  They have 20 databases, and all but two have been migrated successfully.  The last two databases are mostly OLTP.  They attempted to migrate them, but the performance was terribly degraded.  They could not identify the problem, so they rolled them back.  

I am stepping in to try and help them identify the source of the problem.  Initially, of course, I am going to assess both instances and compare the obvious (configuration, settings, etc.).  But remember, 18 of their databases migrated just fine.  These last two, for some reason, had very notable performance degradation.  

I noted the builds for both environments below, and I was hoping for insight on the best approach.  Possibly somebody has seen this before, or at least can advise on a suggested approach for my diagnostic efforts.


v2005 - 4 servers in a Windows cluster (3 active, 1 passive) and another server housing mirrors for two databases.  SQL v2005 Enterprise is on each server.  only one instance per server, all disk is on EMC

v2008 is 4 two-node active/active clusters -- 3 are production, 1 is test.  v2008 R2 Standard Edition is on each server.  Only 1 instance per server, and all disk is on netapp.




Personally, I am a huge advocate for the Enterprise edition.  They are moving from Enterprise to Standard --- but, surely that isn't the sole source of this degradation.  Any advice?
0
Comment
Question by:dbaSQL
  • 15
  • 5
  • 4
  • +2
28 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"They attempted to migrate them, but the performance was terribly degraded."
Was the performance degraded after migration and they were brought up on line for OLTP processing?
Was a DBCC CHECKDB done after migration?
Was a RE-INDEX for ALL tables/indexes done after migration?
Was a Update Statistics done after the RE-INDEX above?
What SQL Server 2008 Service Pack is installed?

Regardless of all the above which will help improve performance on the SQL 2008 Standard your issue is caused NOT by downgrade in edition but the downgrade in disk system where SQL Database are located from "all disk is on EMC" to "all disk is on netapp".

That's why only heavier (and most likely larger) used OLTP database are having trouble to keep up in my opinion due to worse IO throughput.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
>>>Regardless of all the above which will help improve performance on the SQL 2008 Standard your issue is caused NOT by downgrade in edition but the downgrade in disk system where SQL Database are located from "all disk is on EMC" to "all disk is on netapp".

Can you elaborate, please?  EMC vs NetApp -- what tells me that one is any better than the other?  How would I confirm this?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I am VERY anxious for your elaboration on the disk, lcohan.  i am onsite reviewing the resource, and something odd just came to my attention.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Yet these objects to not exist:
sys.dm_os_windows_info
sys.dm_server_memory_dumps

Any reference fails with these:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_server_memory_dumps'.

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_os_windows_info'.


Were these DMVs not introduced in v2008 R2 ?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
same thing with sys.dm_os_volume_stats
sql says it doesn't exist.

If I can't look at the drive stats, I don't know how I am going to support that it is the disk.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> Was a Update Statistics done after the RE-INDEX above? <<

You should never update stats after rebuilding an index -- the index rebuild will give you far better stats than an update statistics would.


But then again, you don't need to re-index simply because you've gone to another version.  You could the rebuild stats instead.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
yeah, I'm not worried about the stats, indices or any CHECKDB right now.  the performance difference between one and the other is night and day.  and, the databases are so miniscule.  practically not even there.  this is not fragmentation, or outdated stats.  

it's got to be something else.

I'm still collecting data from the new box, which is v2008 r2 standard.  config, file/disk stats, etc.  then I will do the same to the other box that they are still running in production, which is v2005 enterprise.  then I will compare the two.

this is the new instance:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

this is the old instance:
Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
very odd, some of these supposed to be in v2008 dmv's aren't there.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
Yeah, those DMVs should be there, that's troubling too.

Check the dbs and make sure they don't have autoclose specified -- that can cause massive performance issues; that is, if autoclose is on, turn it off, pronto!
0
 
LVL 16

Expert Comment

by:Gerald Connolly
Comment Utility
Tell us about how the LUNs are configured on the EMC and the Netapp?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Specifically in what respect, Gerald?

>>>>
Regardless of all the above which will help improve performance on the SQL 2008 Standard your issue is caused NOT by downgrade in edition but the downgrade in disk system where SQL Database are located from "all disk is on EMC" to "all disk is on netapp".

That's why only heavier (and most likely larger) used OLTP database are having trouble to keep up in my opinion due to worse IO throughput.
>>>>

lcohan, are you able to elaborate at all?  this is very pressing, i need to be sure that I understand what you are saying.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
Comment Utility
Well the biggest change appears to be your underlying storage infrastructure. Your performance hits may be related to this change. EMC and NetApp in General have different storage philosophies.

So are your DBS on block storage LUNs on the EMC box, and Filer based on the NetApp?

What are the RAID types and sizes on the different boxes.

What does the connection infrastructure look like for EMC and for the NetApp?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Both Gerald and lcohan have now suggested the same with the disk.  Specifically, EMC vs NetApp.  I understand how the disk plays into the server's performance, but what I am asking is there anything definitive here that makes NetApp worse than the EMC?  The performance between the two instances truly is severe.  Great, if that's all the NetApp.  But why?  

Netapp -
   Each Agg contains all volumes, and is configured for raid dp.
   Each volume contains only one LUN
   Each LUN is presented to the host as a single drive

EMC -
  Each LUN is in a Specific Raid Group.  (some are R5, some are R10)
  Each LUN has its own storage group for the host access
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
You have not mentioned memory.  Remember that Standard only supports up to 32GB, so if the old box had 128GB then that may explain the difference.

Yet these objects to not exist:
It sounds like they are missing the DMVs that did not exist in SQL Server 2005.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
Comment Utility
So how many disks/spindles are involved on both the EMC and the NetApp?

We're the DBs that have the problems on RAID-5 or RAID-10 on the EMC?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
the raid 5 was just for the backups, Gerald.  it's not an issue of RAM either.  The original server, which they are trying to migrate away from, has 42G.  The new server, that they are trying to migrate to, has 64G.

I see a number of obvious weaknesses.  For example, tempDB sits on the same drive as the database data files, and it has only one data file.  That is the server they're trying to migrate to.  Yet it has two data files on the old one, that performs better. Still not quite the TempDB that I would have built -- but, I wonder if that oversight with the tempDB is enough to cause this great of a performance difference.

I should review the writeup on what limitations Standard edition has, in comparison to Enterprise.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Box #1 is v2005 Enterprise.  Box #2 is v2008 Standard.  It's a little hard to compare just standard vs enterprise, due to one being 05 and the other being 08.   ugh

v2005 Enterprise outperforms v2008 Standard.

are the limitations of standard  + the poorly configured tempDB enough to do this?

...and maybe + the inadequacies of NetApp?   I say maybe, because I still don't have the answer on why EMC may be better than NetApp.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
Comment Utility
Re EMC v NetApp, without looking at the detail setup of the these two devices it's hard to advise if Your NetApp Filer configuration is inadequate as opposed to the EMC box.

It maybe that the NetApp box is better than the EMC, but is just configured incorrectly or that placement is incorrect, or that the infrastructure for the NetApp box just isn't up to coping with these last two DBs.

We can't help if you don't give us the information!
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
with exception of the spindle count, I have given what has been asked for, Gerald.  lcohan made a very specific statement early on --

>>>but the downgrade in disk system where SQL Database are located from "all disk is on EMC" to "all disk is on netapp".<<<

suggesting that the move from EMC to NetApp was a downgrade all by itself.  I am hoping for clarification in this regard.  

I was trying to find the product specifications for v2005 enterprise as well as those for v2008 standard, such that I could do a side by side.  you know, cpu limitations, ram limitations etc.  does anybody have this reference?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
auto close isn't on, scott.  I forgot to get back to you on that.

anybody have the paper on the max/limitations for v2008 standard, as well as v2005 enterprise?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
The new server, that they are trying to migrate to, has 64G.
Only 32GB are supported on Standard Edition

For example, tempDB sits on the same drive as the database data files
Aren't you confusing logical drives with physical drives?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
thank you, Anthony.  do you know where I can find the paper to actually support this?  both the ram limitations, as well as cpu, per edition?

as for tempdb, no.  I'm not confused.  it's all sitting on the same physical drive.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
It is here:
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).ASPX
And I was wrong SQL Server standard supports up to 64GB.  It is the Standard O/S that supports 32 GB.

 it's all sitting on the same physical drive.
I cannot see how that is possible, but I will take your word for it.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
thank you Anthony.  I found this link last night, too, but I wasn't able to find the same thing for v2005 enterprise.  their installs are not using defaults.  or maybe they were, but somebody went in afterward to relocate the tempdb.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
Comment Utility
http://blog.pluralsight.com/emc-vs-netapp-storage-wars

"EMC uses solid-state drives and automates storage tiering via its Fully Automated Storage Tiering program. NetApp, meanwhile, creates a virtual storage layer. It does not use any software."
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
Comment Utility
but I wasn't able to find the same thing for v2005 enterprise.
Features Supported by the Editions of SQL Server 2005
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
anthony, big thank you!

>>>"EMC uses solid-state drives and automates storage tiering via its Fully Automated Storage Tiering program. NetApp, meanwhile, creates a virtual storage layer. It does not use any software." <<<

lcohan, is it that simple? solid state vs not?  I am meeting with these guys on Friday.  if I can provide evidence of this nature, my job is done.

As of this posting, back in August, it looks like NetApp was beginning to introduce SSD's in their platform.  See the "Using SSDs for Persistent Storage and Flash Pools" section.
https://communities.netapp.com/community/netapp-blogs/netapp-360/blog/2013/08/29/netapp-storage-subsystem-design-solid-state-drives

Don't take offense, I haven't looked at the two side by side before, and I want to believe that what you've said is what it is.  Given the reference I pasted above, would you say the same?
0
 
LVL 16

Assisted Solution

by:Gerald Connolly
Gerald Connolly earned 50 total points
Comment Utility
EMC has products that range from the the low-end to the very high top-end of capacities and performance.
NetApp products tend to be more midrange.

A bland statement that EMC is better than NetApp is disengenious as it doesn't take into account the details of the boxes being compared. Are we comparing apples-with-apples, or apples-with-oranges here?

Not only does the box matter the storage setup makes a difference, you have already said that the EMC is using RAID-1 & RAID-5, but that the NetApp box is using Double Parity which typically hits write performance.

Both EMC and NetApp have SSD capability in a range of their products, but it sounds like you already have a hardware setup, you just need to find out why these two DBs perform badly:
Could it be the disks are slower
could it just be down to the DP config of the LUNs?
Could it be there just isn't the IOPS available for your workload?
could it be your infrastructure pipe is oversubscribed?
could it be the NetApp box is just too slow to cope?
Etc
Etc
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
I don't believe there is one specific problem.  Rather, it is a combination of several things.  Misuse and mis-configuration is probably the best label for what I have found -- from the LUN separation (or lack thereof) with NetApp, to the several different mis-configurations within SQL Server itself.

Thank you each for your time, and your input.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now