White Papers

Optimizing SQL Server I/O
19 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
7 Optimizing SQL Server I/O
7.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 data SQL Server needs does not reside
in the buffer pool, it will need to access disk 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 additional
memory. Generally speaking, the more memory the better but there is a point of diminishing returns that is
unique to each environment.
7.2 Buffer pool extension
Starting 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. Only pages that have not been modified can be stored in the
buffer pool extension. While the extension file can reside on any type of storage, it is best to store it on local
solid-state devices. Using this feature can provide significant performance benefits without adding memory to
the database server. By caching more pages on the server, the I/O load on the array is reduced.
While the SSD tier on the SC Series array can be used for the buffer pool extension, the SSD tier is typically
more effective when used as primary storage. In cases where there is not enough SSD capacity to be
effective for primary storage of the database volumes, the SSD tier can be considered for the buffer pool
extension. When placing the buffer pool extension on the array, use the following guidelines:
• Create a separate volume for the buffer pool extension.
• Assign a storage profile that is configured to store all data on the SSD tier (tier 1).
• Do not take snapshots of the buffer pool extension volume.
7.3 Persistent memory
SQL Server 2016 introduced support for persistent memory (PMEM) and the capabilities are being expanded
with SQL Server 2019 to cover more scenarios as well as the Linux
®
operating system. In many cases,
PMEM can be used to accelerate challenging I/O workloads and make I/O patterns more efficient with SQL
Server PMEM features such as tail-of-log-cache and Hybrid Buffer Pool. Virtualized environments running
Hyper-V
®
or VMware
®
can also take advantage of PMEM, making it a wise investment. Dell EMC supports
PMEM starting with the Dell EMC PowerEdge™ 14th generation (14G) servers. For more information, see the
Microsoft article Storage Class Memory in SQL Server 2016 SP1 and the Dell EMC document Dell EMC
NVDIMM-N Persistent Memory User Guide.
7.4 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, it 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