Do you measure query IO with SET STATISTICS IO ON ?

Introduction

In our tuning work and often in presentations we see people use SET STATISTICS IO ON as a handy way to measure IO, especially logical reads. But, not many people know that it skips measuring IO from certain types of objects (functions) and gives you falsely low numbers.

Here is a demo

First, we will create a big table in a tempdb and a table-valued function that full-scans that table and returns only one int value:

Now, we will measure logical reads of a SELECT that full-scans big table, and compare it to logical reads of the same SELECT encapsulated in a table-valued function.

Miracle?

Did we just miraculously optimized our FULL SCAN so from 8045 reads it went down to 1 read, an improvement of 804 500% ? No, we didn’t. It did the same full scan in both cases of all 1 million rows. Here is the estimated execution plan of the SELECT from a function:

PlanFunction

So, why SET STATISTICS IO showed us only 1 logical read? The reason is, it does not measure IO that is within a scalar or table-valued functions at all! It measured 1 IO needed to read the resulting @ret table variable that function returned. It measures tables, INLINE table-valued functions (views with parameters), views, but NOT scalar or table-valued functions. How to measure all IO of a statement that contains functions? Use profiler or extended events. Here is what profiler will give you, all logical reads:

Profiler

Conclusion

If you have functions (except INLINE table-valued functions) in your SQL statement, you can’t measure IO with SET STATISTICS. Use profiler instead.

Posted in Uncategorized
4 comments on “Do you measure query IO with SET STATISTICS IO ON ?
  1. Juerg Maier says:

    In profiler I only see a Reads number. Can profiler be set to show logical/physical reads as separate numbers?

  2. Vedran Vedran says:

    Profiler shows only logical reads – the only important one IMHO. There is no option to show you physical reads in profiler. Query can read or write only memory (pages in buffer pool cache, to be specific), it cannot read the disk or write to disk directly. First time the query executes (let’s assume server just started) pages are not in buffer cache so there will be e.g. 1000 physical reads (to bring pages from disk to buffer pool) and 1000 logical reads (fetching those pages from buffer pool and doing some work with them). The second time query executes, all the requested pages are in buffer pool already, so there will be no physical reads, and you will see again 1000 logical reads. Logical reads is the only stabile number you can rely on. You should tune only logical reads to make them smaller and measure your success only with logical reads. If you make logical reads lower, physical reads will also be lower or even dissapear. Physical reads only show that you do not have enough RAM to cache entire DB, or you are fetching some pages for the first time. Physical reads are not so important really. You can buy more RAM and/or tune logical reads and physical will dissapear or be much smaller.
    Writes are also done only to memory (buffer pool – logical writes) if we put aside transaction log. Physical writes are done not from the user session scope, but by an asynchronous process called CHECKPOINT which fires about once every minute, depending on some settings and amount of db changes. Also, some events can initiate CHECKPOINT, even manual checkpoint commad.

  3. Dan says:

    Thanks, this helped me! I wish STATISTICS IO would show the result of the functions though.

  4. rick willemain says:

    Very well done and helpful. Thank you !

Leave a Reply

Your email address will not be published. Required fields are marked *

*