Home > Storage > PowerStore > Databases and Data Analytics > Dell PowerStore: Microsoft SQL Server Best Practices > Managing performance
PowerStore will perform dynamic load balancing of volumes and vVols to intelligently balance IO utilization across both nodes in a PowerStore appliance. PowerStore monitors performance on each node and based on utilization will rebalance volumes and vVols non-disruptively to maximize performance. Intelligent load balancing runs automatically to rebalance workloads to maximize performance and requires no user intervention. This is beneficial for workloads like SQL Server where the workload may change over time or when new workloads are onboarded to a PowerStore appliance.
PowerStore utilizes an internal set of performance rules to determine when and if volumes and vVols will be relocated. Once a volume is relocated, system performance is reassessed, and the cycle will repeat as necessary.
PowerStore performs best when the system performance is balanced across both nodes in the appliance. The “ownership” of a volume by a node is known as Node Affinity. The Node Affinity is set when the volumes are mapped to a host. By default, volume Node Affinity alternates between the two nodes in the appliance when mapping volumes to a host to balance the workload. Usually, this process sufficiently balances the load across both nodes in the appliances. Intelligent load balancing will perform dynamic rebalancing automatically over time once certain performance thresholds are met. Because every SQL Server workload is unique, and storage access patterns depend on database layout across one or more files, Node Affinity adjustments might be necessary to maximize overall system performance.
Volume Node Affinity is shown in the Volumes or Virtual Volumes list under Storage. By default, this column is not displayed. To add it to the view, click the Columns selection icon next to the filter icon. Scroll down the list and select Node Affinity.
Figure 11. Selecting additional columns in the Volumes list
Figure 12. Volumes list with Node Affinity added
System select node A and System select Node B are the system-assigned values. Node Affinity can be set using the PowerStore CLI (CLI) or PowerStore REST API. This example uses the CLI.
The following command displays the details of a volume named SQLData2-007:
pstcli -destination <PowerStore Manager URL/IP> -u <id> -p <password> /volume -name "SQLData2-007" show
The following command sets the Node Affinity for volume SQLData2-007 to Preferred_Node_A. Valid values are Preferred_Node_A, Preferred_Node_B, and System_Select_At_Attach.
pstcli - destination <PowerStore Manager URL/IP> -u <id> -p <password> /volume -name "SQLData2-007" set -node_affinity “Preferred_Node_A”
Note: When Node Affinity is set to Preferred_Node_A or Preferred_Node_B, it is no longer managed by PowerStore unless it is set to System_Select_At_Attach. Setting Node Affinity to System_Select_At_Attach returns it to a system managed state.
Multi-tenant scenarios where multiple SQL Server databases, instances, or other applications share a PowerStore appliance are typical. There are several PowerStore features that can assist in managing multi-tenant scenarios.
Selecting a performance policy sets a relative priority (high, medium, or low) for the volume, volume group, or vVol using a share-based system such that when resources are constrained, the resource with the higher performance policy receives priority. If there is no PowerStore resource constraint, the volumes perform equally.
Leaving all volumes at the default of medium provides equal performance to all volumes. The volumes all perform at the highest level until a PowerStore volume is set to a different performance policy.
Setting performance limits on volumes or volume groups can control one demanding application, sometimes referred to as “noisy neighbor” from monopolizing storage resources and degrading performance for other workloads. PowerStore allows QoS policies to be created and applied to volumes or volume groups. These policies allow absolute limits to be set for IOPS and bandwidth, or density-based limits per provisioned GB of storage. QoS rules also allow a burst percentage to be specified for situations where exceeding the limit for a short time is acceptable.