SQL Server Indexed View Question

Question

I have a requirement to create a report that is killing the processor and taking a long time to run.

I think I could speed this up significantly by creating an index view that keeps all this data in one place making it a lot easy to query/report on. This view would not just be used for the report as I think it would benefit quite a few areas in the data layer.

The indexed view will potentially contain 5 million+ records, I cant seem to find any guidance as to at what point indexed views are not longer recommended. I assume that an index view of this size would take considerable time to build when SQL first starts, but I would hope after this the cost of maintaining it would be minimal.

Is there any kind of best practice guide as to when to use index views and when not to use them? Would the view rebuild itself after every server restart or does it get stored somewhere on the disk?

Answer 1

I'm not aware of any guidance concerning size of indexed views. It's effectively a separate table that's being "automagically" updated every time the base tables on which it depends are updated, so I tend to think of it as a separate table.

As to your question on the building of the index - it's stored on disk, the same as every other index, so it doesn't get rebuilt during server restart (other than any repair that takes place due to transactions not having completed before the restart).

Answer 2

The index associated with your Indexed View will be updated whenever updates are made to the any of the columns in the index.

High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.

The real benefits of Indexed Views are when you have aggregates that are too expensive to compute in real time.

Please see: Improving Performance with SQL Server 2008 Indexed Views:

Indexed views can increase query performance in the following ways:

• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

The 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. Indexed views are rarely used in queries with a cost of less than 1.

Applications that benefit from the implementation of indexed views include:

• Decision support workloads.
• Data marts.
• Data warehouses.
• Online analytical processing (OLAP) stores and sources.
• Data mining workloads.

From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

• Joins and aggregations of large tables.
• Repeated patterns of queries.
• Repeated aggregations on the same or overlapping sets of columns.
• Repeated joins of the same tables on the same keys.
• Combinations of the above.

Answer 3

An indexed view (aka materialized view) is maintained by SQL Server after every change to the underlying table(s). Needless to say, you should not have an indexed view on a table that has traffic.

For your problem, a better solution would be to run the query and store it in its own table, like:

select * into CachedReport from YourView

That will give you the performance of an indexed view, while you can decide when to refresh it. For example, you could refresh it by running the select into query from a scheduled job every night.

Answer 4

There's no hard row number limit on when to use a table or a materialised view. However as a guide line avoid a materialised view over volatile tables - the load can kill your server.

First off as Timothy suggested check the indexes on your underlying tables, then the statistics. Your Query Optimiser might be just on the complete track due to missing/out of date statistics.

If this doesn't help with performance check what data is really required from the view as my guess is that a) the row count and b) the row size is what is killing your server loading the whole view into temp table and running it through I/O contention.