Why SQL Server has more I/O when my query has Where?

Question

I use SQL Server 2012 and I write this query :

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;

I write two line above for clean cash and set statistics on.

Those are my query :

SELECT C.custid, C.companyname, O.orderid, O.orderdate 
FROM Sales.Customers AS C 
     INNER JOIN Sales.Orders AS O 
        ON C.custid = O.custid 

SELECT C.custid, C.companyname, O.orderid, O.orderdate 
FROM Sales.Customers AS C 
    INNER JOIN Sales.Orders AS O 
        ON C.custid = O.custid 
WHERE O.custid < 5

But I got this statistic :

For first query : (830 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 1, read-ahead reads 25, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And for second query : (30 row(s) affected) Table 'Customers'. Scan count 0, logical reads 60, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Why logical reads in second query is more than first considering the number of row?

enter image description here

Thilo Thilo · Accepted Answer · 2014-12-03T01:01:50

Without the condition:

Table 'Customers'. Scan count 1, logical reads 2

With the condition:

Table 'Customers'. Scan count 0, logical reads 60,

The difference is between a Scan in the first case (which is fast, because the table is small), versus repeated Random-Access via Index (which is not so fast, because the table is too small for that to make sense).

But note that all these repeated logical reads caused by the the Nested Loop Join won't result in actual I/O (physical reads) as they are going to the same few blocks over and over again (and those are in your buffer cache working set).

Does this cause a performance problem for you? It seems that the query is fast enough as it is. Hopefully the optimizer gets it right for cases where it really matters (i.e. where big tables are involved).

Why SQL Server has more I/O when my query has Where?

2 Answers