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

Many times, as DBAs, we are expected to performance tune poorly performing third-party applications. Unfortunately, there is often not too much we can do, other than tweaking indexes, because we are unable to modify the application's design or code like we would have to in order to boost performance.

If you are running SQL Server 2000/2005 Enterprise Edition, you have another tool in your toolkit to help boost the performance of a third-party application, and that is indexed views. While a third-party application can't use an indexed view directly (code would have to be changed to use a newly created indexed view directly), the query optimizer can.

For example, if you find a poorly performing query that is run by the application, you can create an indexed view that could be used to speed the query. (This assumes that an indexed query is appropriate for the query). Even though the application does not know about the indexed view, the query optimizer does, and it can evaluate it to see if it will perform better than running the query normally. And if the query optimizer does choose to use the indexed view, the query's performance is boosted.

While this is a great concept, it can take a lot of work, and you will need to experiment to see if the query optimizer uses the query you create. Because if it doesn't, then you need to remove the indexed query, as it will be wasted overhead if it is not used. [2000, 2005] Updated 3-15-2005

*****

Ideally, indexed views should be created at the same time clustered and non-clustered indexes are created for a table. The reason for this is that it is easy, if you don't plan well, to create indexed views and clustered and non-clustered indexes that overlap and are redundant. Redundant data increases overhead and hurts I/O performance, so this is to be avoided. If you can't create both at the same time, just keep in the back of your mind that whenever you tweak indexed views or indexes on a table, that you need to reevaluate all of the indexes on the table at this time in order to prevent redundant indexes from occurring. [2000, 2005] Updated 3-15-2005

*****

Use the SQL Server Index Wizard or Database Engine Tuning Advisor and an appropriate Profiler trace file to help identify potential indexed views. When the Wizard or Advisor runs, it automatically looks for potential indexed views and recommends any that if finds. But don't rely on this tool as the only way to identify indexed views, as it is unable to identify all possible candidates. [2000, 2005] Updated 3-15-2005