Posted by serge on 10/01/58 11:41

I am running a query in SQL 2000 SP4, Windows 2000 Server
that is not being shared with any other users or any sql
connections users. The db involves a lot of tables,
JOINs, LEFT JOINs, UNIONS etc... Ok it's not a pretty
code and my job is to make it better.

But for now one thing I would like to understand with your
help is why the same SP on the same server and everything
the same without me changing anything at all in terms of
SQL Server (configuration, code change, ...) runs in
Query Analyzer in 1:05 minute and i see one table get a
hit of 15 million logical reads:
Table 'TABLE1'. Scan count 2070, logical reads 15516368,
physical reads 147, read-ahead reads 0.

This 'TABLE1' has about 400,000 records

The second time i ran right after in Query Analyzer again:
Table 'TABLE1'. Scan count 2070, logical reads 15516368,
physical reads 0, read-ahead reads 0.


I can see now the physical reads being 0 as it is
understandable that SQL is now fetching the data from
memory.

But now the third time I ran:
Table 'TABLE1'. Scan count 28, logical reads 87784,
physical reads 0, read-ahead reads 0.


The Scan count went down from 2070 to 28. I don't
know what the Scan count is actually. It scanned the
table 28 times?
The logical reads went down to 87,784 reads from 15
million and 2 seconds execution time!

Anybody has any ideas why this number change?

The problem is i tried various repeats of my test, i
rebooted the SQL Server, dropped the database, restored
it, ran the same exact query and it took 3-4-5 seconds
with 87,784 reads vs 15 million.

Why i don't see 15 million now?

Well i kept working during the day and i happen to run into
another set of seeing 15 million again. A few runs would
keep running at the paste of 15 million over 1 minute and
eventually the numbers went back down to 87,784 and 2
seconds.

Is it my way of using the computer? Maybe i was opening
too many applications, SQL was fighting for memory?
Would that explain the 15 million reads?
I went and changed my SQL Server to used a fixed memory
of 100 megs, restarted it and tested again the same
query but it continued to show 87,784 reads with 2 seconds
execution time.

I opened all kinds of applications redid the same test
and i was never able to see 15 million reads again.

Can someone help me with suggestions on what could be
this problem and what if i could find a way to come to
see 15 million reads again?

By the way with the limited info you have here about the
database I am using, is 87,784 reads a terrible number of
reads, average or normal when the max records in the many
tables involved in this SP is 400,000 records?

I am guessing it is a terrible number, am I correct?


I would appreciate your help.

Thank you

[Back to original message]