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
>
>

Question on Identity columns in 2005.

Howdy all. I used to frequent this forum and hope all has been well with
Paul, Hillary, and any other regulars I may have missed. Anyways, I just set
up Transaction, Immediate Updating replication on two 2005 boxes,
replicating the adventureWorks DB. One table in particular
(Person.AddressType) contains an Identity coulmn, and I did nothing to alter
it in any way. Anyways, once replication was fully configured, I inserted a
row into the table on the Subscriber, and it was assigned Identity value
20009. Questions:
1. It's been a while since I've done anything with Immediate Updating stuff,
but it seems to me that in 2000 I would have had to manually configure the
Identity value on the Subscriber box to use a different range of Identity
values than on the Publisher, right?
2. I looked on the Subscriber table Identity column, expecting the Seed to
be 20009, but it's not, it's 1! How can it be getting the value of 20009 if
the Seed is still set to 1?
TIA, ChrisR
Hi Chris - good to see you're back here!
The automatic identity range management is one of the defaults that has
changed across versions.
The seed and increment is the same behaviour though across versions. This
really refers to the table creation on the publisher. Run DBCC
CHECKIDENT(tablename) at the subscriber to get the new value (or look at the
check constraints on the subscriber's table). I'm guessing that it made
sense that it remains as the original because the new one would at some time
become out of date anyway when a new range is requested, so the easiest
solution was to just take the old table script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. But why is it "ignoring" the value of 1 that I see in
Management Studio?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SpgBYBMHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi Chris - good to see you're back here!
> The automatic identity range management is one of the defaults that has
> changed across versions.
> The seed and increment is the same behaviour though across versions. This
> really refers to the table creation on the publisher. Run DBCC
> CHECKIDENT(tablename) at the subscriber to get the new value (or look at
> the check constraints on the subscriber's table). I'm guessing that it
> made sense that it remains as the original because the new one would at
> some time become out of date anyway when a new range is requested, so the
> easiest solution was to just take the old table script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||The idenity value is being reset on initialization using DBCC CHECKIDENT, so
the defined value is now 'meaningless'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWuRb7BMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> The idenity value is being reset on initialization using DBCC CHECKIDENT,
> so the defined value is now 'meaningless'.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>