I am a supporter of always going to the basics when it comes to performance troubleshooting. Whenever I have done any perf tuning exercise, I start by enabling the Statistics IO as part of my debugging. When STATISTICS I/O is enabled, SQL Server maintains I/O statistics for the queries on a per-table basis (as opposed to a cumulative fashion for all tables). This output is sent back as a message after the query completes.
For each table referenced in the query, there will be one row that is similar to this:
Table ‘authors’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Scan count: shows the number of times that the OLE DB row set corresponding with the table or index was opened for reading. This value is dependent on the type of plan chosen and where this table is accessed in relation to the other tables listed in the query. Note that just because this says scan that it does not necessarily mean that it was a table scan or index scan—it may have been an index seek.
Logical reads: counts the number of times that a request was made to access a page belonging to that particular table. This is incremented in the underlying calls to one of the page suppliers (linked pages, unordered page, etc.). This counter gets incremented regardless of whether the page was already in cache or a disk I/O was required.
Physical reads: is incremented in the same place that increments the logical reads, but only if the underlying request read the page from the disk. Note that each one of these requests is essentially synchronous – the page was requested and the caller had to wait for the I/O to complete before continuing execution.
Read ahead reads: is incremented when the page(s) were not already found in cache and had to be read from the disk. Here, the caller generally is able to do the other work after calling this and will use the page (latch it) at a later time. It is possible for the caller to request the page(s) to be read, but never actually use it. This might happen in the case of queries with TOP or SET ROWCOUNT, semi joins or anti semi joins where we can break out of the loop on the first match/non-match etc. Pages that are included in the read ahead counter are not included in the physical read counter.
Now that we have this understanding in place, I hope you will use this information at some point in time while doing performance tuning in your environments. Do let me know if you ever used this information as your tuning techniques and how you did troubleshooting with such information.
Reference: Pinal Dave (https://blog.sqlauthority.com)