Profiler not reporting reads accurately

[patrickshroads]

I am running a profiler trace against a database and noticed that the
reads column always shows 0. When running the same trace against
another machine I get back values in the reads column. I took a query
that profiler reported as having 0 reads and ran in in query analyzer
wtih STATISTICS IO on and confirmed that there are in fact reads:

Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads
0, read-ahead reads 1.
Table 'tt_clineitem'. Scan count 10, logical reads 125208, physical
reads 1540, read-ahead reads 2995.
Table 'tt_contractitem'. Scan count 32, logical reads 676, physical
reads 0, read-ahead reads 0.
Table 'tt_contract2'. Scan count 3, logical reads 121, physical reads
4, read-ahead reads 0.

I am on SQL 2000 sp3a. Any help appreciated.

Thanks!

[David Gugick]

pa************@hotmail.com wrote:

I am running a profiler trace against a database and noticed that the
reads column always shows 0. When running the same trace against
another machine I get back values in the reads column. I took a query
that profiler reported as having 0 reads and ran in in query analyzer
wtih STATISTICS IO on and confirmed that there are in fact reads:

Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads
0, read-ahead reads 1.
Table 'tt_clineitem'. Scan count 10, logical reads 125208, physical
reads 1540, read-ahead reads 2995.
Table 'tt_contractitem'. Scan count 32, logical reads 676, physical
reads 0, read-ahead reads 0.
Table 'tt_contract2'. Scan count 3, logical reads 121, physical reads
4, read-ahead reads 0.

I am on SQL 2000 sp3a. Any help appreciated.

Thanks!

You can't compare the STATISTICS IO and Profiler Read counts as they
come from different places in SQL Server. That aside, what event are you
looking at to examine the reads in Profiler? And do you have the client
tools patched to the same service pack level on both PCs? You can check
this from the HELP | ABOUT screen in Profiler. Should read version 760
if on SP3.

--
David Gugick
Imceda Software
www.imceda.com

[patrickshroads]

I am tracing SQL:BatchCompleted and SQL:StmtCompleted. I know that I
should expect some discrepancy between STATISTICTS IO and Profiler but
it's a huge difference between 0 and what STATISTICS IO is reporting.
Plus, *every* line in Profiler is reporting 0 reads (writes and CPU
seem to be OK though).

I checked the version of Profiler and it is correct, although it's not
a client problem because this happens when I do server side traces
using the tracing stored procs.

Thanks

[David Gugick]

pa************@hotmail.com wrote:

I am tracing SQL:BatchCompleted and SQL:StmtCompleted. I know that I
should expect some discrepancy between STATISTICTS IO and Profiler but
it's a huge difference between 0 and what STATISTICS IO is reporting.
Plus, *every* line in Profiler is reporting 0 reads (writes and CPU
seem to be OK though).

I checked the version of Profiler and it is correct, although it's not
a client problem because this happens when I do server side traces
using the tracing stored procs.

Thanks

What machine are you running this against. I haven't seen a problem with
Reads reporting incorrectly. There was a CPU reporting problem with the
RPC:Completed event that was recently patched and should be fixed by
SP4.

What server version and SP level are you running this against because it
now sounds like from what you've described that this is a server-issue
and not a client one.
--
David Gugick
Imceda Software
www.imceda.com

[Greg Linwood]

>I know that I should expect some discrepancy between STATISTICTS IO and

Profiler<
Why?

If a query requires x number of reads, why would you accept the fact that
different tools report different numbers for the same query?

I understand that these tools DO currently report different number, but in
my opinion, they shouldn't.

Regards,
Greg Linwood
SQL Server MVP

<pa************@hotmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...I am tracing SQL:BatchCompleted and SQL:StmtCompleted. I know that I
should expect some discrepancy between STATISTICTS IO and Profiler but
it's a huge difference between 0 and what STATISTICS IO is reporting.
Plus, *every* line in Profiler is reporting 0 reads (writes and CPU
seem to be OK though).

I checked the version of Profiler and it is correct, although it's not
a client problem because this happens when I do server side traces
using the tracing stored procs.

Thanks

[David Gugick]

Greg Linwood wrote:

I know that I should expect some discrepancy between STATISTICTS IO
and Profiler<

Why?

If a query requires x number of reads, why would you accept the fact
that different tools report different numbers for the same query?

I understand that these tools DO currently report different number,
but in my opinion, they shouldn't.

Regards,
Greg Linwood
SQL Server MVP

Greg,

You probably won't find this helpful since you were a part of the thread
back in September, but Gert Drapers posted the following link that the
OP might find useful - despite it not adddressing the READ problem he is
seeing:

http://support.microsoft.com/default...B;EN-US;314648

Gert also added this:

"In statistics IO the logical read count represents the total number of
page
reads from the tables involved in the statement, both in-memory and disk
pages.

SQL Profiler, or better to say SQL Trace, the server side part which
does
the recording, read count represents the total number of page reads from
all
objects for executing the query, so this include system table access,
which
is not included in statistics IO.

Therefore you will find that when using statistics IO the count becomes
sort
of constant for a given query if all objects are in memory; while when
using
SQL Trace the count can still vary, because it counts all pages it needs
to
read in order to perform the query, so also reading of system tables to
check object existance, object access etc. Also running trace adds
overhead
which can cause additional read, for example the fact that you are
collecting the username for each event raised."

Orignal thread here:
http://www.sqlmonster.com/Uwe/Forum....Query-Analyzer
Having said that, it would be nice if all the tools reported the same
values...

--
David Gugick
Imceda Software
www.imceda.com

[Andrew J. Kelly]

I have seen in rare cases when trace reported 0's when the performance
counters were blown away. A typical symptom would be if you did a select
against the sysprocesses table and all the waittypes show as Misc. If this
is the case then stop all monitoring of performance counters against that
machine and reboot it.

--
Andrew J. Kelly SQL MVP
<pa************@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

I am running a profiler trace against a database and noticed that the
reads column always shows 0. When running the same trace against
another machine I get back values in the reads column. I took a query
that profiler reported as having 0 reads and ran in in query analyzer
wtih STATISTICS IO on and confirmed that there are in fact reads:

Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads
0, read-ahead reads 1.
Table 'tt_clineitem'. Scan count 10, logical reads 125208, physical
reads 1540, read-ahead reads 2995.
Table 'tt_contractitem'. Scan count 32, logical reads 676, physical
reads 0, read-ahead reads 0.
Table 'tt_contract2'. Scan count 3, logical reads 121, physical reads
4, read-ahead reads 0.

I am on SQL 2000 sp3a. Any help appreciated.

Thanks!

[David Gugick]

Andrew J. Kelly wrote:

I have seen in rare cases when trace reported 0's when the performance
counters were blown away. A typical symptom would be if you did a
select against the sysprocesses table and all the waittypes show as
Misc. If this is the case then stop all monitoring of performance
counters against that machine and reboot it.

Good to know Andrew. I'll keep that in mind if I see it reported again.
Thanks.

--
David Gugick
Imceda Software
www.imceda.com

[patrickshroads]

The SQL Server perfmon counters are in fact missing from this box. I
will reinstall them and report back.