When I SET STATISTICS IO ON

SET statements in TSQL allows user to get more information or change behavior of the query execution in SSMS.

One of the most common SET statement we use is SET STATISTICS ON/OFF.

This shows statistical information about the query information. This is what we get when we turn on STATISTICS IO ON.

Statistic IO Result:

---------------------------

Table '<<Table name>>'. -> Name of the table we read

Scan count 1, -> Number of scans or seeks

logical reads 160,  -> Reads from RAM

physical reads 0, -> Reads from Disk

read-ahead reads 0, -> Number of pages placed into the cache for the query

lob logical reads 0, -> Large objects read from RAM

lob physical reads 0, -> Large objects read from Disk

lob read-ahead reads 0. -> Number of pages for LOB placed into the cache for the query

LOB stands for Large objects, such as ntext, nvarchar, varchar, etc.

LOB data is stored not on the row, but away from the row.

• If size is < 8000 bytes, stored on row overflow pages
• If size is > 8000 bytes, stored on LOB data pages