Best and Worst of having indexed views in SQL 2005

Published 30 July 07 08:15 AM | SQL Master 

Consider to have indexed view if the queries are accessing using a frequently occurring aggregations and joins. Irrespective of whether or not a query is asked frequently, it may be a candidate for an indexed view if it takes significant time to answer, and the value of getting the answer quickly is high.  Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. As the BOL explains:

 a query optimizer considers indexed views only for queries with nontrivial cost. This avoids situations where trying to match various indexed views during the query optimization costs more than the savings achieved by the indexed view usage.

Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While tuning the physical design of a database, trade offs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Database Tuning Advisor should be used wherever it is possible. 

In this case a simple set of terms can get you understanding of best and worst of considering Indexed Views within the database, the best options for indexed views include:

  • Data marts, data warehouses, decision support, data mining, OLAP applications.
  • Views that join two or more large tables.
  • Views that aggregate data.
  • Repeated patterns of queries.
  • tables with multiple clustered indexes that has minimum updates & deletes.
  • Each view has to be investigated for the potential substitution before it is rejected.

  • The view must be created using schema binding and any user-defined functions referenced in the view must also be created with the SCHEMABINDING option.

The worst (bad performance) options for indexed views include:

  • OLTP applications with a high percentage of INSERTS, UPDATES, and DELETES within your tables that are queried frequently.
  • Queries that don't use JOINs or aggregations.
  • Views that end up creating more rows than found in the underlying base tables.

  • Additional disk space will be required to hold the data defined by the indexed view.

So net performance improvement achieved by a view is the difference of the total query execution savings offered by the view and the cost to store and maintain the view.

Comments

# SSQA.net - SqlServer-QA.net said on July 30, 2007 11:24 AM:

Consider to have indexed view if the queries are accessing using a f requently occurring aggregations

# Log Buffer #56: a Carnival of the Vanities for DBAs « H.Tongu?? YILMAZ Oracle Blog said on August 3, 2007 4:56 AM:

PingBack from http://tonguc.wordpress.com/2007/08/03/log-buffer-56-a-carnival-of-the-vanities-for-dbas/

# Other SQL Server Blogs around the Web said on August 6, 2007 9:53 PM:

Consider to have indexed view if the queries are accessing using a f requently occurring aggregations

# TrackBack said on August 16, 2007 6:13 AM:
# TrackBack said on August 16, 2007 6:13 AM:
# TrackBack said on August 16, 2007 6:14 AM:
# TrackBack said on August 16, 2007 6:14 AM:
Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.