White Papers

Optimizing SQL Server I/O
21 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
7.7.1 Database design
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 data types as well as indexes, partitioning, and other features
that can improve efficiency. It is common for database design to only be focused on usability.
7.7.2 Query design
How a query is written can greatly affect the amount of I/O SQL Server needs to perform when executing the
query. Queries should return only the required amount of data in the most efficient manner possible. There
are many great tools and resources available to help design efficient queries.
7.7.3 Application design
Consider how applications are using the data and the manner in which it is requested. Sometimes code and
component reuse can result in the same data being unnecessarily retrieved over and over again. All data
access should be purposeful.
7.7.4 Maintenance
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 data files, 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
maintenance scripts to perform maintenance on an as-needed basis.
For mission critical databases these 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.