SQL Server Performance

Index hint on Indexed views

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by EasySQL, Aug 13, 2007.

  1. EasySQL New Member

    Hi All
    I have a indexed view which I am calling from a sp.To treat this view as a table I am using the with (noexpand) hint in the T-Sql.This view has 3 indexes.Whenever I execute the sp and look at the execution plan it never utilizes the other indexes except for the clustered index even though The other column are used in the where clause.How can I force the optimizer to use the non clustered indexes on the view to bring down the execution time.
    When I tried giving the index hint after removing noexpand option it is throws warning that the hint would be ignored.Is it possible to implement both the noexpand hint and the index hint(can this be conditional based on the where clause columns) .
    sample script
    select tv.*, tcp.id
    FROM V_Tableview tv WITH(NOEXPAND)
    INNER JOIN dbo.Table2 TCP
    ON Tv.ID = TCP.ID

  2. Luis Martin Moderator

    SQL use the best plan to execute any query, views or tables.
    Most of times the idea to force the use of any index, is no good.
    The indexes created for you to improve the performance, came from DTA or from yourself?
  3. satya Moderator

  4. EasySQL New Member

    I created the additional indexes on the materialized view based on the filter criteria (columns frequently used in the where clause) .But somehow the optimizer utilizes only the clustered index. The clustered index is a composite index combining 3 columns.The indexes which i created have different columns and not the one that are in the clustered index.The rows returned from the Indexed View is 2500 out of the total 7 million rows.Is that the reason that it doesn't use the non clustered index.
    Thanks for your replies and the link to tips.

  5. satya Moderator

    BTW what is the edition of SQL you are running?
    It is true that NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index and you could test it using OPTION (EXPAND) clause.
  6. EasySQL New Member

    Hi satya
    Here is the version i am using
    Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
  7. satya Moderator

    Try with other option as mentioned above.

Share This Page