locked
Indexes are not getting used on indexed view RRS feed

  • Question

  • Hi,

    I have a veiw that is heavly used by the application with one unique cluster index and one non-clustered index.

    To increase performance i have created the non-clustered index as i mention above.

    But the actual problem is that my non-clustered index is not geting used in index view,when i see the query plan it reference the actual table.

    Select * from ViewName    ----->It used the above unique clustered index.

    select columnName from ViewName -->It start using the actual tables name.

    Can some one help me on it.

    This above condition accour in my Production database.

     

    I have  test database with same schema and it used the non-clustered index  as expected.

    I have compared both the test and prod database,all schema,index etc are same...but differnet query plan.

     

     


    Regards Madan Agrawal Please vote if you find my post valuable for you.
    Thursday, December 15, 2011 10:53 AM

Answers

  • What version and edition of SQL Server are you using?

    If you are not using Developer / Enterprise editions, then try using table hint NOEXPAND.

    Table Hints (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187373.aspx

    In the above link, check for "Using NOEXPAND".

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Stephanie Lv Thursday, December 22, 2011 1:22 AM
    Thursday, December 15, 2011 6:13 PM
  • Madan,

    The two databases could have different execution plans if the optimizer sees differences between the data.  If your development database doesn't have as much data, or the statistics have not been updated in one of the environments, then it's very reasonable that the execution plans would differ.  If the data sets are the same and the schemas are the same, make sure the statistics are updated and recompile the objects and they should match.

    Eric Isaacs


    Eric Isaacs
    • Marked as answer by Stephanie Lv Thursday, December 22, 2011 1:22 AM
    Friday, December 16, 2011 7:37 PM

All replies

  • What version and edition of SQL Server are you using?

    If you are not using Developer / Enterprise editions, then try using table hint NOEXPAND.

    Table Hints (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187373.aspx

    In the above link, check for "Using NOEXPAND".

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Stephanie Lv Thursday, December 22, 2011 1:22 AM
    Thursday, December 15, 2011 6:13 PM
  • Update the database statistics and recompile the View.  If that doesn't work, consider making the view into an indexed view.
    Eric Isaacs
    Thursday, December 15, 2011 7:32 PM
  • I have two databases on same server(Development edition) ,One is little bit old in terms of data and another is just a replica taken few days before of production(Enterprise editon) server.

    But when i excute the same query on both databases in development edition, both gives me two differnt execution plan.

    One is using indexes created on view and another using indexes of table that  has been reference by veiw.

    Schema and indexes are same on both databases in developer edition server.

    can someone let me what i am missing.

     


    Regards Madan Agrawal Please vote if you find my post valuable for you.
    Friday, December 16, 2011 4:45 AM
  • Just because there's an index on a view doesn't mean that SQL Server will always use it. Please try the suggestion made by Hunchback.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Friday, December 16, 2011 4:53 AM
  • Thanks Jeff,

    But i have one question, Why sql engine on same server use two differnt query execution plan for same type of schema on two different database.


    Regards Madan Agrawal Please vote if you find my post valuable for you.
    Friday, December 16, 2011 4:57 AM
  • And aslo if you see pinal dave post...He say indexes on view are never getting used. But this is not in my case.

    http://blog.sqlauthority.com/2010/10/12/sql-server-indexed-view-always-use-index-on-index/


    Regards Madan Agrawal Please vote if you find my post valuable for you.
    • Edited by Madan.Agrawal Friday, December 16, 2011 5:02 AM changes
    Friday, December 16, 2011 4:59 AM
  • Madan,

    The two databases could have different execution plans if the optimizer sees differences between the data.  If your development database doesn't have as much data, or the statistics have not been updated in one of the environments, then it's very reasonable that the execution plans would differ.  If the data sets are the same and the schemas are the same, make sure the statistics are updated and recompile the objects and they should match.

    Eric Isaacs


    Eric Isaacs
    • Marked as answer by Stephanie Lv Thursday, December 22, 2011 1:22 AM
    Friday, December 16, 2011 7:37 PM