SQl Server - "Warnings": Operation caused residual I/O

Question

Hello ,

I've seen this warning in SQL Server 2017 execution plans:

Operation caused residual I/O . The actual number of rows read was 102 626 685 but the number of rows returned was 64 827 005

Here is a snippet from SQLSentry PlanExplorer:

Have you any idea how i can improve that ?

As Bellow the Execution Plan :

https://www.brentozar.com/pastetheplan/?id=rJJIxEOCi

Thanks for help !

Answer 1

I assume that this the same query in your previous post. Given the nature of the query, you can expect a pretty wild plan like this. But I note that it is serial and not parallel, so there is reason to check for parallelism blockers.

I'm boarding a plane in a few minutes, so I don't have time for a full analysis, but I notice one:

,FORMAT(t1.Date, 'yyyy-MM') AS 'Mois'

FORMAT uses CLR under the covers and results in context switches which are expensive at this scale. This may also be your parallelism blocker. Change this to:

convert(char(7), t1.Date, 121) AS "Mois"

As for the residual I/O, i belive that only tells us that a predicate was pushed down to the storage engine.

Answer 2

"Residual I/O Warnings" is a common issue which alert us that the query is reading more data than it should.

Have you any idea how i can improve that ?

In order to examine the issue we must some sample data and database structure to repriduce the scenario in order to play with (the query is needed mu can be found in the XML as well).

(1) Did you noticed this alert:

Missing Index (Impact 79.2813): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON Schema2.Object7 (Column5,Column4,Column9) INCLUDE (Column3,Column8,Column6,Column10)

Did you try to create such index?

(2) In the meantime, the following free book online provide an awesome explanation following step by step full scenario

https://resources.enterprisetalk.com/ebook/SW-SentryOne-EN-1.pdf

You can jump to page 17 where it looks closer on the error “Operation caused residual IO." but better to have this opportunity and read from the start (page 1).

Please inform us if there anything that still open after you read it :-)