SQL Server Performance Forum – Threads Archive
Query analysis using set statistics time on
Hi We are trying to analyse a query which involves a inner join between 2 tables.
with set statistics time on and
set statistics io on we got the following results Table ‘trainy_let_mast’. Scan count 330, logical reads 1027, physical reads 0, read-ahead reads 0.
Table ‘TRAINY_LET_TRAN’. Scan count 2, logical reads 10609, physical reads 0, read-ahead reads 0. SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 253 ms.
Then we chaned the sequence of fields used in inner join condition to match the sequence
defined in the clusterd index. Then different we got set of values as below Table ‘trainy_let_mast’. Scan count 330, logical reads 1027, physical reads 27, read-ahead reads 0.
Table ‘TRAINY_LET_TRAN’. Scan count 1, logical reads 10609, physical reads 0, read-ahead reads 0. SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 540 ms.
Now to improve performance what values in this shuld be given weitage. Shuld we look for
low cpu time or low scan count or low physical read. Please suggest Regards
Sudha
Just curious to know if you have used clustered unique index in second one ?
Cheers
Sat
Hi here is the explanation In this case 2 tables r involved. Trainy_let_mast and trainy_let_tran. And primary key has been
defined on 3 columns in both in the order col1, col2, col3. As it is sql 2000, by defult this
creates a clustered unique index in that combination of col1, col2, col3. In inner join the join condition is
a.col3=b.col3 and a.col2=b.col2 and a.col1=b.col1 in first case.
a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 in second case (here this sequence matches the order followed in the case of index) In both the cases same index (created by default when primary key is set) is present. No other index is created.
One rule of the thumb is to have a clustered index on an identity field, or at least on a single unique column, and preferably not on a composite key. Another rule of the thumb is that – if you need a multi-column FK – you should have a parallel FK based on the identity column of the parent table. The columns in a composite index should be ordered by the number of distinct values relative to your most common queries/joins, putting the column with the highest number first. For different queries, you may need different indexes, as it depends on what you’re filtering/joining on. *** Anyway, did you compare the execution plans for the two versions of the query?
I usually try to decrease the subtree cost, logical reads, and cpu. Most of the time, lowering the subtree cost lowers the other two …
]]>