This is not an answer, but instead a (hopefully) better explanation of my comment left on David Browne's (much better and hopefully accepted) answer.
First, David's answer is the best approach in my opinion, and he does a great job of quickly touching on the major concerns with multi-tenant database design. Use partitioning! Use it! Partitioning is the right feature here and works with NCCIs as well.
My comment about further improving NCCI performance by using a filtered NCCI was not meant as an alternative to partitioning, rather (depending on your data behavior) you may want to use a filtered NCCI in addition to partitioning. A filtered NCCI is a good option when the following is true:
- For a given table where you're considering a NCCI, said table does not have more than 1 million records inserted per tenant, per week. Per the answer to my initial comment on your question, this looks to be true for your scenario.
- For the same table, there is also a column or columns included within its definition that implies new/volatile data or older/stable data. This could be a date field, or a combination of columns, but basically this column or columns will be used as the filter condition, and if no combination of columns exist that represents data volatility, a filtered NCCI becomes more difficult to properly define, and therefore, less useful.
First, what's so great about a filtered NCCI?
Filtered NCCIs are pretty cool in that when data being returned by a query isn't entirely contained within a filtered NCCI, the engine will still use it to pull out what data it can and then go to the (traditional) row-store indexes to pull the remaining data. This is especially helpful regarding analytical/aggregate workloads where NCCIs tend to out perform their row-store index counterparts.
This concept is better explained in this MS article, but I find this info-graphic, also taken from that article, to be quite effective at summing it all up:
What's the downside of using an NCCI?
It's important to know that after a NCCI is created, any data stored within a RowGroup effectively becomes read-only. Changes are not applied to the RowGroup itself, rather a record is marked as deleted and the updated data is stored in a different RowGroup within the NCCI. These changes are cataloged in the Deleted Buffer and the Deleted Bitmap so the engine knows what's valid in a given RowGroup. Proper maintenance will help minimize the performance costs of changing data, but if you have a lot of activity occurring on the newest data within a table (e.g. the "hot" data) between maintenance windows, a filtered NCCI may be the best way to segment the less volatile/warm data from the more volatile/hot data.
Why the assumptions are important?
If the first assumption is false and you will see more than 1 million records inserted into a table per tenant per week, then you can forgo a filtered NCCI and should instead create the NCCI using the additional
COMPRESSION_DELAY keyword. There's a query MS recommends you run before doing this, found here, that will give you a better idea of what this value should be, but the maximum value is 10080 or 7 days time. This will keep a delta store open until either 1 million records are accumulated or the specified period of time passes and then that delta store will be converted into a NCCI RowGroup. This basically allows the NCCI to automatically work like a filtered NCCI with a sliding window. However, if you don't have enough incoming data to periodically fill a RowGroup to take advantage of this use-case, a filtered NCCI may be the better approach.
If the second assumption is false, and you don't have a column that can easily define what data is warm vs hot, a filtered NCCI becomes no better than a normal NCCI. Again, use the
COMPRESSION_DELAY keyword with an appropriate value and go from there.
The cost of a filtered NCCI
Filtered NCCIs don't come without a cost; the main issue being the definition of the filter. As hot data eventually becomes warm data, the filter may also need to be adjusted so this additional data can be included within the filtered NCCI. You are unable to change the filter of a NCCI using
ALTER INDEX, so changing the definition of the filter requires that you to drop the index and recreate it with the updated filter clause. This obviously will carry ramifications in that the index won't be accessible during this change, so if your database doesn't afford a long enough maintenance window for this sort of operation, don't use a filtered NCCI. Filtered NCCIs also have some other technical limitations as fully outlined here.
The benefit of a filtered NCCI will outweigh its cost when your new data is very volatile and a NCCI isn't providing a good performance boost over its row-store index counterparts. Testing is the name of the game here, but in the right situation, a filtered NCCI sitting on top of a partitioned table for your multi-tenant database could translate to big gains in performance.
Hopefully that does a better job of explaining my comment to David's earlier answer.... and again, this is not an answer, just a very long-winded comment.