Query optimizer at times directly go to the base table and the query does not get the advantage of the indexes on the view.
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
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.
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.
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.
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.
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'
