Introduction
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.
Storage pools: General recommendations
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:
- Separate workloads with different I/O profiles.
- Separate pools where Dell EMC FAST™ Cache is and is not active.
- Dedicate resources to meet specific performance goals.
- Separate resources for multitenancy.
- Create smaller failure domains.
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:
- Extreme Performance tier—Use 800 GB SAS Flash 2 drives, and configure them all with RAID 5 (8+1).
- Performance tier—Use 1.2 TB SAS 10 k rpm drives, and configure them with RAID 5 (4+1).
- Capacity tier—Use 6 TB NL-SAS drives, and configure them all with RAID 6 (6+2).
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:
- Type of the workload
- Expected IOPS, latency, and throughput
- Database size
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:
- Single-initiator, multiple-target zoning—Creates each FC zone with a single initiator (HBA port) and multiple targets (front-end ports). Therefore, each HBA port requires a separate FC zone with front-end ports. Create independent zones for each HBA installed in the host.
- World Wide Name (WWN) zoning—Contains only the host HBA port and the front‑end primary ports. In most cases, including the front-end reserve ports is unnecessary because they are not used for volume mapping. Dell EMC recommends creating zones by using a single initiator host port and multiple Storage Center ports.
- Multipathing—Designates how many of the Dell EMC storage front-end ports that the system allows the volume to be mapped through. Dell EMC recommends the use of the Dell EMC PowerPath™ Multipathing utility for designating the ports.
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:
- Place tempdb, database, and log files on separate LUNs.
- Allow for data growth and avoid exceeding 80 percent capacity of the LUNs for database files.
- Configure end-to-end multipathing by using PowerPath software for availability/redundancy and throughput optimization.
- Use multiples of 64K size as allocation unit for SQL Server volumes.
- Ensure that I/O is distributed across both controllers. For best results, use the same number of volumes on each controller.
For Hyper-V storage design, Dell EMC recommends the following:
- Provision a Cluster Shared Volumes (CSV) store and a cluster quorum disk on Dell EMC storage. Map the quorum disk and CSV volumes to the cluster nodes to enable faster failover of the SQL Server VM without the need for dismounting the drive, changing ownership, or remounting the volume.
- Create a virtual SAN by grouping WWNs of virtual FC adapters hosted in PowerEdge servers and Dell EMC storage. Configure the VMs that are hosting SQL Server with direct access to the storage LUNs.
Best practices for configuring the Windows OS include:
- Use an allocation unit size of 64 KB to format the volume that stores the database files.
- Enable the Windows Lock Pages in Memory (LPIM) policy by adding an account with privileges to run sqlservr.exe. The LPIM policy determines which accounts can use a process to keep data in physical memory. The policy prevents the Windows OS from paging out a significant amount of data from physical memory to virtual memory on disk.
Best practices for configuring the Hyper-V host and deploying VMs include:
- Do not overcommit resources. Hyper-V allows overcommitting resources such as CPU and network. However, overcommitting leads to performance degradation when the resource usage exceeds the available resources.
- Provision static or dynamic memory. For information, see SQL Server 2016 Memory settings.
- Provision compute resources. A vCPU is a representation of the physical core of a processor or threads/logical processors in the core. In Hyper-V, you can adjust the relative weights and reserves for a VM by going to Settings > Processor. If CPU resources are overcommitted, setting the weights and reserves optimizes the way these resources are used. You can prioritize or deprioritize specific VMs based on your needs.
- Use fixed-size virtual hard disks for the OS. Dell EMC recommends using fixed virtual hard disks (VHD/VHDX) for production workloads. Using dynamic virtual hard disks can result in occasional pauses during disk resizing. Use dynamic disks for noncritical test environments or nonproduction environments.
- Use separate storage LUNs for data and log files. To rule out disk contention, Dell EMC recommends using separate LUNs. For best performance, create distinct LUNs for SQL Server data files and SQL Server log files.
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:
- How frequently you monitor your database
- The VM size (size of the processors, memory, and other resources) as compared to the size of a single non-uniform memory access (NUMA) node on the host’s physical architecture
- Preference between performance and levels of scalability
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:
- Determine startup RAM and minimum memory values based on your needs. Microsoft recommends leaving the max server memory value at its default setting, which allows SQL Server to manage memory dynamically. However, Dell EMC recommends that you change this value, as needed, if one or both of the following apply:
- You are running multiple applications on the VM.
- You can reasonably ascertain the maximum amount of memory that you want to assign to SQL Server.
- Set the min server memory value (default value is zero) based on usage and performance considerations because dynamic memory is enabled for the VM.
- When using dynamic memory, set the reserves for the VM by using the Memory Buffer option. Base the amount of memory reserved on the min server memory value that is set for SQL Server, and the memory that is required for any other applications and the OS.
Recommendations for static memory allocation include:
- To maximize performance, assign a particular amount of memory to a VM based on its virtual and physical NUMA architecture.
- To ensure optimal performance, disable the NUMA Spanning option for the Hyper‑V host, so that a single physical NUMA node supports a single virtual NUMA node.
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.
- Set max server memory and min server memory values based on the amount of memory you want to reserve for the operating system, typical requirements of your SQL Server, and other performance considerations. For more information about memory settings, see Blitz Result: Memory Dangerously Low or Max Memory Too High.
Parallelism settings
For SQL Server parallelism settings, consider the following:
- The SQL Server configuration option max degree of parallelism controls the number of processors that are used for the parallel execution of a query. If the SQL Server VM in question migrates from hosts with different core configurations, leave this setting at 0. However, if that is not the case, use another value.
- The SQL Server configuration option cost threshold for parallelism specifies a threshold at which query plans run in parallel. Dell EMC recommends that you change the value of this setting from its default value of 5 to 50. You can adjust this number further.
For more information about parallelism settings, see MAXDOP of Confusion.
