White Papers

Optimizing SQL Server I/O
20 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
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.
7.5 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. The thin write feature saves space by not
physically storing all the zero data. It will simply mark a bit in the metadata indicating these pages are filled
with zeros. Although thin writing mitigates the space concern, there is still processing that occurs on the
controller to receive and inspect the data. 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.
When Instant File Initialization is enabled, SQL Server will skip the process of zeroing out its data files when
allocating space. Dell EMC recommends enabling Instant File Initialization.
7.6 Resource Governor
The Resource Governor was added in SQL Server 2008 to allow database administrators to limit the CPU
and memory resources a query is able to consume. This feature was enhanced in SQL Server 2014 to allow
I/O resources to be limited as well. For example, the Resource Governor can be used to reduce the impact of
a user running an I/O intensive report by limiting the maximum number of IOPS that user can perform. While
a query throttled by the Resource Governor will take more time to complete, overall database performance
will be better.
7.7 Database design considerations
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 including the business owner,
architect, developer, database administrator, and system administrator. Decisions at the design level have a
multiplied downstream impact as 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. There are more copies of databases that people retain in various
forms than they realize.
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. Database
tuning is a very large topic and is beyond the scope of this document. However, there are numerous
resources available in both online and print form. At a high level, the following areas should be considered
when tuning a database to reduce I/O.