GilaMonster (4/2/2009)
Grant Fritchey (3/27/2009)
1. The first query, by a long shot. Although it has a higher number of physical reads. But if both these queries are accessing the same data then it was probably in cache when you ran the second query.Why do you say that? It has higher logical IOs (100 000 vs 80 000) as well as the higher physical
Mainly based on the scan count. I'm still assuming, possibly incorrectly, that the second query was able to read from cache where as the first had to go to disk. I think if the second query had to go to disk as well, it'd be quite a bit longer.
However, I wasn't aware that the scans were actually a little less of a decent measure than I had originally believed (by the way, do you just memorize Connect bugs or something) so maybe my original estimate is off.
Generally, I don't count on the logical IOs as a single measure. I combine them with what I see in the execution plan and, possibly incorrectly, the scans, along with the actual execution times. What's not known here is how the queries were tested. Did the OP clear the cache and the buffers prior to each execution? If not, you're not getting a thorough test. At the very least we'd need to run the queries in reverse order and see how that affects the results.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning