Statistics IO results 
Author Message
 Statistics IO results

The logical reads, is that a distinct number of pages read out of cache or
is that just the number of pages read from cache? What I need to know is if
I have a join in my query and the Stats I/O says there are 63,000 logical
reads, does this mean I have 63,000 pages in cache that had to be read (and
could have pushed some other data out of cache) or could it be there are
only 25,000 distinct pages but some were read multiple times to come up with
the 63,000 reads?  If the latter is the case, can I find out how many actual
data pages are in cahce used by the query?

This seems to happen in my query because of a loop join that seems to go
fast but has lots of reads, if I change the query so that I get a hash join,
the reads are lower but the cpu utilization is way too high AND takes longer
than the loop join.

Thanks



Mon, 22 Aug 2005 04:55:18 GMT
 Statistics IO results

Yes, a cache hit count as a logical read, multiple hits on the same page count as multiple
reads. There are some problems with STATISTICS IO, but I don't have the details where I'm
sitting. Also, a hash operation is by nature CPU intensive.

The suggestion I got (from a good source) is to do a SELECT GETDATE() before and after the
query. Just be cautious if you are on a server with other activities. This can serve as a good
complement to STATISTICS IO. Also, you might want to do the measurement with a cold cache, i.e.:

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT GETDATE()
SELECT...
SELECT GETDATE()

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer

Quote:

> The logical reads, is that a distinct number of pages read out of cache or
> is that just the number of pages read from cache? What I need to know is if
> I have a join in my query and the Stats I/O says there are 63,000 logical
> reads, does this mean I have 63,000 pages in cache that had to be read (and
> could have pushed some other data out of cache) or could it be there are
> only 25,000 distinct pages but some were read multiple times to come up with
> the 63,000 reads?  If the latter is the case, can I find out how many actual
> data pages are in cahce used by the query?

> This seems to happen in my query because of a loop join that seems to go
> fast but has lots of reads, if I change the query so that I get a hash join,
> the reads are lower but the cpu utilization is way too high AND takes longer
> than the loop join.

> Thanks



Mon, 22 Aug 2005 05:03:05 GMT
 Statistics IO results
That's the answer I was hoping for.  I've tried the query with a clean cache
& getdate() in the procedure and the opposite is true, loop joins are
usually longer due to the I/O from the HD, but the CPU still really stinks
on the hash join.  I'm trying to figure out how to get a merge join vs the
loop join when it's a really big RS returned.  If you can find the info you
mentioned on the problems with Stats IO I'd appreciate it.



Quote:
> Yes, a cache hit count as a logical read, multiple hits on the same page
count as multiple
> reads. There are some problems with STATISTICS IO, but I don't have the
details where I'm
> sitting. Also, a hash operation is by nature CPU intensive.

> The suggestion I got (from a good source) is to do a SELECT GETDATE()

before and after the
Quote:
> query. Just be cautious if you are on a server with other activities. This
can serve as a good
> complement to STATISTICS IO. Also, you might want to do the measurement

with a cold cache, i.e.:
Quote:

> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> SELECT GETDATE()
> SELECT...
> SELECT GETDATE()

> --
> Tibor Karaszi, SQL Server MVP
> For help on TSQL, please provide code we can execute in Query Analyzer




Quote:
> > The logical reads, is that a distinct number of pages read out of cache
or
> > is that just the number of pages read from cache? What I need to know is
if
> > I have a join in my query and the Stats I/O says there are 63,000
logical
> > reads, does this mean I have 63,000 pages in cache that had to be read
(and
> > could have pushed some other data out of cache) or could it be there are
> > only 25,000 distinct pages but some were read multiple times to come up
with
> > the 63,000 reads?  If the latter is the case, can I find out how many
actual
> > data pages are in cahce used by the query?

> > This seems to happen in my query because of a loop join that seems to go
> > fast but has lots of reads, if I change the query so that I get a hash
join,
> > the reads are lower but the cpu utilization is way too high AND takes
longer
> > than the loop join.

> > Thanks



Mon, 22 Aug 2005 05:16:12 GMT
 Statistics IO results
Post:
-some create statements for the table(s) and index(es).
-the query (or should I say procedure).
-some insert statements to put at least one row
in each table and such that there is at least one row in the input.
-now many rows are in each of the real tables
-now many rows meet various criteria (including how many
rows would be in a real result).

Bye,
Delbert Glass


Quote:
> That's the answer I was hoping for.  I've tried the query with a clean
cache
> & getdate() in the procedure and the opposite is true, loop joins are
> usually longer due to the I/O from the HD, but the CPU still really stinks
> on the hash join.  I'm trying to figure out how to get a merge join vs the
> loop join when it's a really big RS returned.  If you can find the info
you
> mentioned on the problems with Stats IO I'd appreciate it.

> "Tibor Karaszi"


Quote:

> > Yes, a cache hit count as a logical read, multiple hits on the same page
> count as multiple
> > reads. There are some problems with STATISTICS IO, but I don't have the
> details where I'm
> > sitting. Also, a hash operation is by nature CPU intensive.

> > The suggestion I got (from a good source) is to do a SELECT GETDATE()
> before and after the
> > query. Just be cautious if you are on a server with other activities.
This
> can serve as a good
> > complement to STATISTICS IO. Also, you might want to do the measurement
> with a cold cache, i.e.:

> > CHECKPOINT
> > DBCC DROPCLEANBUFFERS
> > DBCC FREEPROCCACHE
> > SELECT GETDATE()
> > SELECT...
> > SELECT GETDATE()

> > --
> > Tibor Karaszi, SQL Server MVP
> > For help on TSQL, please provide code we can execute in Query Analyzer



> > > The logical reads, is that a distinct number of pages read out of
cache
> or
> > > is that just the number of pages read from cache? What I need to know
is
> if
> > > I have a join in my query and the Stats I/O says there are 63,000
> logical
> > > reads, does this mean I have 63,000 pages in cache that had to be read
> (and
> > > could have pushed some other data out of cache) or could it be there
are
> > > only 25,000 distinct pages but some were read multiple times to come
up
> with
> > > the 63,000 reads?  If the latter is the case, can I find out how many
> actual
> > > data pages are in cahce used by the query?

> > > This seems to happen in my query because of a loop join that seems to
go
> > > fast but has lots of reads, if I change the query so that I get a hash
> join,
> > > the reads are lower but the cpu utilization is way too high AND takes
> longer
> > > than the loop join.

> > > Thanks



Mon, 22 Aug 2005 06:15:36 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Get statistics io with OLEDB ADO recorset

2. script for reporting per-table IO statistics

3. STATISTICS IO

4. STATISTICS IO

5. set statistics io OFF

6. statistics io

7. SET STATISTICS IO: Scans ?

8. 6.5 -- no statistics io for select into?

9. Set Statistics IO On

10. Unix filesystem: use buffered IO or direct IO?

11. Help... float data resulting in inaccurate statistics :(

12. CPU Statistics as a result of Oracle


 
Powered by phpBB® Forum Software