|
|
Posted by serge on 10/01/10 11:41
Thanks Erland, I did learn a few more things today after trying the
things you said. Plus I also remembered one of my other posts where
you suggested to compare the execution plan in text format.
This is something I'll be doing while I continue my understanding
of the execution plan in my 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.
I did test this but I started getting reads of 100,000 instead of 15
million.
But I can't say I did a clean job. I did a
SELECT *
INTO TABLE2
FROM TABLE1
Then dropped the Foreign Keys of TABLE1, dropped TABLE1,
renamed TABLE2 to TABLE1.
Then i re-run my query and i got 100,000 reads.
Anyhow what I found after is like you said statistics related.
> 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.
This time I restored the same db on my second SQL instance on my
machine. I ran the query dozens of time and kept getting 15 million reads
in 1+ minute.
I left the machine idle for a few hours, returned back, re-ran the query
and same 15 million reads...
Immediately I ran
UPDATE STATISTICS dbo.TABLE1 WITH FULLSCAN
and i re-ran the query and it took 2 seconds and 87 thousand reads!
Ok I can tell you that yesterday on my first SQL instance I ran the
Database Maintenance Plan wizard and chose to update the stats using
50% sample data. And that explains why my queries were running in 2
seconds. But I still can't understand why my queries kept running in 2
seconds
if i was dropping the database and restoring it brand new! I thought the
database
stats info would be stored inside the database. It's almost like either the
stats
info, or the execution plan???? maybe is being stored in the master
databases
of my SQL Server??
I just did another test on my second SQL Server Instance. I dropped the db,
restored it again, ran my query, it took 10 seconds to execute, i ran it
again
and it took 2 seconds and 87 thousand reads. So it looks to me something
is being stored in the master database of my SQL Server Instance otherwise
why it is not taking 15 million reads anymore?
Thank you
Navigation:
[Reply to this message]
|