SQL Server Performance

Query analysis using set statistics time on

Discussion in 'T-SQL Performance Tuning for Developers' started by rrl_sudha, Jun 27, 2007.

  1. rrl_sudha New Member


    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

  2. techbabu303 New Member

    Just curious to know if you have used clustered unique index in second one ?


    Cheers
    Sat
  3. rrl_sudha New Member

    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.


  4. Adriaan New Member

    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?
  5. sql_er New Member

    I usually try to decrease the subtree cost, logical reads, and cpu. Most of the time, lowering the subtree cost lowers the other two ...

Share This Page