Solved

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

Posted on 2014-03-17
28
539 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
ID: 39934734
"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
ID: 39934982
>>>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
ID: 39935013
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
ID: 39935024
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
ID: 39935062
>> 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
ID: 39935120
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
ID: 39935122
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
ID: 39935184
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
ID: 39936229
Tell us about how the LUNs are configured on the EMC and the Netapp?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39937735
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
ID: 39938487
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
ID: 39938570
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
ID: 39938598
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
ID: 39938660
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Author Comment

by:dbaSQL
ID: 39938717
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
ID: 39938721
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
ID: 39939309
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
ID: 39939642
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
ID: 39940097
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
ID: 39940227
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
ID: 39940240
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
ID: 39940273
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
ID: 39940291
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
ID: 39940556
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
ID: 39940605
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
ID: 39940703
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
ID: 39941321
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
ID: 39949113
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Subtracting Amounts from "Among Rows" 3 41
ASP.NET 5 Templates 2 65
SQL Query stumper 3 36
SQL Field Length for Email Address 3 15
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

18 Experts available now in Live!

Get 1:1 Help Now