How to identify queries that cause high Logical I/O? I need some help to identify SQL queries that could suddenly increase the SQL Server Logical I/O.
Can someone please help to provide some advice? This was from SQL Server 2017
Not enough rep to comment, so posting here.  Below query should get you started:
SELECT 
      qs.total_logical_reads
    , qs.total_logical_writes
    , qs.total_physical_reads
    , qs.last_logical_reads
    , qs.last_logical_writes
    , ISNULL(OBJECT_NAME(st.objectid), 'Ad-hoc Query') [Proc Name]
    , st.text
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
ORDER BY (qs.total_logical_reads + qs.total_logical_writes) DESC

Note - the totals are cumulative over time, so careful of the &quot;gotchas&quot; of queries which have been using little IO each execution but have been executed frequently over longer period of time versus queries which just started using more IO and/or executing more frequently. Check out more info about sys.dm_exec_query_stats and other potentially useful queries on the MS doc.