SQL Server Logical IO Block Sizes and Physical Reads I am trying to understand cache churn caused by SQL statements that are doing some table scans.

Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs).

Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred. 

Given that, my questions are:


What is the IO block size that a SQL Server logical IO operation refers to.
How do I get Physical IOs for a particular instance of SQL execution?
if something is doing 400K logical IOs, how much memory is streamed across (IO block size * logical IOs)


In SQL Server, 1 "logical I/O" refers to reading 1 8 KB page from the buffer pool (memory).  It's possible that a physical I/O will be incurred first (to get the page from disk to memory).  So 400K logical reads is 400,000 * 8 KB = 3,200,000 KB of data being read (~3 GB).

A reference for this, with a lot more detailed information, can be found on the Microsoft Docs site: Pages and Extents Architecture Guide

You mention block size, which I imagine is in reference to the OS level block size.  While this matters at the OS level, and can potentially impact SQL Server performance, it's not relevant to logical I/O in terms of the number of pages - which is fixed at 8KB.


  Also, I am trying to understand how to take the Report on top Logical IO SQL statements and get the physical IO that these incurred


You can find this in sys.dm_exec_query_stats.  This includes columns for:


max_logical_reads, 
total_logical_reads, 
last_logical_reads,
max_physical_reads, 
total_physical_reads, 
last_physical_reads


...etc.