Hardware config requirements for data warehouse server

Please advise abt the hardware config required for data warehousing
(SSIS,SSAS,SSRS) to be installed on Hyper V vm?
SP_2018 .IT ConsultantAsked:
Who is Participating?
Ryan McCauleyData and Analytics ManagerCommented:
It seems that the VHD vs. Physical mapping doesn't make as large a difference as I was led to believe - throughput looks to be roughly the same:


I don't think this was always the case - at least, not on ESX, which is where we do most of our virtualization and where a pass-through disk is quite a bit quicker (in latency, if not really in throughput).

SAS 15k in a RAID-10 will be quick, and may be close on throughput, but still won't rival an SSD for access times. Also, your iSCSI connection is limited to 120MB/sec in ideal conditions - if the network is quiet, you've got flow control configured properly, and there's no other contention - and in most cases, you'll see a bit below that. It may still be enough to service your needs, but if you've got the option to go 10Gbit, I'd consider that.

At the end of the day, the only way to know how your configuration is perfoming is to actually testing. I've used SQLIO before, and there's a great walk-through here:


His script is overkill - each of those lines runs for two minutes, making the whole script something like 8 hours of stress test. However, dropping it to 15 seconds/line (or only running a few different lines) will give you a pretty clear overview of the performance you're seeing. If you run it both on your fast and your "slow" server, it should let you know pretty starkly what the issue is.

For example, running this tool showed that one of my LUNs have appalling performance (4MB/sec), even though it was on the same SAN as some other LUNs that were great (400MB/sec). It turns out that the partitions weren't aligned properly when they were created, leading to a terrible bottleneck - reformatting the partition in question with proper alignment got things running at full speed, but it took actually comparison numbers before I could engage the storage team and have them look more deeply into the issue (rather than just anecdotal "it's being slow" stories I'd been giving them).

Let me know if you have any questions!
David ToddSenior DBACommented:

This is a piece of string questiion.

Is this new, or an upgrade/migration/consolidation from another system? What sort of resources does the previous system(s) have?

If what are are going to be doing is of any size, then they need to be separate machines, vm or physical.

Depending on the size of the cube(s) for SSAS it can consume a fair amount of ram.

The key thought is that after all the overhead from Hyper V has been deducted, what are you presenting to the guest systems?

I've seen production web servers (which is what SSRS is) on 1U single processor servers, and SSAS sharing a 4GB physical machine with SQL.

If this is a new hyperV server, then obviously get as much as you can and its easy to balance off the competing needs  by adjusting the VMs.

Is disk going to be local to Hyper V or from a SAN?

My first thoughts

What sort of thing(s) are you doing with SSIS?
SP_2018 .IT ConsultantAuthor Commented:
Thanks for the advise.

I have installed one sql server on  hyper v vm having enterprise edition, 2 vcpus, 32gb ram , disk storage is from SAN , we also have another standalone physical server which has
a SSD drive on it and users compare running queries on  both servers and find queries on
server with ssd hard drive outperform at times.
Can hardware configuration on vm be further improved to match the physical server.
Currently ETL is performed on vm server with help of stored procs without using SSIS\DTS .
We plan to configure few more SQL  servers for SSIS and SSAS implementation
Can you please advise what would be the best hardware configuration on hyperv vms and how performance be improved on Hyper V

Thank you
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

David ToddSenior DBACommented:

Its pretty difficult to beat a physical machine with SSD drives.

Short answers - get a SSD based SAN
Give the VM more CPUs
I think 32GB of ram is Windows Standard max. Any more and you will need to upgrade the OS.

What have you done to tune the disks in the SAN for SQL?

What version/edition of guest OS? Did you know that you can speed up SQL by making sure the disk partition alignment is 'correct'?

Ryan McCauleyData and Analytics ManagerCommented:
SQL Server performance is often limited by the speed of the underlying disk storage (not always, but I've found it's a factor more often than it's not in performance issues). With your comparison server running an SSD, even Fiber Channel drives would be hard up to compete with it. What kind of storage is underlying the SAN you're connected to? Does it have SSD as well, or is it Fiber channel (or SAS? Or SATA?) - that can make a big difference. Also, are you accessing it via iSCSI - if so, at what speed - or via an HBA? An HBA should be able to match the performance of a local drive, but iSCSI, even on a gigabit link, can be limited during burst periods.

Also, is the storage you're using with your Hyper-V VM mapped directly to the LUN, or are you using a standard virtual hard drive file? If you're using a VHD, that's an extra layer of abstraction, and I've see it slow disk access down significantly - for maximum performance, you should be attaching your LUN directly to the VM using the native disk option in Hyper-V.

Given the proper configuration and high-speed underlying storage, a VM should perform similarly to a physical server of equivalent specs - they're nothing inherently slower about using a VM, but it takes some extra care with configuration.
SP_2018 .IT ConsultantAuthor Commented:
What kind of storage is underlying the SAN?
is it Fiber channel (or SAS? Or SATA?)
The drives themselves are SAS, 15K rpm.  The array is using RAID 10 in both the existing source array and the new target array.

Also, how is it being accessed?  is it via iSCSI - if so, at what speed – or is it via an HBA?
iSCSI, no HBA.  The network is all 1GB (not 10GB).  The SAN traffic is all isolated to a SAN-only VLAN.  The SAN traffic uses Jumbo Frames and Flow Control.

Also, is the storage used on Hyper-V VM mapped directly to the LUN, or is it a standard virtual hard drive file?
 They are all standard virtual hard drive files.  Having the VM directly map a drive is supposed to be slightly faster, but Microsoft’s guidance indicated that it would be on the order of a few percent.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.