SQL Server Performance

Index questions

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jul 15, 2009.

  1. atulgoswami New Member

    I got some questions in my mind related to Index internal processing in some different scenarios
    1. Query which has condition like Col1>Col2 and both the columns are covered in common non clustered index.
    2. Query which has one inner join on columns (table1.col1=table2.col2), Col1 has a non clustered index and this index is covering two more columns, which are not used in the query.
    OR
    Table has one non clustered index with three columns and only one column is used in JOIN operation
    3. Imagine a case, where one temp table in SP is referenced 10 times with 10 different pattern (columns in JOIN) then how to decide candidates for non clustered index
    These are some very raw questions, which i was thinking while working on one slow query. It would be of great help if i get any article or any other information source. I tried to search these things but so far no success.
    Thanks
  2. Adriaan New Member

    Anyone jump in, here's my thoughts ...
    #1
    The > operator means the engine expects to match 1/3 of the rows, and may do a table scan for this.
    #2
    The big question is whether Col1 is the first column in the multi-column index. Whether the index is clustered or not is irrelevant for queries.
    #3
    If the patterns depend on the parameters, then create the index(es) depending on the parameters ... But as long as this temp table contains a small number of rows, you won't really need indexes (you will need a PK, so as not to have a heap table).

Share This Page