October 7, 2014 at 12:17 am
Hi All,
Good Day. Primarily we have WH Solution in place running with the back end of SQL 2005 on Windows 2003 Server. Recently we have upgraded databases from SQL 2005 to SQL 2012 ENT Version 64 bit along with OS components of Windows 2012 R2 STD Version in Hyper V Platform. SQL 2005 was running on Physical Server with Windows Clustering and upgrade Virtual platform also similar concept architecture of physical platform. The legacy (SQL 2005 was running on Physical Servers and new SQL 2012 databases were running Hyper V). The real problem is SQL 2005 was performing much better than SQL 2012. Especially we are seeing slowness issues while processing large amount of volumes through front end.
We have enforced some of the better practices already as recommended by MS and still not getting the expected outcome. After doing all sort of investigation understand that, the bottleneck problem is with Disk I/O throughput.
Is connecting iSCSI LUN's in to Virtual Platform degrade the performance of DISK I/O ? Instead VHD will give better Disk I/O throughput when compared to iSCSI Disks?
It would really helpful if someone suggest the solution to improve performance in Hyper V farm.
Regards,
Thulasi.
October 7, 2014 at 4:38 am
Hello,
->What are the current bottlenecks on the new server? (wait_stats)
Can you use the new vhdx format instead of vhd?
You mention the upgrade of the sql server, did the OS of the VM upgrade as well? (windows 2003-> X)
Any competition from other VM's on the same host/storage?
Did/do the old server use the same storage as the new?
What is the connectionspeed with the storage/server: 1 GB/s, 10 GB/s ... (iscsi, fibre, direct attached, ...)
October 7, 2014 at 4:57 am
Hi Jo,
Thank you so much for your reply. Yes, the VM Host machine also upgraded to Windows 2012 STD 64 bit edition.
Wait Type - PAGEIOLATCH_SH 107
Avg. Disk sec/Transfer 82
Current Disk Queue Length 37.
The Storage remain same for all VM's in newly upgraded systems. SQL 2005 was running with different storage box and different location and new is NetApp Solution and old one is Dell.
Regards,
Thulasi.
October 12, 2014 at 4:19 am
Been away for a while.
I'm unfamiliar with isci-target (this technology?).
Is it possible to consult your Netapp vendor? Could only find best practices for sql server on Netapp, it doesn't mention isci-target
Statistics are updated?
October 12, 2014 at 4:27 am
Thanks. Ya stats are up to date and we are just revisiting the Disk Configuration in Hyper V platform today to address this issue. Will keep you inform about the outcome. Thanks once again.
October 13, 2014 at 4:52 am
Is TCP Offload disabled on the Hyper-V host and on all guest machines?
TCP offload was originally designed to improve performance by forcing the network adaptor to do some of the IP packet management. The problem today is that the server CPUs have far more capacity than the adaptors to do this work, and allowing TCP offload can harm performance.
We run SQL Server in AWS, and found that disabling TCP offload gave us a performance boost.
More details about disabling TCP offload are in Step 7 of http://support.microsoft.com/kb/976640
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 14, 2014 at 3:57 pm
There are different thoughts around Virtual vs IScSI in different scenarios , I prefer to be on ISCSI when my database sizes are huge.
Apart from that , I see the disk queue length is high, Are data , log and tempdb are all on separate drives ? if so are all they separate luns till to the root physical layer.
October 15, 2014 at 1:55 pm
trayalacheruvu (10/7/2014)
Hi All,Good Day. Primarily we have WH Solution in place running with the back end of SQL 2005 on Windows 2003 Server. Recently we have upgraded databases from SQL 2005 to SQL 2012 ENT Version 64 bit along with OS components of Windows 2012 R2 STD Version in Hyper V Platform. SQL 2005 was running on Physical Server with Windows Clustering and upgrade Virtual platform also similar concept architecture of physical platform. The legacy (SQL 2005 was running on Physical Servers and new SQL 2012 databases were running Hyper V). The real problem is SQL 2005 was performing much better than SQL 2012. Especially we are seeing slowness issues while processing large amount of volumes through front end.
We have enforced some of the better practices already as recommended by MS and still not getting the expected outcome. After doing all sort of investigation understand that, the bottleneck problem is with Disk I/O throughput.
Is connecting iSCSI LUN's in to Virtual Platform degrade the performance of DISK I/O ? Instead VHD will give better Disk I/O throughput when compared to iSCSI Disks?
It would really helpful if someone suggest the solution to improve performance in Hyper V farm.
Regards,
Thulasi.
You need to involve your SAN admin here.
There are two type of latencies on a SAN: front end and back end. He needs to help you and find current values.
Now, I am not familiarized with HyperV, I use VMware, but depending of how the LUN or disk was formatted, that will also affect your disk's performance. The same is also true for the cluster size (disk's partition)
Use SQLIO (during a maintenance window) and check your MS-SQL latencies. That should be your 1st step. You need to find what are the current IOPS for your current configuration. You can compare, if old box is still around, against the previous system.
But to answer your question. iSCSI is basically a protocol that uses networks to make remote disks available as local. Properly configured should not be an issue, but I've seen some implementations that run faster and are easier to setup using default virtualization protocol.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply