White Papers

Deploying SQL Server on ME4 Series storage
11 Dell EMC PowerVault ME4 Series and Microsoft SQL Server | 3923-BP-SQL
4.1.4.2 MPIO
ME4 Series arrays support Asymmetric Logical Unit Access (ALUA), and when MPIO is configured, the
default MPIO policy is round robin with subset. This is the recommended setting for all database volumes.
This setting works best for most environments because it is easy to manage and performs very well. Use
other MPIO policies with caution and remember to review custom MPIO policies when adding or removing
volumes from the host.
4.2 SQL Server I/O reduction
4.2.1 Memory
Unnecessary I/O can be avoided and performance can be increased by allocating the proper amount of
memory to SQL Server. SQL Server performs all I/O through the buffer pool (cache) and therefore uses a
large portion of its memory allocation for the buffer pool. Ideally, when SQL Server performs I/O, the data is
already in the buffer pool and it does not need to go to disk. This type of I/O is referred to as logical I/O and is
the most desirable because it results in the best performance. If the SQL Server data does not need to reside
in the buffer pool, it will need to access disks, resulting in physical I/O.
Proper memory allocation is critical to SQL Server performance and can improve storage performance as
well. In many cases, SQL Server and storage performance can be further improved by adding memory.
Adding memory generally improves performance, but there is a point of diminishing returns that is unique to
each environment.
4.2.2 Buffer pool extension
With SQL Server 2014, the buffer pool can be extended to a file on the file system to provide additional space
to cache data or index pages. Using this feature can provide significant performance benefits without adding
memory to the database server in some cases. By caching more pages on the server, the I/O load on the
array is reduced.
When placing the buffer pool extension on the array, create a separate volume for the buffer pool extension
and do not take snapshots of the buffer pool extension volume. The buffer pool data is repopulated by SQL
Server when the instance is restarted, therefore data recovery does not apply.
4.2.3 Database compression
The overall I/O workload can be reduced by enabling database compression in SQL Server. While there is a
tradeoff in terms of CPU utilization on the database server, compression is still a viable option to consider and
test in any environment. Database compression reduces I/O by reducing the amount of data that needs to be
stored. The SQL Server data pages are compressed in memory before being written to disk, resulting in fewer
pages needed to store the same number of rows and therefore less I/O.
4.2.4 Instant file initialization
By default, SQL Server writes zeros to the data file during the allocation process. The process of zeroing out
the data files consumes I/O and acquires locks as the SQL Server data pages are written. This activity can
occur for minutes or even hours depending on the file size. While this may seem minor, writing zeros to these
files can occur at critical periods when time and performance are critical such as database auto growth,
expanding a full data file, replication, or restoring a database as part of a disaster-recovery event.