Hardware config requirements for data warehouse server

Posted on 2012-09-06
Last Modified: 2016-02-18
Please advise abt the hardware config required for data warehousing
(SSIS,SSAS,SSRS) to be installed on Hyper V vm?
Question by:isonali
    LVL 35

    Expert Comment

    by:David Todd

    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?

    Author Comment

    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
    LVL 35

    Expert Comment

    by:David Todd

    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'?

    LVL 28

    Expert Comment

    by:Ryan McCauley
    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.

    Author Comment

    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.
    LVL 28

    Accepted Solution

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now