Filtered Indexes - Simple Partitioning without Enterprise Edition?

That is correct, and one of the limitations of this solution. In my scenario the maintenance of the index does not add a lot of overhead but I could see other examples where it could be significant.

Thanks

Nigel

We have a very similar situation, and are looking forward to upgrading and making use of filtered indexes.

It's not clear to me... did you try using filtered indexes with just the "status" field, and still had problems with complex queries? Or were the problems you experienced in prior versions, where the "status" index was a regular (non-filtered) index.

Thanks.

In my opinion, filtered indexes are one of the unsung heroes of 2008+. They can significantly reduce IO when the column is part of an oft-used predicate.

As previously mentioned, relying on autostats to maintain the statistics will only result in threshold changes at the TABLE level, not the column level. For slowly changing, large, historical tables, statistics will only be an issue over time. More than likely, if you have a defrag plan on your indexes, the stats on the index will be updated by the index rebuild process.

Thanks for re-enforcing my belief patterns.

We use enterprise almost exclusively but the STANDARD edition point is worth the reminder. PARTITIONING is enterprise+.

Thanks for the article and talking about the problem solving as you walked through this. We have done like things and have built these to construct "covered queries" where the index contains that fields we want to use. Again it adds to overhead overall but the speed of getting the data where we read far more than we write has sped up the major functions of the system.

Keep up the good work!

Did you try just putting the Status column only in the index? SQL can still use that index to do a key lookup back to the main table. Hopefully the number of rows in Active status would be limited enough to allow SQL to do that.

Or did you try that and find that SQL would then not use the index but do a full scan instead?!

Hi there, originally the status field was a normal index, and then I started experimenting with a filtered index and a few covering fields, and eventually came to the conclusion that adding all the columns would be highly beneficial in this scenario. This was due to the steady increase in disk IO over the years starting to create minor performance issues. Although I show a very simple example the real world queries are actually very complex but the common theme was very frequently that I was looking to take all the ACTIVE customer orders and look at the ACTIVE order routing records for those orders so if I had 'tables' of just the active orders the query optimiser would have a much simpler time without thinking about bookmark lookups etc. I hope this helps.

Hi there, I tried filtered indexes as you suggest and that was beneficial in it's own right. But I knew I could go further. Some of the table have very high selectivity (eg typically 7500 Active records out of 20 million rows) and some less so and I just knew that a 'full covering filtered index' including all columns would not be a great overhead and enable the query optimiser to get the right answer every time without thinking about it and that proved to be the case. It could be a very dangerous technique where selectivity is not so good and there is much higher data volume changing the status field. Sometimes you just know. The hardest thing was figuring out that a filtered index could create a virtual table by including all the other fields, even though it now seem obvious, as almost every example of such indexes only include a few fields but my index looks very strange with many fields!

It's a good idea, but why not use included fields, which will reduce the overhead further?

In the long run, especially with examples of having 7,500 active out of 20M, you will want to move to partitioned views if you stick on standard edition.

Table A - Status constraint to have only a [RowStatus] of "A" (Active)

Table B - Status constraint to have only a [RowStatus] of "H" (Historic)

Table C - Status constraint to have only a [RowStatus] of "X" (Cancelled)

All default inserts go into Table A and are moved into B or C when the status changes. You then access the records via a unioned view:

CREATE VIEW dbo.vAllOrders

AS

SELECT COL1, cOL2, RowStatus

from Table A

UNION ALL

SELECT COL1, cOL2, RowStatus

from Table B

UNION ALL

SELECT COL1, cOL2, RowStatus

from Table C;

The optimizer is bright enough to know, if your constraints are in place and trusted, to only read data from the correct table. So in the example above, SELECT * FROM dbo.vAllOrders WHERE [RowStatus] = 'A' would only read data from Table A (7.5K rows) and would totally ignore Table's B and C (partition elimination).

There's no massive code change involve and this scales very well. Reading history (20M rows) will be slow but reading active records will be very fast. Your storage costs will be far lower and your indexes much more productive. Google partitioned views/partition elimination, as this would definitely be the way forward in the long run. Partition elimination does work in all versions, including standard, and is a great way of getting robust partitioning in situations like these where you cannot use enterprise table partitioning.

I'm wondering - why do you say that included columns would reduce overhead? I assume you mean the overhead of maintaining the index when there are inserts, updates, or deletes. If so, included columns actually increase the overhead because the data for the included columns is stored at the leaf level of the nonclustered index and so must be managed whenever the data in the underlying table changes. Included columns are a good idea when the increased performance of "covered" queries that use the index outweighs the costs of maintaining the index.

At any rate, the author here described a filtered index that included ALL the columns of the table. He explained pretty well that the costs of maintaining such an index were small enough and easily justified by the performance gain of queries that could use that index.

Thanks, that's a good idea I will investigate. Things are slightly more complex than I painted as I use merge replication with over 100 tables (this is the largest in rowcount) over relatively low bandwidth ADSL to many sites so moving large amounts of data around is not trivial in that scenario. Once the system was set up and in steady state relatively low numbers of records would be moving round. That is one of the reasons I liked the solution suggested as it required no code changes and was replication friendly. Thanks again for the suggestion.