December 13, 2017 at 9:50 am
I will have about 7 tables that are used for operational reporting and ETL extracts. Most of the operational reporting will be limited to last 2 years of data, ETL will be close to that but also could utilize whole table. most of these tables will be 40-120 million rows all in same database. For the operational reporting looking at whether indexes views are better or filtered indexes. Here is the caveat, most of the operational reporting will actually be done from a Read only AG node that contains this database. Any experience or ideas with this would be appreciated. Leaning towards the indexed views
December 13, 2017 at 12:10 pm
It Depends.
It depends on how you're querying these tables, how you're joining them, what kinds of Aggregations you're performing, how your data is partitioned and stuff like that.
Filtered indexes are good for what you're describing with respect to filtering for date ranges.
If you're frequently filtering for data from the past two years then a correctly designed, filtered index (preferably covering) will perform nicely. Indexed views are great for pre-aggregating data, pre-joining data, adding indexes on columns you wish existed (e.g. you want to group by Month by you don't have a Month column and, instead need to go non-SARGable using code that looks like:
GROUP BY MONTH(<some datetime column)
Being on SQL Server 2016 - there's a few other indexes to consider including clustered and nonclustered columnstore indexes. In 2016 you have filtered columnstore indexes.
That's my $0.02.
-- Itzik Ben-Gan 2001
December 14, 2017 at 4:56 am
I agree with everything Alan said. I would test both. It sounds like a filtered index is likely to work better for you, but test both to be sure. Just remember that the test should include maintaining the index, so in addition to reads from the index, you do writes to the base table in order to see the behavior and cost of keeping each index type updated. I suspect the filtered index will be less here, but please, don't go on my assumptions, test it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2017 at 6:34 am
that will be my approach will start with biggest tables and work my way through
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply