Monica Rathbun givesĀ a few methods for reducing how many I/O operations a query requires:
Implicit Conversions
Implicit conversions often happen when a query is comparing two or more columns with different data types. In the below example, the system is having to perform extra I/O in order to compare a varchar(max) column to an nvarchar(4000) column, which leads to an implicit conversion, and ultimately a scan instead of a seek. By fixing the tables to have matching data types, or simply converting this value before evaluation, you can greatly reduce I/O and improve cardinality (the estimated rows the optimizer should expect).
There’s some good advice here if your main hardware constraint is being I/O bound.