SQL Server Performance

Index View

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, May 9, 2008.

  1. isa New Member

    Hello everyone, I m using SQL Server2005, I have a Complex view whichcontains lot of tables and Inner, Right and Left Outer Join used, ThisView Returns the approx 2 lac rows and takes processing time is 36secs, Kindly tell me what can i do ? i mean to say Create Index View orcreate small indexes on this view for good performance?
    And tell me which type of Index used and on which column?

    Reply me asap, Thanx in Advance.
  2. satya Moderator

  3. FrankKalis Moderator

    You've provided not enough information to suggest anything meaningful. I would start by investigating where the time now is spent and rather tune that instead of looking for ways to worked around the root issue.
  4. moh_hassan20 New Member

    take into account that:
    indexed view is supported only in Enterprise Edition (EE) not Standard Edition(SE).
    EE query processor use the clustered index, while the SE query processor scans the table and ignores the index on the view .
  5. MichaelB Member

    Per MSDN.... (http://msdn.microsoft.com/en-us/library/ms187864.aspx)
    Indexed views can be created in any edition of SQL Server 2005. In SQL Server 2005 Enterprise Edition, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
  6. moh_hassan20 New Member

    Thank MichaelB for that tip (noexpand) for other editions of sql 2005.
    i also found that FAQ :
    Q. Why isn't my indexed view being picked up by the query optimizer for use in the query plan?
    A. There are three primary reasons the indexed view may not be being chosen by the optimizer:
    (1)You are using a version other than Enterprise or Developer edition ofSQL Server. Only Enterprise and Developer editions support automaticquery-to-indexed-view matching. Reference the indexed view by name andinclude the NOEXPAND hint to have the query processor use the indexedview in all other editions.
    .....
    the complete article: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx:
  7. MichaelB Member

    I wasnt trying to say that it is completely supported, but your reply of :
    indexed view is supported only in Enterprise Edition (EE) not Standard Edition(SE).
    EE query processor use the clustered index, while the SE query processor scans the table and ignores the index on the view .
    did not completely answer the question. It is supported, but will not work without the no hint. You said it was only supported in EE which would be false.
  8. moh_hassan20 New Member

    i said:
    indexed view is supported only in Enterprise Edition (EE) not Standard Edition(SE).

    and support means from my point of view as i said:
    "EE query processor use the clustered index, while the SE query processor scans the table and ignores the index on the view ."

    what is the objective of indexed view? It is enhancing performance when the query processor use the clustered index.

    and that is completly valid in sql2000:
    review: http://support.microsoft.com/kb/268361

    Microsoft says
    "You can create Indexed Views on all editions of SQL Server 2000. While it is possible to create indexed views in all editions of SQL Server 2000, the benefits in performance can be obtained only in the Enterprise and Developer edition of SQL Server 2000. This is because the Query Optimizer does not take the indexes on the views into consideration when generating an execution plan in other editions."
    review: http://support.microsoft.com/kb/270054/EN-US/

    so
    indexed view is supported only by EE is true .
    I think that my last post did answered completely the question.




  9. FrankKalis Moderator

    To add to the discussion:
    We're using Enterprise Edition exclusively and still I have to specify the NOEXPAND hint to force the use of a certain indexed view. I'm not entirely sure why this view isn't considered by the QP, but I'm planning to investigate when there is time [:D]
  10. FrankKalis Moderator

    Btw, cool down guys. [:)]
  11. MichaelB Member

    GGGGRRRRRRR
    just kidding!

Share This Page