SQL Server Performance

Indexed view

Discussion in 'Performance Tuning for DBAs' started by vkrajann, Dec 8, 2004.

  1. vkrajann New Member

    Query optimizer at times directly go to the base table and the query does not get the advantage of the indexes on the view.
  2. thomas New Member

    Is it Enterprise Edition of SQL Server 2000? If it is not, you need to use the WITH NOEXPAND hint in your selects to get the server to use indexed views.

    Tom Pullen
    DBA, Oxfam GB
  3. vkrajann New Member

    Yes i am using Enterprise edition with sp3. Some places it works. But microsoft support team is not recommending to use WITH NOEXPAND hint in the enter. edition.
  4. Chappy New Member

    Are you able to post a script in order that we can reproduce this?
  5. satya Moderator

    What kind of other Table/index hints you're using?
    How often the SPs and other execution plans were updated, I mean UPDATE statistics part?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. vkrajann New Member

    Update statistics is set to automatic. View is having cluster index and nonclusterindex.
  7. satya Moderator

    How about schedule of reindexing and manual update stats.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. vkrajann New Member

    Did not improve the performance.
  9. satya Moderator

    Can you post the query and execution plan for further assesment.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. vkrajann New Member

    Here is the query :
    SELECT Count_type, Store_Number, BusinessUnit_Id, Colour_Id, Stroke_Id, Department_Id
    Count_Datetime, UPC, Fk_Stockpot_id
    FROM VWCOUNTACTIVITY
    WHERE Count_type = 'NegativeStock'
    and Store_Number = '2600'
    and BusinessUnit_Id = '01'
    and Department_Id = 'T01'
    and Stroke_Id = '0001'
    and Colour_Id = 'NC'
    and UPC = '09325937'
    and Fk_Stockpot_id = 1
    and Count_Datetime = '2004-11-02 16:51:50.140'

Share This Page