Having a major performance loss with an Indexed view that is using a INDEX SCAN on a table inspite of relevant clustered indexes on the joined tables? A bit of background on this issue is first query doesn’t use indexed view. It uses index scan on table that has an inner join nested loops with IndexSeek on another table. Index scan cost is 0.17 + other operations. Second query is forced to use index view, it’s index seek. Cost is 0.003 Third query is using index scan on indexed view. Why indexed view is not used for first query? It’s much cheaper. I run enterprise edition (you can see that third query uses indexed view w/o NOEXPAND.
Its important to know about Indexed View before going into details, consider these few guidelines when designing indexed views:
- Design indexed views that can be used by several queries or multiple operations.
- For example, an indexed view that contains the SUM of a column and the COUNT_BIG of a column can be used by queries that contain the functions SUM, COUNT, COUNT_BIG, or AVG. The queries will be faster because only a small number of rows from the view need to be retrieved rather than the full number of rows from the base tables and a portion of the computations required for performing the AVG function have already been done.
- Keep the index key compact.
- Consider the size of the resulting indexed view.
- In the case of pure aggregation, the indexed view may not provide any significant performance gains if its size is similar to the size of the original table.
-
Design multiple smaller indexed views that accelerate parts of the process.
Indexed views are similarly maintained; however, if the view references several tables, updating any of them may require updating the indexed view. Unlike ordinary indexes, a single row insert into any of the participating tables may cause multiple row changes in an indexed view. This is because the single row may join with multiple rows of another table. The same is true for updates and deletes. Consequently, the maintenance of an indexed view may be more expensive than maintaining an index on the table. Conversely, the maintenance of an indexed view with a highly selective condition may be much less expensive than maintaining an index on a table because most inserts, deletes and updates to base tables the view references will not affect the view. These operations can be filtered out with respect to the indexed view without accessing other database data. As a general recommendation, any modifications or updates to the view or the base tables underlying the view should be performed in batches if possible, rather than singleton operations. This may reduce some overhead in the view maintenance.
The user can achieve the perfomance after taking into consideration of table structure & schema with proper indexing strategy and using hints such as NOEXPAND will gain the performance. Further there is much information on designing and performance is availabile in this regard, refer http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx link.
]]>