Home > Workload Solutions > SQL Server > Guides > Design Guide—Ready Bundle for Microsoft SQL Server 2016 > Storage configuration
Unisphere storage management is a web-based solution that provides an interface for storage management and configuration tasks. The configuration depends on performance and capacity needs, preferred type of server connectivity, performance in terms of IOPS and MB/s, as well as future growth needs for both performance and capacity. This section provides guidance and best practices for storage management and configuration.
Dell EMC generally recommends that you use fewer storage pools within Unity storage to reduce complexity and increase flexibility. However, you might want to configure multiple storage pools to do the following:
Storage pools must maintain free capacity to operate properly. By default, Unity storage raises an alert if a storage pool has less than 30 percent free capacity. It also automatically begins to invalidate snapshots and replication sessions if the storage pool has less than 5 percent free capacity. Dell EMC recommends that storage pools always have at least 10 percent free capacity.
Hybrid pools contain HDDs (SAS or NL-SAS drives) and can contain more than one type of drive technology. Hybrid pools typically provide greater capacity at a lower cost than all-Flash pools. They also typically have lower overall performance and higher response times. Use hybrid pools for applications that do not require consistently low response times or that have large amounts of mostly inactive data.
Dell EMC recommends provisioning a Flash tier in hybrid pools. The minimum recommended Flash capacity is at least 5 percent of the pool capacity. The Flash tier helps enable pool performance efficiencies and improves response times when using snapshots or replication, or both.
You can improve hybrid pool performance by increasing the amount of capacity in the Flash tier, so that more of the active dataset resides on and is serviced by the Flash drives.
Hybrid pools can have up to three tiers—Extreme Performance, Performance, and Capacity. Dell EMC recommends using a single drive speed, size, and RAID width within the tier of a hybrid pool, as follows:
FAST VP accelerates performance of a specific storage pool by automatically moving data within that pool to the appropriate drive technology, based on data access patterns. FAST VP is only applicable to hybrid pools within a Unity hybrid system.
The default and recommended FAST VP policy for all storage objects is “Start High then Auto-tier.” This policy places initial allocations for the storage object in the highest tier available and monitors activity to this storage object to determine the correct placement of data as it ages.
FAST VP is most effective if data relocations occur during or immediately after normal daily processing. Dell EMC recommends scheduling FAST VP relocations to occur before backups or nightly batch processing. For applications that are continuously active, consider configuring FAST VP relocations to run constantly.
Dell EMC recommends maintaining at least 10 percent free capacity in storage pools, so that FAST VP relocations can occur efficiently. FAST VP relocations cannot occur if the storage pool has no free space.
FAST Cache is a single global resource that can improve performance of one or more hybrid pools within a Unity hybrid system. You can create FAST Cache with SAS Flash 2 drives only, and it is only applicable to hybrid pools. Dell EMC recommends that you place a Flash tier in the hybrid pool before you configure FAST Cache on the pool.
Enable FAST Cache on the hybrid pool if the workload in that pool is highly transactional and has a high degree of locality that changes rapidly.
For applications that use larger I/O sizes, have lower skew, or do not change locality as quickly, increasing the size of the Flash tier rather than enabling FAST Cache might be more beneficial.
FAST Cache can increase the IOPS achievable from the Unity system, and it will most likely result in higher CPU utilization to service the additional I/O. Before enabling FAST Cache on additional pools or expanding the size of an existing FAST Cache, monitor the average system CPU utilization to determine if the system can accommodate the additional load.
Dell EMC storage offerings use multiple drive types, with varying capacities and performance levels, and attempt to match data with the optimal drive type based on storage pools. Typically, mission-critical data is stored on high-performance drives, while less-critical data is moved to high-capacity drives.
While every environment is unique, an OLTP workload generally consists of small, random reads and writes. The required number of IOPS primarily determines the sizing of a storage system that services an OLTP workload. Different drive types have different performance capabilities. Faster-spinning drives can provide more IOPS with lower latency than slower-spinning drives. For best performance, Dell EMC recommends SSD drives for OLTP workloads.
The following factors affect drive selections:
Calculate the number of disks for capacity by using the following formula:
Number of disks = (Required Capacity / Capacity per disk)
Calculate the number of disks for performance by using the following formula:
Number of disks = (Read IOPS + [Write IOPS x RAID performance overhead]) / IOPS per disk
FC zoning enables the partitioning of the FC fabric into multiple subsets called zones. A zone includes FC initiators and targets, and enables communication between the initiators and targets.
Best practices for setting the I/O paths include:
Create a server cluster for high availability and map the volumes to the server cluster. Map the volumes by using Storage Center Console or Enterprise Manager Console.
For optimal performance, Dell EMC recommends the following:
For Hyper-V storage design, Dell EMC recommends the following:
Best practices for configuring the Windows OS include:
Best practices for configuring the Hyper-V host and deploying VMs include:
For SQL Server 2016, you can assign memory for a VM either dynamically or statically. To choose between dynamic and static memory, consider the following factors:
In general, choose dynamic memory when VMs are unmonitored and are relatively small, and when scalability is preferred over performance. For larger production VMs that are reasonably monitored, choose static memory for better and more consistent performance.
For dynamically assigned memory, see Running SQL Server with Hyper-V Dynamic Memory in the Microsoft Development Network Library.
Best practices for dynamic memory include:
Recommendations for static memory allocation include:
Note: Use this option with caution because Hyper-V does not start, restore, or accept a live migration for a VM if the hypervisor is unable to map each virtual NUMA node to a physical NUMA node.
For SQL Server parallelism settings, consider the following:
For more information about parallelism settings, see MAXDOP of Confusion.