suhasini.m (3/27/2009)
I have 2 queriesSELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1
SELECT * FROM Table1
JOIN Table2
ON Table1.Column1 = Table2.Column1
AND Table1.Column2 = XXX
Both these query returns same number of results say 7000. And Table1 is a partitioned table with Column2 is the partition key. When i compared the execution plan of these 2 queries, the relative cost of these 2 queries is 65:35 which shows 2nd query is around 40% better than 1st query in terms of performance. But my concern is with the IO statistics.
The IO statistics of these two queries are as follows.
1st Query
---------
Table1 - Scan Count:0 Logical Reads:100000Physical Reads:200
2nd Query
---------
Table1 - Scan Count:7000 Logical Reads:80000Physical Reads:100
There has been a decrease in the no of logical and physical reads. But the scan count has increased to the number of rows returned by the query. I have the following questions on this.
1. Which is the better query in terms of IO?
2. What does Scan Count exactly mean. And howz it related to IO.?
3. As per the Scan count defenition its a table or index scan. If its a table or index scan howz it better in performance. (There are no scans happening on this table in the execution plan)?
4. Is there a better way to compare the IO of queries?
5. Is there a way to get the IO in terms of size (like MBs) other than the no of reads?
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.
2. Scans are the number of times that the engine had to scan an index or a table. It tells you how the data is being accessed and can help explain performance issues. Scans are not inherently bad, but high numbers of scans should give you pause.
3. There must be scans going on. Do you have user defined functions in the query? These can mask all kinds of performance problems including scans. Post the execution plans if you can.
4. Generally, that's the best way.
5. Not specific to the execution of a particular query, no. You can get these measures through a server-side trace. Otherwise you're looking at performance counters that measure what's happening within the server, probably not helpful for performance tuning a particular query.
"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