|
|
Posted by Erland Sommarskog on 10/01/16 11:41
serge (sergea@nospam.ehmail.com) writes:
> 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?
Or at least accessed. Say that you run a query like:
SELECT SUM(OD.UnitPrice * OD.Quantity)
FROM Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE O.CustomerID = N'VINET'
This gives the following statistics IO:
Table 'Order Details'. Scan count 5, logical reads 10, physical reads 0,
read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
There are five orders for VINET in Northwind..Orders. SQL Server seeks
the index on Orders.CusteromerID and for every match it looks up the
order in Order Details. Not by scan, but by seeking the index. But
the output from STATISTICS IO does not make that distinction.
Note that is what happens with nested-loop joins. Had the join been
implemented as a merge or a hash join, the Scan Count would be 1
for both table - but then it had also truely been a scan.
> 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?
My guess is auto-statistcs. When you run queries than scan a lot of
data, SQL Server takes the oppurtunity to sample statistics abou the
data. Once that statistics is available, the optimizer may find a better
way to implement the query.
If you were to create a new table, and move the data over to that
table, my prediction that you will see the same result. First 15
million reads a few times, and then a reduction to 87000 reads. If you
also study the query plan, you will see that it changes.
> Is it my way of using the computer? Maybe i was opening
> too many applications, SQL was fighting for memory?
No, that has nothing to do with it.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|