0

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:

  1. What is the IO block size that a SQL Server logical IO operation refers to.
  2. How do I get Physical IOs for a particular instance of SQL execution?
Improve this question
4
  • I don't understand what you mean by "how much memory is streamed across (IO block size * logical IOs)". A logical IO is a read of an 8K page from cache and a block is an 8k pages on disk. A single physical IO may be a single page read or multiple 64K extents (contiguous pages) using scatter-gather IO. The physical IO may be done via prefetch or when a need page isn't already in cache.
    – Dan Guzman
    Commented Mar 19, 2019 at 11:49
  • Every logical IO is a block being retrieved (from either cache or disk). That memory being referenced by total block size (think the block is being copied). What it the block size?
    – Inquisitor Shm
    Commented Mar 19, 2019 at 11:58
  • @Dan Guzman - Are you suggesting that one physical read (in your comment 'reading mutliple 64K extends using scatter-gather') equates to one logical read? And, that this shows up in SQL Server reporting as one logical read?
    – Inquisitor Shm
    Commented Mar 19, 2019 at 21:41
  • No, a logical read a read of a single 8K page from cache. Physical reads, which bring pages into cache before they can be used, are an independent metric.
    – Dan Guzman
    Commented Mar 19, 2019 at 22:22

1 Answer 1

Reset to default
5

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.