SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips

SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips

By : Brad McGehee Apr 04, 2007

 

-http://www.sql-server-performance.com/tips/indexed_views_p1.aspx

 

If your application needs to access views often, consider adding a unique clustered index to your views to significantly improve performance. When a view is created with a unique clustered index, the view is created with the clustered index, which is created and stored in the database the same way as a clustered index on a table.

Once a unique clustered index has been created for a view, you can also create non-clustered indexes for the same view, which can be used by queries against the view to enhance performance.

As the underlying tables of the view are modified, the clustered index, and any non-clustered indexes of the view, is modified so that it is always up-to-date when it is accessed. And just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index. 

Indexed views can be used by SQL Server two different ways. First, the view can be called directly from a query, as conventional views are currently used. But instead of running the view's underlying SELECT statement and creating the view's result set on the fly, it uses the unique clustered index to display the results of the view almost immediately. Second, any query that is run on SQL Server 2000/2005 is automatically evaluated to see if any existing indexed views exist that would fulfill the query. If so, the Query Optimizer uses the indexed query, even though it has not been specified in the query, greatly speeding the query.

To get the most benefit out of indexed views, you need to use the SQL Server 2000/2005 Enterprise Edition. While you can create indexed views in the other editions of SQL Server 2000/2005, they will not be automatically considered by the query optimizer, and they require the use of the NOEXPAND hint to be used. [2000, 2005] Updated 7-10-2006

*****

The best candidates 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.
• For tables that would benefit from multiple clustered indexes. In a sense, an indexed view is the equivalent of a clustered index. But don't do this if the table is subject to many INSERTS, UPDATES, or DELETES.

The worst candidates for indexed views include:

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

[2000, 2005] Updated 7-10-2006

*****

Selecting the ideal unique clustered index, and any non-clustered indexes, for an indexed view is very complicated . This process should not be done in isolation, but done at the same time indexes are selected for the underlying table(s) used in the view. It is important not to add indexes to the underlying table(s) and the view that are redundant. It is highly recommended that you use the Index Tuning Wizard or Database Engine Tuning Advisor to help evaluate and select the indexes to be used in both the underlying table(s) and the index. [2000, 2005] Updated 7-10-2006

*****

When designing indexes for indexed views, keep the following considerations in mind :

• Select indexes that can be used by multiple queries in order to get the biggest bang for the buck.
• Avoid creating indexed views on tables that change a lot (many INSERTS, UPDATES or DELETES).
• Just like designing queries for tables, keep the index column as narrow as possible in order to reduce the size of the index.
• If the resulting view returns about the same number of rows as the underlying table(s), then the indexed view may not add much performance benefit.
• Instead of creating a single large indexed view, consider creating two or more smaller views. This is especially handy if the underlying tables are located in different databases, or you need to use the UNION statement, as both of these are prohibited in indexed views. By using multiple indexed views, you can overcome some of the limitations of indexed views.
• Sometimes it is not possible to design a single indexed view to assist the performance of a query. If this is the case, consider create two or more smaller indexed views that can satisfy the needs of the query, boosting its performance.

[2000, 2005] Updated 7-10-2006

*****

Another way to think of an indexed view is as an additional "clustered index" . We already know that an indexed view is a physical implementation of a view that has a clustered index. We also know that adding an indexed view incurs its own overhead, and for an indexed view to be worth it, that the performance gains from using it must be greater than its cost.

What we must not forget is that an indexed view is just a view, and that views can be significantly restricted by the use of an appropriate WHERE clause. For example, let's say that we have a view that joins two tables. The first table is 5 million rows and the second table is 2 million rows. Depending on your goals, you can create an indexed view on these joined tables with little overhead, assuming that you have an appropriate WHERE clause in the view.

In our example, the resulting indexed view might only have 200 rows in it. If this is the case, then the maintenance overhead of this indexed view will be very small because SQL Server will only have to track changes for a very small percentage of the total number of rows in both tables. SQL Server need only manage the overhead of the total number of rows in the indexed view, not both tables.

If you keep the above in mind, you can create an indexed view for the sole purpose of speeding up common queries (in a sense, acting as an additional clustered index for a table), assuming that you appropriately use the WHERE clause to minimize the amount of rows that SQL Server has to maintain overhead for. [2000, 2005] Updated 3-15-2005