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

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?
LVL 18
dbaSQLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
"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
dbaSQLAuthor Commented:
>>>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
dbaSQLAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dbaSQLAuthor Commented:
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
Scott PletcherSenior DBACommented:
>> 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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
very odd, some of these supposed to be in v2008 dmv's aren't there.
0
Scott PletcherSenior DBACommented:
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
Gerald ConnollyCommented:
Tell us about how the LUNs are configured on the EMC and the Netapp?
0
dbaSQLAuthor Commented:
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
Gerald ConnollyCommented:
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
dbaSQLAuthor Commented:
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
Anthony PerkinsCommented:
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
Gerald ConnollyCommented:
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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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
Gerald ConnollyCommented:
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
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
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
Anthony PerkinsCommented:
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
dbaSQLAuthor Commented:
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
Anthony PerkinsCommented:
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
dbaSQLAuthor Commented:
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
lcohanDatabase AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
but I wasn't able to find the same thing for v2005 enterprise.
Features Supported by the Editions of SQL Server 2005
0
dbaSQLAuthor Commented:
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
Gerald ConnollyCommented:
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
dbaSQLAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.