Databases Reference
In-Depth Information
If you are using Dynamic Memory, then you should set the Startup RAM value in Hyper-V to repre-
sent as much memory as SQL Server will normally need, and set the Maximum RAM value
to allow for any extra memory you think the virtual server might be allocated in a peak
workload situation. In my experience, setting Max Server Memory to be 2GB lower than the
Maximum RAM value set in the Hyper-V coni guration allows SQL Server to increase its memory
utilization as more memory is allocated. Note that this situation requires the Standard or Enterprise
Editions of SQL Server 2012, or the Enterprise Edition of previous versions.
Common Virtualization Design Mistakes
In my role as a consultant, I regularly work with many different instances of SQL Server, an
increasing number of which now run on virtual servers. Each virtual environment I see them run in
has its strengths and weaknesses. The following list describes some common design decisions
that have a negative impact on SQL Server:
➤
Too many vCPUs
— As mentioned earlier, the more vCPUs a virtual server has, the longer
the virtual server potentially has to wait for sufi cient underlying physical CPU cores to
become available in order for it to execute a CPU instruction. I've seen virtual servers
running fairly light SQL Server workloads that have 4, 8, or even 16 vCPUs assigned to them
“because they could” while in fact they could have performed comfortably with just two.
➤
Unmanaged memory coni gurations
— Sometimes the coni guration options in the hypervisor
make it seem like you can assign as much memory as you like, and it will take care of
i nding and allocating all the memory required. There's some truth in that but you still need
to account for all the memory assigned to virtual servers, even if some of the burst capability
you give them is contended/shared with other virtual servers. In one environment I saw,
when a virtual server running the backup software got busy at night, the performance of all
the other virtual servers dropped severely! It was difi cult explaining how such a simple
misconi guration completely unrelated to SQL Server was causing this. The solution is to
know where all your critically needed memory will come from during the busiest of
workload periods.
➤
One big LUN with one big partition
— SQL Server, even when running in a virtual server,
benei ts from having multiple uncontended drives for the different types of i les it uses.
A default deployment option for some virtualization software is to use a single large pool of
physical drives and create a single large partition on it; onto that are put all the i les for the
entire virtualization environment. This can quickly lead to storage hotspots, workload
contention, and having to adopt the same storage coni guration settings for every virtual
server, such as the storage cache policy. Ideally, a SAN should be coni gurable so that differ-
ent controller settings can be applied to different storage partitions, allowing the storage that
SQL Server will ultimately use to be optimized wherever possible. It also makes performance
monitoring difi cult because you can't always easily identify a specii c virtual server's workload.
As you saw earlier, the solution here is to distribute a busy database server's storage across
multiple virtual hard drives, providing they can use different groups of physical drives.
➤
Saturated host bus adapters
— It is common to see a host server with only a single HBA
running several I/O-intensive virtual servers. Not only is this a single point of failure, but
the HBA can easily get saturated, causing I/O requests to be queued and introducing storage
latency.
Search WWH ::
Custom Search