Home > Storage > Unity XT > Virtualization, Cloud & Applications > Dell Unity XT: Microsoft SQL Server Best Practices > Reducing SQL Server I/O
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 disk resulting in physical I/O.
Proper memory allocation is critical to SQL Server performance and can improve storage performance as well. Often, SQL Server and storage performance can be further improved by adding additional memory. Allocating more memory is better, but there is a point of diminishing returns that is unique to each environment. Refer to the Microsoft documentation about server memory configuration options for more information,
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 sometimes. 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.
SQL Server 2016 introduced support for Persistent memory (PMEM) and the capabilities are being expanded with SQL Server 2019 to cover more scenarios and the Linux® operating system. Often, 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 supports PMEM starting with Dell PowerEdge™ 14th-generation (14G) servers. For more information, see Watch your transactional database performance climb with Intel Optane DC persistent memory and the Dell NVDIMM-N Persistent Memory User Guide.
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 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.
By default, SQL Server writes zeros to the datafile during the allocation process. The process of zeroing out the datafiles 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. Time duration and performance are critical during database auto growth, datafile expansion, replication, or restoring a database as part of a disaster recovery event.
When Instant File Initialization is enabled, SQL Server will skip the process of zeroing out its datafiles when allocating space. Dell Technologies recommends enabling Instant File Initialization to maximize datafile growth performance.
Resource Governor can be used to reduce the impact of a user running an I/O intensive report by limiting the maximum number of CPU or IOPS that user can perform. Resource Governor can also be used to protect against poorly developed queries. While a query throttled by the Resource Governor may take more time to complete, overall database performance will be better.
Reducing SQL Server I/O requires a holistic approach. Many of the items in this section will require involvement from the whole team responsible for the SQL Server applications. This includes the business owner, architect, developer, database administrator, and system administrator. Decisions at the design level have a multiplied downstream impact. Data is written and read multiple times and duplicated in various types of database copies including databases copied for other uses such as testing and reporting, replicated databases, replicated storage, and backups.
One of the most challenging aspects of SQL Server is that the I/O pattern and the amount of I/O that is generated can vary greatly depending on the application, even if those applications have databases of the same size. This is because the design of both the database and the data access code control SQL Server I/O.
Database tuning can be one of the most cost-effective ways to reduce I/O and improve scalability. At a high level, consider the following areas when tuning a database to reduce I/O.
The foundation of the entire database and the schema for how data will be stored and ultimately accessed is determined by the database design. The database design should support both usability and efficient data access. This includes efficient table design and datatypes as well as indexes, partitioning, and other features that can improve efficiency. It is common for database design to only be focused on usability while performance and scale are overlooked.
How a query is written can greatly affect the amount of I/O SQL Server needs to perform when running the query. Queries should return only the required amount of data in the most efficient manner possible. Tune the queries responsible for consuming a relatively large number of resources for best performance and scale.
Consider how applications are using the data and how it is requested. Sometimes code and component reuse can result in the same data being unnecessarily retrieved repeatedly. All data access should be purposeful.
SQL Server uses a cost-based optimizer to generate query plans for data access. These plans are based on the statistics regarding how data is distributed in the tables. If the statistics are inaccurate, bad query plans may result and unnecessary I/O will be performed. Proper database maintenance includes ensuring that statistics are up to date.
Frequent data modifications can also lead to fragmentation within SQL Server datafiles, producing unnecessary I/O. Fragmentation can be addressed through index reorganization or rebuilds as part of regular database maintenance.
The database maintenance process itself can also have a large I/O impact. Typically, every table and index does not need to be rebuilt or reorganized every time maintenance is run. In addition, table partitioning strategies can also be leveraged to make the maintenance process more selective. Consider implementing intelligent maintenance scripts that use usage statistics to perform maintenance on an as-needed basis.
For mission-critical databases, maintenance activities need to be considered as part of the overall design. If maintenance is not considered as part of the overall process, issues can arise, such as unmanageable sizes and feature incompatibilities that limit available options and strategies.