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

Yassir 201 Reputation points
2023-02-26T01:08:30.67+00:00

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:

User's image

User's image

Have you any idea how i can improve that ?

As Bellow the Execution Plan :

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

Thanks for help !

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,807 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2023-02-26T13:13:19.1966667+00:00

    "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 :-)

    0 comments
  2. Erland Sommarskog 101.7K Reputation points MVP
    2023-02-26T14:18:52.72+00:00

    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.

    0 comments