2 Replies Latest reply: May 26, 2017 7:11 AM by Phil Hummel RSS

sql .vhdx lun sizes for hyper-v 2016, 2012 R2 and best practices

admingirl

Hello,

 

Rather than use pass-thru disks(virtual HBA) for large sql VMs, the windows team wants large .vhdx files combining sql tembdb, logs, and data points on the same lun so that the VM can failover easily. Some of these Virtual HBA connected hosts are roughly 8 TB. My issue is that I prefer that the .vhdx files be broken up the same way I do the luns, with tempdb, logs, and data1 all on separate luns.

 

I have been looking for a best practices guide. I don't like using very large luns because of potential queue depth issues.

 

If anyone has any feedback that would be greatly appreciated.

 

Thanks so much!

Admingirl

  • 1. Re: sql .vhdx lun sizes for hyper-v 2016, 2012 R2 and best practices
    Mich

    Having worked with SQL DB administrators before I believe they have always advocated separate LUNs for the different uses. This is also recommended in the following White Paper from DELL EMC.

     

    https://support.emc.com/docu39798_White_Paper:_EMC_Mission_Critical_Infrastructure_for_Microsoft_SQL_Server_2012_-_EMC_Symmetrix_VMAXe,_EMC_FAST_VP,_SQL_Server_AlwaysOn_Availability_Groups,_VMware_vSphere.pdf?language=en_US

     

    There is a KB https://support.emc.com/kb/499112 that discusses a lot of the performance issue. They are for for VMware, but the principals are the same for Hyper-V.

    If you were talking about CSVs where the LUNs are being accessed by each host then maybe, but with SQL instances I would still recommend separating the Logs/tempdb etc on separate LUNs.

     

    Regards, Michel.

  • 2. Re: sql .vhdx lun sizes for hyper-v 2016, 2012 R2 and best practices
    Phil Hummel

    You are right to be concerned if the Windows team presents a single 8TB disk to the HyperV host and then uses that for lots of virtual disks that require "high" IOPS, queuing from the HyperV host will be an issue.  Do you have any data from Diskspd or SQL Server showing excessively high latency in this environment?

     

    There will also be disk queues managed by the Windows guest OS for each virtual disk.  Again the question is will the VM require multiple virtual disks to get adequate IOPS and latency.

     

    Another benefit of using more vhdx virtual files for a single SQL Server VM is that it give you better visibility into IOPS and latency data from perfmon.  If you mix data, logs, and tempdb files on one disk you lose the ability to measure latency for data and log files separately or to see how much IO is going to tempdb from perfmon.

     

    The last layer of disk abstraction is the SQL Server files that sit on your virtual disk.  Space for log files is typically laid out at creation and then used as "circular file" so space allocation during normal operation should not be an issue.  The evils of autogrow for log files is well documented.  Data files and TempDB files have to deal with space allocations for individual tables and indexes on an ongoing basis.  Having too few data files for user databases or TempDB can also lead to queuing and latency issues for high throughput SQL Servers.

     

    Diskspd is your friend.  You need to test IOPS and latency when the VM is created by the Windows team and turned over to the SQL team.  Let the Windows team know you are going to stress test the IO of the VM since it can impact other users of the HyperV environment.  You won't be able to get any information about SQL data file allocation issues from Diskspd.  Follow the guidance from Microsoft for SQL 2016.  SQL 2016 uses a much better file allocation for TempDB when the instance is created/restarted.  It is not a bad idea to follow that for user databases that are expected to handle high throughput at low latency.