SQL server: Indexed views and filtered indexes: making your very large database smaller.
For many clients, I have been able to manage very complex tuning with indexed views and filtered indexes.
- Indexed views: virtual framework physically maintained!
Of course, indexed views are schema binding. Schema binding tables via indexed views offer additional advantage in terms of securing the structure of your database. The caveat of such views is mainly the performance impact on dml operations and deployment of structure change.
Usually indexed views carry a clustered index to allow fast performance on selects. I believe the impact is relatively small, especially if your indexed views relate to a very small portion of your data. I would actually recommend setting up indexed views only on a tiny portion of your large tables.
Indexed views are not answering all the issues due to its limitations: no left join, no subqueries, no min, no max aggregations, etc... but in some specific cases, it is really really powerful.
For a client, I was able to remove 1 million ios consumption to 50 ios on a very frequent select. I think you should be interested in indexed view when:
- the tables underneath are handling millions of rows.
- the result of the indexed view is cnsistantly less than a few thousand rows.
- product inventories
- Orders pending
- last hour activities
You might say that the cachestore could have made the necessary adjustment to a frequent query. I guess yes, unless the tables underneath keep changing! and this is usually the case.
Also, many data models are not nicely build and reference to very large tables with a lot of columns, texts, etc... Indexed views help you out there as well to remove the very expensive key lookup.
Another advantage is the use of indexed view on transactional replication. Also you can grant select permission on this query for specific process, so you limit access to the database.
Please do react to tell me what you think guys about using indexed views, advantages, drawbacks, etc...
2. Filtered indexes: Your transactional activities handled on a tiny portion of your large tables
Filtered indexes are no other than indexes but with a where condition. For example, you might have processes wondering about the last hour orders on your Ecommerce website. This could represent a few hundred orders but your table is super huge, as you never archived the orders.
Sure you can set up an index on the ordered date, sure you can use indexes, but in some case, your problem is not really setting an index on one column, your problem is the key lookup that is expensive or the fact that adding columns on your indexes are very expensive on DML operations, whether or not it is an included column in your index. You can set up filtered indexes on like the last hour, adding all the lookup columns you might need for a specific process that runs every second. Your cachestore does not work anyway because your website is successful and keep having orders!
I have written an article on dynamic filtered index that you can find on my profile if you need to use non deterministic values to filter things.
I have many clients, in telecommunications, in financial industry and in Ecommerce that have been using dynamic filtered indexes for multiple years and this has been a lifesaver for them.
Sometimes, solutions are not setting up fancy stuff like BPE, in-memory OLTP or column clustered index, sometimes it is just common sense!
Let me know, DBAs, in the world, what you think, comment this article, tell me about your experience with indexes views, filtered indexes and maybe other methods to improve performance of your queries, that are not just the classic indexing or making your table small and archive...