MS SQL Server, Statistics IO results

Statistics IO results

Author	Message
cw3 #1 / 4	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

Tibor Karasz #2 / 4	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

cw3 #3 / 4	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

Delbert Glas #4 / 4	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

Page 1 of 1

[ 4 post ]