Question on SET STATISTICS IO ON

Howdy all. I was wondering about the output I'm getting from using this
command both before and after adding an index to speed up a sproc. Here is
the output (for the table in question) before an index is added:
Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
reads 0, read-ahead reads 0.
and here is the output after after the index is added:
Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
reads 0, read-ahead reads 0.
It also now has a "worktable" result, which did not exist before the index
was added:
Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
read-ahead reads 0.
As you see, the Scans after the index was added decreased significantly by
adding the index, but it also increased the Logical Reads. I've been looking
at this article (
http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
) and it says that what matters most is the Logical Reads. It goes so far as
to say that an increase in Logical Reads wil actually hurt performance.
However, it doesnt say anything about Scans being lowered this much but
Logical Reads going up at the same time.
So my questions (finally):
1. Why would Scans be affected by adding an index? After all, a Scan refers
to the number of times that tables referenced in the query have been accessed.
2. As a general rule, are more Logical Reads OK if the number of Scans drops
tremendously?
TIA, ChrisR> 1. Why would Scans be affected by adding an index? After all, a Scan
> refers
> to the number of times that tables referenced in the query have been
> accessed.
It appears that before you added the index it was doing a nested loop join
or an index seek for each matching row. After it appears to scan the entire
table or index twice. It's hard to say without seeing the query plan.
> 2. As a general rule, are more Logical Reads OK if the number of Scans
> drops tremendously?
That typically means you are scanning and that may be OK or it may not. The
goal is really to reduce the number of reads as much as possible.
Can you post the DDL for the table & Indexes along with the query you are
using?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BAB3E39F-DB82-4375-AAE4-9428D550002A@.microsoft.com...
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646,
> physical
> reads 0, read-ahead reads 0.
>
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been
> looking
> at this article (
> http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
> ) and it says that what matters most is the Logical Reads. It goes so far
> as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan
> refers
> to the number of times that tables referenced in the query have been
> accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans
> drops
> tremendously?
> TIA, ChrisR
>
>|||ChrisR,
In general, a lower logical read count is better, because potentially
each logical read is a physical read.
But that theoretical model for the worst case situation almost never
applies in reality. You will have a buffer cache that is very likely to
cache at least some of the pages of the table you are querying. If there
is a clustered index scan or table scan, then each logical read
represent reading a page. For a large table, the buffer cache hit ratio
might be low. If there are many index seeks for a loop join, then the
logical reads of the index seek refers to finding a row on a page, and
these logical reads are likely to have a much higher buffer cache hit
ratio, especially for the index root and branches. Also, processing a
cached logical "page" read will require more CPU then processing a
cached "row" read.
Unfortunately, SQL Server will only report the total number of logical
reads, and will not tell if they are "page" logical reads and/or "row"
logical reads.
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
The scan count suggests that there were 70341 index seeks, requiring
(almost) 238646 logical row reads.
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
If the query has only one reference to the HomeStudyResponses table,
then the scan count suggest that a parallel query plan was executed and
that 2 processors scanned (or partially scanned) the table/index,
requiring (almost) 352493 logical page reads.
Now for your questions:
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
Correct. But if you add an index, it gives the optimizer more options
how to execute the query. In your case, it looks like the optimizer
switched from using a Loop Join to a Merge Join or Hash Join. It looks
like a bad switch, because the logical read count has gone up, and now
their are probably "page" reads instead of earlier "row" reads.
Of course, the real test is to measure the performance. You should test
this. In the end it is only (average) execution duration that matters.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
No, definitely not. If all you have is logical reads, then lower is
better. For the same number of logical reads, logical "row" reads are
better than logical "page" reads (because of the higher likelyhood of a
cache hit, and the lower CPU overhead). The number of scans doesn't
directly affect performance, at least not significantly.
--
Gert-Jan|||On Aug 29, 1:00 pm, ChrisR <Chr...@.discussions.microsoft.com> wrote:
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been looking
> at this article (http://www.sql-server-performance.com/articles/per/statistics_io_time...
> ) and it says that what matters most is the Logical Reads. It goes so far as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
> TIA, ChrisR
Statistics IO is just part of the story. I always use it together with
STATISTICS TIME on. CPU is a precious resource and saving CPU cycles
is very important, in some cases more important than saving logical
reads. Sequential reads can be very fast, and more reads may be not
too bad. Also index pages may be more likely to be found in the cache
- that is something the optimizer can never know. i would solve
problems like this on case-by-case basis without making general
statements if possible.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/|||Thanks to all!!!
"ChrisR" wrote:
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
>
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been looking
> at this article (
> http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
> ) and it says that what matters most is the Logical Reads. It goes so far as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
> TIA, ChrisR
>
>